Weatherglass Bucket Query

events.sql

create or replace function bucket(timestamp, interval) 
  returns timestamptz 
  as $$
    select 
      to_timestamp(
        floor(
          extract(epoch from $1) / 
          extract(epoch from $2)
        ) * extract(epoch from $2)
      );
  $$ language sql;
with timeslices as (
  select
    interval,
    0 as blank_count
  from generate_series(
    bucket($2, $4), 
    bucket($3, $4), 
    $4::interval 
  ) as interval
),
events_per_interval as (
  select
    bucket(created_at, $4) as interval, 
    count(*) as count
  from events
  where site_id = $1
    and created_at >= $2
  group by interval
)
select 
  timeslices.interval as interval, 
  coalesce(events_per_interval.count, timeslices.blank_count) as count
from timeslices
  left outer join events_per_interval 
    on events_per_interval.interval = timeslices.interval
order by timeslices.interval;