1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
| WITH global_spine AS (
SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS interval_id
,DATEADD('day'
,(interval_id - 1)
,'2020-01-01T00:00'::TIMESTAMP_NTZ
) AS spine_start
,DATEADD('day'
,interval_id
,'2020-01-01T00:00'::TIMESTAMP_NTZ
) AS spine_end
FROM TABLE(GENERATOR(ROWCOUNT => 1097)
),
groups AS (
SELECT product
,MIN(sales_date) AS local_start
,MAX(sales_date) AS local_end
FROM my_first_table
GROUP BY product
),
group_spine AS (
SELECT product
,spine_start AS group_start
,spine_end AS group_end
FROM groups AS G
CROSS JOIN LATERAL (
SELECT spine_start
,spine_end
FROM global_spine AS s
WHERE s.spine_start >= g.local_start
)
)
SELECT g.product AS group_by_product
,g.group_start
,g.group_end
,t.*
FROM group_spine AS g
LEFT JOIN my_first_table AS t on sales_date >= g.group_start
AND sales_date < g.group_end
AND g.product = t.product;
|