While storing the data, we may get some gaps in data. But, we should not have the gaps in the report data. So, by using Common Table Expressions(CTE), we create the series of time/date values that do not have any gaps. We will study to create a time/date values series in this blog.
In Snowflake, we must group the data by time. However, we don’t require the gaps in our report data. We should create the series of time/date values that do not have gaps through the common table expression:
set start_date = ‘2022-06-02’
set end_date = ‘2022-06-30’
with cte_data (data_rec) as (
select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec))
from cte_date1
where date_rec < $end_date
)
select date_rec1
from cte_date1
date_rec1
2022-06-02
2022-06-03
2022-06-04
2022-06-05
2022-06-06
….
2022-06-30
We left to join our data series against the gapless series. Creating the count of the sessions for every day:
set start_date = ‘2022-06-02’
set end_date = ‘2022-06-30’
with cte_date (date_rec) as (
select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec))
from cte_date1
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;
By creating a series of time or date values through common table expressions, we can avoid gaps in data. I hope this blog offers sufficient information about avoiding gaps.
Snowflake Related Articles
If you have any queries, let us know by commenting below.
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
Name | Dates | |
---|---|---|
Snowflake Training | Dec 07 to Dec 22 | View Details |
Snowflake Training | Dec 10 to Dec 25 | View Details |
Snowflake Training | Dec 14 to Dec 29 | View Details |
Snowflake Training | Dec 17 to Jan 01 | View Details |