A Date Dimension

Reports group by time all the time: revenue per month, per weekday, weekends against weekdays. A date dimension is a table with one row per calendar day and columns that describe that day: its month, the month's name, the weekday, whether it is a weekend. Reports join the fact to it and group by those columns, instead of working them out again in every query.

It also fixes a quiet bug. fact_order_lines only has the days on which someone ordered; a day with no orders leaves no row at all. Count days or average per day from the fact alone and the empty days are missing, so the numbers look better than they are.

Making the days: DuckDB's table function range(start, stop, step) gives one row per step, in a column named range, and leaves out the stop value. On dates, each value is a timestamp (a date with a time), and CAST(range AS DATE) keeps only the date. MONTH, MONTHNAME and DAYNAME give the month's number, the month's name and the weekday's name; x IN ('a', 'b') is True when x is one of the listed values. Three days of March:

SELECT CAST(range AS DATE) AS day, DAYNAME(range) AS day_name
FROM range(DATE '2026-03-01', DATE '2026-03-04', INTERVAL 1 DAY)

day | day_name
2026-03-01 | Sunday
2026-03-02 | Monday
2026-03-03 | Tuesday

The report then starts from dim_date and joins the fact with a LEFT JOIN on the date, which keeps every day, also the days no order line matches (their fact columns are NULL). The check prints the first rows of dim_date, its size, and the report:

date | month | month_name | weekday | is_weekend
2026-01-01 | 1 | January | Thursday | False
2026-01-02 | 1 | January | Friday | False
2026-01-03 | 1 | January | Saturday | True
(3 rows)
day_rows | first_day | last_day
365 | 2026-01-01 | 2026-12-31
(1 row)
is_weekend | days | days_with_orders | revenue
False | 261 | 161 | 38698
True | 104 | 67 | 18280
(2 rows)

Your task: the starting code builds fact_order_lines (paid lines: order_id, date, amount) and most of the report. Build dim_date, one row per day of 2026 with the columns date, month, month_name, weekday and is_weekend, then LEFT JOIN the fact to it in query.

Assignment
Follow the Coding Tutorial to build dim_date from range and join the order lines to it with a LEFT JOIN.

Hint
range leaves out its stop value, so stop at DATE '2027-01-01' to end on 2026-12-31. Every column of dim_date is worked out from range, the column the series makes.