CREATE OR REPLACE TABLE PAGE_TRAFFIC(
    DT DATE,
    PAGE_NAME VARCHAR(100),
    PAGE_VIEWS NUMBER(38,0)
);

INSERT INTO PAGE_TRAFFIC (DT, PAGE_NAME, PAGE_VIEWS) VALUES('2024-09-01', 'Homepage', 10);
INSERT INTO PAGE_TRAFFIC (DT, PAGE_NAME, PAGE_VIEWS) VALUES('2024-09-02', 'Homepage', 15);
INSERT INTO PAGE_TRAFFIC (DT, PAGE_NAME, PAGE_VIEWS) VALUES('2024-09-05', 'Homepage', 13);
INSERT INTO PAGE_TRAFFIC (DT, PAGE_NAME, PAGE_VIEWS) VALUES('2024-09-02', 'International news', 2);
INSERT INTO PAGE_TRAFFIC (DT, PAGE_NAME, PAGE_VIEWS) VALUES('2024-09-08', 'International news', 9);

WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, dt) AS dt 
FROM DATES 
WHERE dt < '2024-09-08'
)
SELECT * 
FROM DATES
;

WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, dt) AS dt 
FROM DATES 
WHERE DT < '2024-09-08'
)
SELECT 
DATES.DT
, PAGE_VIEWS -- NULL is returned if there is no match in DATES.
FROM page_traffic AS pt 
    RIGHT JOIN DATES -- All rows from this CTE will be returned, even if there is no match in page_traffic.
    ON pt.dt = dates.dt 
    AND page_name = 'Homepage'
;

WITH RECURSIVE DATES AS  ... -- Truncuating this to make these code blocks more readable.
SELECT 
DATES.dt
, page_name
, page_views
FROM page_traffic AS pt 
    RIGHT JOIN DATES
    ON pt.dt = dates.dt 
    AND page_name = 'Homepage'
;

WITH RECURSIVE DATES AS  ...
SELECT 
DATES.dt
, 'Homepage' AS page_name
, COALESCE(page_name, 'Homepage') AS page_name_v2
, FIRST_VALUE(PAGE_NAME) IGNORE NULLS OVER (ORDER BY DATES.DT) AS page_name_v3
, PAGE_VIEWS
FROM page_traffic AS pt 
    RIGHT JOIN DATES
    ON pt.dt = dates.dt 
    AND page_name = 'Homepage'
;

WITH RECURSIVE DATES AS ...
SELECT 
DATES.dt
, COALESCE(page_name, 'Homepage') AS page_name
, page_views
FROM page_traffic AS pt 
    RIGHT JOIN DATES
    ON pt.DT = dates.DT 
    AND page_name = 'Homepage'
UNION ALL 
SELECT 
DATES.DT
, COALESCE(page_name, 'International news') AS page_name
, page_views
FROM page_traffic AS pt 
    RIGHT JOIN DATES
    ON pt.dt = dates.dt 
    AND page_name = 'International news'
;

WITH RECURSIVE DATES AS ...
SELECT 
pt.*
, dates.dt as dates_dt -- Note I've renamed it this to make it a bit easier to understand.
FROM page_traffic AS pt 
    CROSS JOIN DATES 
ORDER BY page_name, pt.dt, dates.dt
;

WITH RECURSIVE DATES AS ...
SELECT 
DISTINCT 
DATES.dt
, PT.page_name 
FROM page_traffic AS pt 
    CROSS JOIN DATES 
ORDER BY page_name, dt 
;

WITH RECURSIVE DATES AS ...
SELECT 
dates.dt AS dates_dt
, pt.*
FROM page_traffic AS pt 
    CROSS JOIN DATES 
WHERE dates.dt = '2024-09-01'
AND page_name = 'Homepage'
ORDER BY page_name, pt.dt, dates.dt
;

WITH RECURSIVE DATES ...
SELECT 
DISTINCT 
dates.dt AS dates_dt
, pt.page_name 
, CASE WHEN DATES.dt = PT.dt THEN page_views END AS pv
FROM page_traffic AS pt 
    CROSS JOIN DATES 
WHERE dates.dt = '2024-09-01'
AND page_name = 'Homepage'
;

WITH RECURSIVE DATES ...
SELECT 
DISTINCT 
dates.dt AS dates_dt
, pt.page_name 
, CASE WHEN DATES.dt = PT.dt THEN page_views END AS pv -- Be sure to rename this column.
, RANK() OVER (PARTITION BY dates_dt, page_name ORDER BY pv) AS pv_ranking 
FROM page_traffic AS pt 
    CROSS JOIN DATES 
WHERE dates.dt = '2024-09-01'
AND page_name = 'Homepage'
;

-- RDBMS with QUALIFY:
WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, dt) AS dt 
FROM DATES 
WHERE dt < '2024-09-08'
)
SELECT 
DISTINCT 
dates.dt AS dates_dt
, pt.page_name
, CASE WHEN DATES.dt = PT.dt THEN page_views END AS pv
FROM page_traffic AS pt 
    CROSS JOIN DATES 
QUALIFY RANK() OVER (PARTITION BY dates_dt, page_name ORDER BY pv) = 1 
ORDER BY page_name, dates_dt
;

-- If QUALIFY is not supported:
WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, DT) AS pt 
FROM DATES 
WHERE dt < '2024-09-08'
), 
final_dataset AS 
(
SELECT  
dates.dt AS dates_dt
, pt.page_name
, CASE WHEN DATES.DT = PT.DT THEN page_views END AS pv
, RANK() OVER (PARTITION BY dates_dt, page_name ORDER BY pv) AS pv_rank
FROM page_traffic AS pt 
    CROSS JOIN DATES 
)
SELECT 
DISTINCT 
dates_dt
, page_name
, pv
FROM final_dataset
WHERE pv_rank = 1 
ORDER BY page_name, dates_dt
;

WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, dt) AS dt 
FROM DATES 
WHERE dt < '2024-09-08'
)
SELECT 
DISTINCT 
dates.dt AS dt
, pn.page_name
FROM DATES
    CROSS JOIN (SELECT DISTINCT page_name FROM page_traffic) AS pn
;

WITH RECURSIVE DATES AS 
(
SELECT DATE('2024-09-01') AS dt 
UNION ALL
SELECT DATEADD(DAY, 1, dt) AS dt 
FROM DATES 
WHERE dt < '2024-09-08'
)
SELECT
dates.dt AS dates_dt
, pn.page_name
, pt.page_views as pv
FROM dates as dates
    CROSS JOIN (SELECT DISTINCT page_name FROM page_traffic) AS pn 
    LEFT join page_traffic as pt 
    ON pt.dt = dates.dt
    AND pt.page_name = pn.page_name
ORDER BY page_name, dates_dt
;