Date functions: six drills
Six date-function drills, ordered easy to hard. Each isolates one date pattern. The default editor shows a working solution - try clearing it and writing your own first; the hidden checks will grade what's there when you press Run.
Tables: orders.
1. DATE_TRUNC - monthly revenue rollup (Easy) #
Task: for each month, return total revenue from paid orders. Output month (a DATE for the first of the month) and revenue (rounded to 2 decimals). Order by month.
The skill: DATE_TRUNC('month', col) rounds a date down to the first of its month. Group by the truncated value to bucket rows by month while preserving the year (unlike EXTRACT(MONTH FROM ...), which collapses Jan 2024 and Jan 2025 together).
2. Sargable date filter - last 90 days (Easy) #
Task: find every order placed in the last 90 days relative to 2024-06-01 (use that as the reference "today" so the result is reproducible). Return id, order_date, total. Order by order_date, id.
The skill: an index-friendly date range filter is a half-open inequality - order_date >= base AND order_date < base + interval. Wrapping the column in a function (WHERE DATE_TRUNC('day', order_date) = ..., WHERE EXTRACT(MONTH FROM order_date) = 6) defeats indexes - the engine has to compute the function for every row.
3. STRFTIME - human-readable month labels (Easy) #
Task: for each month with paid orders, return the month, a display label, and revenue. Output month (DATE, first of the month), month_label (the month formatted as 'Jan 2024' - abbreviated month name, space, 4-digit year, via STRFTIME with '%b %Y'), and revenue (rounded to 2 decimals). Order by month.
The skill: STRFTIME(date, format) is DuckDB's canonical date-to-string formatter - %b is the abbreviated month name, %Y the 4-digit year. (Postgres uses TO_CHAR instead; DuckDB's native spelling is STRFTIME.)
The trap: never sort by the label - alphabetically, 'Apr 2024' comes before 'Jan 2024'. Keep the real date column for ordering and use the string only for display.
4. Day-of-week distribution (Medium) #
Task: count paid orders by day of week, with the day name. Output dow (integer 0-6, Sunday=0 on DuckDB / Postgres) and order_count. Order by dow.
The skill: EXTRACT(DOW FROM date_col) returns the day-of-week as an integer. Numbering varies by engine - DuckDB / Postgres use 0=Sunday, 6=Saturday; ISO conventions use 1=Monday, 7=Sunday; SQL Server's DATEPART(weekday, ...) depends on @@DATEFIRST. Always check on your engine before trusting a number.
5. Calendar table - fill missing months (Medium) #
Task: for every month between 2024-01-01 and 2024-12-01 inclusive, return the count of paid orders. Months with no orders should appear with order_count = 0 (not missing rows). Output month (DATE) and order_count. Order by month.
The skill: generate_series (Postgres / DuckDB) builds a virtual calendar; LEFT JOIN your data onto it to fill gaps. The default GROUP BY over the source loses months that have no orders entirely - the calendar pattern fixes that.
6. Month-over-month change (Hard) #
Task: for each month with paid orders, return the month, that month's revenue, the previous month's revenue (NULL for the earliest month), and the change. Output month, revenue, prev_revenue, change. Round revenue and change to 2 decimals. Order by month.
The skill: aggregate to monthly grain first, then LAG over the monthly result. Combining DATE_TRUNC + LAG is the canonical "period-over-period change" pattern. The trap: trying to do it in one window over raw orders gives you order-over-order change, not month-over-month.
The date patterns to remember:
DATE_TRUNC('unit', col)for time-series buckets - groups rows by month / week / day while preserving the year.- Half-open range (
>= start AND < end) for date filters - sargable, indexable, no off-by-one. STRFTIME(date, '%b %Y')for display labels (DuckDB-native; Postgres usesTO_CHAR) - sort by the real date column, never the label.EXTRACT(DOW FROM ...)numbering varies by engine - 0-6 (Sun) on DuckDB / Postgres, 1-7 (Mon) for ISO. Confirm before using.- Calendar table via
generate_seriesto fill missing periods - pure aggregation drops empty months entirely. - Aggregate first, then window for period-over-period change. Doing it in one step over raw rows gives you the wrong grain.