I have a date series file below.
base_date_series.zip (57.1 KB)
It is a csv with the column as_of_date which goes from 1970-01-01 to 2050-01-01
It is continuous and does not contain any gap.
=======
When using the lead() window function inside a CTE like below
with cte as (
SELECT *,
lead(as_of_date, 1) over(order by as_of_date) as as_of_date__lead_by_1,
lead(as_of_date, 2) over(order by as_of_date) as as_of_date__lead_by_2 ,
lead(as_of_date, 3) over(order by as_of_date) as as_of_date__lead_by_3
FROM Minio.finance.l1."base_date_series"
)
select * from cte
where as_of_date__lead_by_2 is null
I notice that when the offset is greater than 1, then I got null values.
==========
But interestingly, if the lead() over() is outside any CTE, there’s no gap.
SELECT *,
lead(as_of_date, 1) over(order by as_of_date) as as_of_date__lead_by_1,
lead(as_of_date, 2) over(order by as_of_date) as as_of_date__lead_by_2
FROM Minio.finance.l1."base_date_series"
where as_of_date >= '1980-11-01'
order by as_of_date
—> Left: running the lead() over() with offset = 2 OUTSIDE of any CTE, and the gap is gone
—> Right: The previous screenshot showing the gap when runnning lead() over() with offset =2 INSIDE a CTE
===========
In short,
There’s a gap when
- offset in lead() over() is greater than 1; and
- window function lead() over() is used inside a CTE
====
I am using dremio/dremio-oss:25.0.0
