I want to make a query getting the average created value per minute. I don’t come up with any idea but using subquery like below. Would there be any other ways using only one query (without subquery)
with item_cnt_per_min as ( select date_trunc('minute', created), count(*) as cnt from item where created > '2020-09-21 06:10' group by 1 order by cnt desc ) select avg(cnt) from item_cnt_per_min
Advertisement
Answer
The average created value could be calculated as:
select count(*) / count(distinct date_trunc('minute', created)) from item where created > '2020-09-21 06:10';
That is, divide the total by the number of minutes.