PostgreSQL getting daily, weekly, and monthly averages of the occurrences of an event in one query

I'd write the query in a way like this:

select event, daily_avg, weekly_avg, monthly_avgfrom (    select event, avg(count) monthly_avg    from (        select event, count(*)        from tracking_stuff        where event in ('thing1', 'thing2', 'thing3')        group by event, date_trunc('month', created_at)    ) s    group by 1) monthlyjoin (    select event, avg(count) weekly_avg    from (        select event, count(*)        from tracking_stuff        where event in ('thing1', 'thing2', 'thing3')        group by event, date_trunc('week', created_at)    ) s    group by 1) weekly using(event)join (    select event, avg(count) daily_avg    from (        select event, count(*)        from tracking_stuff        where event in ('thing1', 'thing2', 'thing3')        group by event, date_trunc('day', created_at)    ) s    group by 1) daily using(event)order by 1;

If the where condition eliminates a significant portion of the data (say more than a half) the use of cte could slightly speed up the query execution:

with the_data as (    select event, created_at    from tracking_stuff    where event in ('thing1', 'thing2', 'thing3')    )select event, daily_avg, weekly_avg, monthly_avgfrom (    select event, avg(count) monthly_avg    from (        select event, count(*)        from the_data        group by event, date_trunc('month', created_at)    ) s    group by 1) monthly--  etc ... 

Just for curiosity I've done a test on the data:

create table tracking_stuff (event text, created_at timestamp);insert into tracking_stuff    select 'thing' || random_int(9), '2016-01-01'::date+ random_int(365)    from generate_series(1, 1000000);

In every query I've replaced thing with thing1, so the queries eliminate about 2/3 of rows.

Average execution time of 10 tests:

Original query          1106 msMy query without cte    1077 msMy query with cte        902 msClodoaldo's query       5187 ms

In 9.5+ use grouping sets

The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned

select event,    avg(total) filter (where day is not null) as avg_day,    avg(total) filter (where week is not null) as avg_week,    avg(total) filter (where month is not null) as avg_month    from (    select        event,        date_trunc('day', created_at) as day,        date_trunc('week', created_at) as week,        date_trunc('month', created_at) as month,        count(*) as total    from tracking_stuff    where event in ('thing','thing2','thing3')    group by grouping sets ((event, 2), (event, 3), (event, 4))) sgroup by event