How to Avoid Gaps in Data in Snowflake
Let's say you need to group by time in Snowflake but you don't want any gaps in your report data.
First, generate a series of date/time values that have no gaps using a common table expression:
set start_date = '2020-04-01';
set end_date = '2020-04-30';
with cte_date (date_rec) as (
select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec)) --or week, month, week, hour, minute instead of day
from cte_date
where date_rec < $end_date
)
select date_rec
from cte_date;Note: you can run multiple statements in sequence in PopSQL with just one click. In the example above, you could highlight all 3 statements at once and then click
Run All.
date_rec
------------------------
2020-04-01
2020-04-02
2020-04-03
2020-04-04
...
2020-04-30Now you can left join your date series data against this gapless series. Here's how you could create a count of sessions for each day:
set start_date = '2020-04-01';
set end_date = '2020-04-30';
with cte_date (date_rec) as (
select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec))
from cte_date
where date_rec < $end_date
)
select
cte_date.date_rec,
count(s.id) as session_ct
from cte_date
left outer join sessions s on to_date(s.start_date) = cte_date.date_rec
group by date_rec;
