title: PostgreSQL getting daily, weekly, and monthly averages of the occurrences of an event in one query
body: <p>Currently I have this rather large query that works by</p>
  
  <ol>
  <li>Aggregating the daily, weekly, monthly counts into intermediate tables by taking the <code>count()</code> of an event grouped by the event name and the date.</li>
  <li>Selecting the avg count over each intermediate table by doing <code>avg()</code> group by just event, doing a union of the results, and because I want to have a separate column for daily, weekly, monthly, putting a filler value of 0 into empty columns.</li>
  <li>I then sum over all the columns, and the 0s basically act as a no-op, which gives me just a single value for each event.</li>
  </ol>
  
  <hr>
  
  <p>The query is pretty large though, and I feel like I'm doing a lot of repetitive work. Is there any way to do this query better or make it smaller? I haven't really done queries like this before so I'm not quite sure.</p>
  
  <pre><code>WITH monthly_counts as (
    SELECT
      event,
      count(*) as count
    FROM tracking_stuff
    WHERE
      event = 'thing'
      OR event = 'thing2'
      OR event = 'thing3'
    GROUP BY event, date_trunc('month', created_at)
  ),
  weekly_counts as (
    SELECT
      event,
      count(*) as count
    FROM tracking_stuff
    WHERE
      event = 'thing'
      OR event = 'thing2'
      OR event = 'thing3'
    GROUP BY event, date_trunc('week', created_at)
  ),
  daily_counts as (
    SELECT
      event,
      count(*) as count
    FROM tracking_stuff
    WHERE
      event = 'thing'
      OR event = 'thing2'
      OR event = 'thing3'
    GROUP BY event, date_trunc('day', created_at)
  ),
  query as (
    SELECT
      event,
      0 as daily_avg,
      0 as weekly_avg,
      avg(count) as monthly_avg
    FROM monthly_counts
    GROUP BY event
    UNION
    SELECT
      event,
      0 as daily_avg,
      avg(count) as weekly_avg,
      0 as monthly_avg
    FROM weekly_counts
    GROUP BY event
    UNION
    SELECT
      event,
      avg(count) as daily_avg,
      0 as weekly_avg,
      0 as monthly_avg
    FROM daily_counts
    GROUP BY event
  )
  SELECT
    event,
    sum(daily_avg) as daily_avg,
    sum(weekly_avg) as weekly_avg,
    sum(monthly_avg) as monthly_avg
  FROM query
  GROUP BY event;
  </code></pre>