Level 8 · Boss: the annual business review

An annual business review is a handful of analytics patterns placed side by side. This boss asks for three of them, and each has one decision that makes it right or wrong.

Step 1: growth and share by channel

Put both years on the same row with conditional aggregation, so the growth is a simple column expression:

SUM(CASE WHEN o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01'
         THEN i.quantity * i.unit_price * (1 - i.discount_pct / 100.0) END) AS revenue_2024

The share of 2025 needs the grand total on every row: SUM(revenue_2025) OVER (). The answer is a striking slide: the app grew 53.2% and is now the largest channel (43.3% of 2025 revenue), while web shrank 7.2%. Total growth would have hidden that shift.

Step 2: two products per category

Here the brief says exactly two per category, so ROW_NUMBER() with a product_id tie-breaker is the right function, unlike the ties-included ranking earlier in the chapter. Watch for close calls: in Textiles the Bramble Percale Quilt beat the Delancey Cashmere-Blend Duvet Cover by under $50 in Q4 2025, and in Decor the top two are $94 apart. On a slide, a margin that thin deserves a footnote.

Step 3: first-order vs repeat revenue

“Is the business built on returning customers?” Number every customer’s completed orders over all history, then look at 2025:

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS order_number

An order with order_number = 1 is that customer’s first ever. Everything else is repeat revenue. At Harbor & Pine, 86% to 91% of each 2025 quarter’s revenue came from repeat orders. That is the signature of a mature customer base: most customers placed their first order before 2025.

The order of operations is the whole point. Number first, filter second. If you filter orders to 2025 inside the CTE, a customer who first bought in 2023 gets order_number = 1 on their first 2025 order, and repeat revenue is badly understated.

Common mistakes

• Two separate queries for 2024 and 2025 joined afterwards: more code, more places for the filters to differ.
• LIMIT for top N per group. It limits the whole result, not each category.
• Filtering before numbering in step 3.

In SQL Server

The CASE-based sums work unchanged; SUM(...) OVER () and ROW_NUMBER() too. Replace DATE_TRUNC('quarter', order_date) with DATETRUNC(quarter, order_date) (2022+) or DATEFROMPARTS(YEAR(order_date), (DATEPART(quarter, order_date) - 1) * 3 + 1, 1). To keep only the top 2 without QUALIFY, filter the ranked CTE in the outer query, as here.