Level 2 · Build the monthly review in SQL

What you are checking, and why

A month is ready to report only when its orders have settled. Orders arrive as pending and become completed, cancelled or refunded over the following days. Counting statuses per month shows where the open orders sit, using the same conditional-count pattern as any pivot:

SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending

November has 2 pending orders out of 715; December has 94 out of 777. That is the evidence behind reporting November.

One CTE per source

The KPIs come from three tables at three grains: order lines, returns and web sessions. The safe pattern is to bring each to one row per month in its own CTE, then join the CTEs on month:

sales   AS (... FROM lines ... GROUP BY month),
refunds AS (... FROM returns GROUP BY month),
web     AS (... FROM web_sessions GROUP BY month)

New buyers need one more step. “New” means the customer’s first completed order falls in the month, so a firsts CTE finds MIN(order_date) per customer, and the sales CTE counts the buyers whose first order month equals the order month. Returning buyers are the rest.

Reading the result

month	net_revenue	orders	avg_order_value	buyers	web_conversion_pct
2024-11	319,297.22	564	566.13	357	3.50
2025-10	174,599.43	333	524.32	261	2.33
2025-11	292,364.40	651	449.10	442	4.45

Against October everything is up, as it is every November. Against last year, volume grew and revenue fell, so the question becomes average order value. Step 3 answers it: seven bulk orders last November, one this November.

Explaining the mover

Step 3 works at the order level, because the size of an order is only known once its lines are added up. A CTE totals each order, the outer query labels it with CASE WHEN and groups the orders by month and band. The result splits each November into a few large orders and many regular ones, which is the breakdown that turns “revenue fell” into a reason leadership can act on.

Pitfalls

• strftime on a timestamp works the same as on a date, so returned_on and order_date give the same 'YYYY-MM' labels and the CTEs join cleanly.
• COUNT(DISTINCT customer_id) per month counts buyers; summing buyers across months double counts people who bought in both.
• Labelling order size on lines rather than orders puts big orders in the wrong band.