Monthly report.

The report combines the whole chapter: a fixed period, CASE categories, CTE steps and aggregates. The period is a half-open interval: order_date >= DATE '2024-02-01' AND order_date < DATE '2024-03-01'. It includes every day from 1 to 29 February and excludes 1 March — no complex date functions and no "last day of the month" guessing. DATE '2024-02-01' is a date literal; the next chapter covers dates in depth.

Conditional aggregation is CASE inside an aggregate: COUNT(CASE WHEN total >= 50 THEN 1 END) counts only matching rows (CASE without ELSE yields NULL and COUNT skips NULL). SUM(CASE WHEN ... THEN total END) sums a subset; wrap it in COALESCE(..., 0) when there may be no rows. ROUND(x, 2) rounds money.

Without ORDER BY the result order is not guaranteed, so order the categories explicitly.

Common mistakes: <= '2024-02-29' or BETWEEN (brittle, and wrong for TIMESTAMP values); different bounds in different CTEs; forgetting rounding; ELSE 0 inside COUNT(CASE …) — that counts every row.

Small example:

WITH march AS (SELECT total FROM orders WHERE order_date >= DATE '2024-03-01' AND order_date < DATE '2024-04-01') SELECT COUNT(*) AS orders_count, ROUND(SUM(total), 2) AS revenue FROM march;