DuckDB Arrays, CTE & DATE Dimension

select generate_series(1,100,12) as num;

select generate_series(DATE '2024-09-01', DATE '2024-09-10', INTERVAL 1 DAY) as dt;

with date_cte as(
	select strftime(unnest(generate_series(DATE '2024-09-01', DATE '2024-09-10', INTERVAL 1 DAY)),'%Y-%m-%d') as dt
)
SELECT dt FROM date_cte;

WITH date_cte AS (
    SELECT unnest(generate_series(DATE '2024-09-01', DATE '2024-09-10', INTERVAL 1 DAY)) AS dt
)
SELECT 
  REPLACE(CAST(CAST(dt AS DATE) AS VARCHAR), '-', '') AS DateKey
  ,CAST(dt AS DATE) AS ShortDate
  ,DAYNAME(dt) AS DayOfWeek
  ,CASE 
    WHEN EXTRACT(DOW FROM dt) IN (0, 6) THEN 'Weekend'
    ELSE 'Weekday'
  END AS IsWeekend
  ,CAST(CEIL(EXTRACT(DAY FROM dt) / 7.0) AS INTEGER) AS WeekOfMonth
  ,EXTRACT(WEEK FROM dt) AS WeekOfYear
  ,'Q' || CAST(EXTRACT(QUARTER FROM dt) AS VARCHAR) AS Quarter
  ,CASE 
        WHEN strftime(dt, '%m') IN ('01', '02', '03') THEN 'Q1'
        WHEN strftime(dt, '%m') IN ('04', '05', '06') THEN 'Q2'
        WHEN strftime(dt, '%m') IN ('07', '08', '09') THEN 'Q3'
        ELSE 'Q4'
    END AS Quarter_alternate
  ,EXTRACT(YEAR FROM dt) AS Year
  ,EXTRACT(MONTH FROM dt) AS Month
  ,EXTRACT(Day FROM dt) AS Day

FROM date_cte
ORDER BY dt;