Level 1 · Holiday season review in SQL

How an analyst reads this ticket

Megan’s message contains three questions at three different cuts of the data: time, category and channel. Before writing any SQL, pin down the one definition all three share, because a review in which the monthly total and the category total disagree is worse than no review:

• Revenue = net line revenue: quantity * unit_price * (1 - discount_pct / 100.0). discount_pct is stored in percentage points (15 means 15%), so dividing by 100 is not optional.
• Which orders: completed only. Cancelled orders never shipped, refunded orders were paid back and pending orders may still fail. Including them inflates December the most, because pending orders cluster in the last days of the year.
• Which lines: quantity > 0. A handful of lines have zero or negative quantities from data entry errors.
• Not revenue: shipping_fee. It lives on orders, one per order, and has its own question.

Write the definition into the top of your notebook or query file. Then each step is just a different GROUP BY on the same base.

Two tools this case uses

Month labels with strftime. strftime(date, format) turns a date or timestamp into text in the shape you ask for. %Y is the four-digit year and %m the two-digit month:

SELECT order_id, order_date, strftime(order_date, '%Y-%m') AS month
FROM orders
ORDER BY order_id
LIMIT 2;

order_id	order_date	month
1001	2023-01-01 20:51:19	2023-01
1002	2023-01-02 07:58:46	2023-01

Because '2025-09' sorts before '2025-10' as text, ORDER BY month puts these labels in calendar order. You can GROUP BY the alias month in DuckDB.

Naming a step with WITH. A CTE (common table expression) gives a query a name so the next query can read from it like a table. It is handy when you want to aggregate first and work with the totals after:

WITH by_month AS (
  SELECT strftime(order_date, '%Y-%m') AS month, COUNT(*) AS orders
  FROM orders
  WHERE status = 'completed'
  GROUP BY month
)
SELECT month, orders
FROM by_month
WHERE orders > 600
ORDER BY month;

The part in brackets runs first and produces a small table called by_month, one row per month; the SELECT after it filters that table and returns three busy months: 2024-12 (629), 2025-11 (651) and 2025-12 (634). Steps 2 and 3 use exactly this: a CTE holds the unrounded revenue per period, and the outer SELECT rounds and computes the growth.

Step 1: the season against the autumn

The base is order_items joined to orders. Each line has exactly one order, so the join adds the status and date without adding rows. Counting orders is where the grain bites: after the join there is one row per line, so COUNT(*) counts lines, not orders. COUNT(DISTINCT o.order_id) is the fix, and average order value is revenue divided by that distinct count.

Grouping by strftime(o.order_date, '%Y-%m') gives sortable text months. Filter the date with a half-open range (>= '2025-09-01' AND < '2026-01-01') so the last evening of December, a timestamp like 2025-12-31 21:14, is not lost.

Step 2: year over year by category

Comparing two periods side by side is conditional aggregation: one SUM(CASE WHEN ... END) per period. Rows outside both seasons give NULL inside the CASE and SUM ignores them. A CTE keeps the raw sums unrounded so the growth percentage is calculated on exact values; rounding first and then dividing can move the last decimal.

Holiday growth is a year-over-year question, not a month-over-month one: comparing December with November tells you about the calendar, comparing December 2025 with December 2024 tells you about the business.

Step 3: did December beat November?

Same pattern, two months, grouped by channel. Expect December to be close to November: Black Friday and Cyber Monday land in late November, while December has the HOLIDAY20 push and a dead Christmas Eve and Day. Which channel grew is the useful part of the answer.

SQL and pandas side by side

Idea	SQL	pandas
line revenue	expression inside SUM	a new column with assign
month label	strftime(order_date, '%Y-%m')	order_date.dt.strftime('%Y-%m')
distinct orders	COUNT(DISTINCT order_id)	("order_id", "nunique")
two periods as columns	SUM(CASE WHEN ...)	filter, then pivot_table

Pitfalls

• Joining orders to order_items and then summing shipping_fee counts each fee once per line.
• BETWEEN '2025-12-01' AND '2025-12-31' on a timestamp stops at midnight on the 31st.
• Integer division is not a risk here because the expression is already a decimal, but multiplying by 100.0 rather than 100 keeps percentage maths safe everywhere.