- Explore
- Collaborate
- Visualize
- Connect
- Pricing
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-30
Now 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;