Capstone: The Q4 Business Review

The last request of the year comes from Rania herself: “I’m presenting the Q4 review to the board. How did Q4 2025 compare with Q4 2024, country by country, and what’s the story?” This is the whole course in one question. It needs date ranges, joins without fan-out, conditional aggregation, CTEs, sanity checks and, at the end, a few sentences a board can act on. Work through it the way you would at a real desk: definitions first, then steps, then checks, then words.

Step 1: fix the definitions

Before any SQL, write down what each word in the question means, and put it where the query can see it:

• Q4: 1 October to 31 December, filtered as half-open ranges, from the first day of the quarter up to but not including 1 January, for both 2024 and 2025.
• Revenue: sum of quantity * unit_price * (1 - discount) from order_items, shipping excluded.
• Which orders: every status except ‘cancelled’, the definition used throughout this course.
• Country: the customer’s country, from customers.
• Grain of the answer: one row per country.

The periods go into a tiny CTE of their own. Every later step joins to it, so changing the quarter means editing one place, not six.

Step 2: build it in named steps

WITH periods AS (
  SELECT 'Q4 2024' AS period, '2024-10-01' AS start_date, '2025-01-01' AS end_date
  UNION ALL
  SELECT 'Q4 2025', '2025-10-01', '2026-01-01'
),
order_revenue AS (
  -- grain: one row per order
  SELECT order_id, SUM(quantity * unit_price * (1 - discount)) AS revenue
  FROM order_items
  GROUP BY order_id
),
q4_orders AS (
  -- grain: one row per non-cancelled Q4 order
  SELECT p.period, c.country, o.order_id, r.revenue
  FROM orders AS o
  JOIN periods AS p
    ON o.order_date >= p.start_date AND o.order_date < p.end_date
  JOIN customers AS c ON c.customer_id = o.customer_id
  JOIN order_revenue AS r ON r.order_id = o.order_id
  WHERE o.status <> 'cancelled'
),
by_country AS (
  -- grain: one row per country
  SELECT
    country,
    SUM(revenue) FILTER (WHERE period = 'Q4 2024') AS revenue_2024,
    SUM(revenue) FILTER (WHERE period = 'Q4 2025') AS revenue_2025,
    COUNT(*)     FILTER (WHERE period = 'Q4 2025') AS orders_2025
  FROM q4_orders
  GROUP BY country
)
SELECT
  country,
  ROUND(revenue_2024, 2) AS revenue_q4_2024,
  ROUND(revenue_2025, 2) AS revenue_q4_2025,
  ROUND(100.0 * (revenue_2025 - revenue_2024) / NULLIF(revenue_2024, 0), 1) AS growth_pct,
  ROUND(revenue_2025 / orders_2025, 2) AS avg_order_value_2025
FROM by_country
ORDER BY revenue_q4_2025 DESC;

Every technique here has a lesson behind it. The join to periods uses a range condition in ON, which labels each order with its quarter and drops everything else. order_revenue collapses items first, so COUNT(*) later counts orders. FILTER puts both years side by side in one pass. NULLIF protects the growth calculation from a country with no sales last year.

Step 3: check before you believe

Two checks take a minute and catch most mistakes. First, reconcile: the eight revenue_q4_2025 values should add up to the total from a simpler query over orders and order_items alone, 146,825.44, and the 2024 column to 52,537.10. They do, to within a cent of rounding, so no fan-out or lost rows. Second, revisit the data-quality suite. The misfiled products don’t matter at country level. Two returned orders without return records and 63 orders still ‘processing’ or ‘shipped’ do: some Q4 2025 revenue may yet be cancelled or refunded.

Step 4: write the answer

The query is not the deliverable; the answer is. Here’s what goes to Rania:

Q4 2025 revenue was 146.8k, up from 52.5k in Q4 2024 (+179%), with every country at least doubling. The growth is volume, not basket size: orders rose from 228 to 642 while average order value held at about 230. The UK is now our largest market (26.5k, up from 5.4k). Jordan’s +627% comes from a small base. Caveat: 63 Q4 orders, most of them from December, were still processing or in transit at year end, so cancellations and returns could trim Q4 slightly.

Three to five sentences, the numbers that matter, one honest caveat. Notice what it doesn’t say: why volume grew. The cohort lesson suggests where to look next, at how many of those orders came from new customers acquired in the Black Friday weeks. A good answer usually ends by pointing at the next good question.

What you can do now

Look back at the first lesson’s request about thin margins. You’ve gone from SELECT and LIMIT to an analysis with five named steps, built-in checks and a written conclusion. More importantly, you’ve built habits: write the question and grain before the query, use half-open date ranges, put right-table filters in ON, aggregate before joining, use NOT EXISTS for “never”, reconcile every total, and treat NULLs and failed checks as questions. Those habits transfer to any database, any dialect and any business. The exercise below is your last one, written from a blank page.