Analyst case study: the March revenue drop

Anyone can write SQL. An analyst decides which SQL to write, in what order, and knows when to stop. The trap with "revenue dropped, why?" is theorizing first - blaming a campaign, a price change, a season - before you have confirmed the drop is even real and pinned down where it lives. Tonight you run the discipline instead: confirm, drill, explain, recommend. The Daybreak numbers are small enough to hold in your head, so the method stays in focus, not the arithmetic.

Four moves, narrowing each time. You start wide (is the drop real?), narrow to the month, narrow to a behavior, then widen just enough to sanity-check - and only then do you write anything down. Skipping straight to "recommend" is how analysts get burned; the funnel keeps you honest.

Before any theory, get the actual monthly trend on screen. We sum completed line revenue per month, then use LAG (from b7) to show the month-over-month change in the same result. If March really dropped, the change column will show it in one negative number - no arguing with it.

WITH m AS (
  SELECT substr(o.order_date,1,7) AS mo,
         SUM(oi.quantity*oi.unit_price) AS rev
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  WHERE o.status = 'completed'
  GROUP BY mo
)
SELECT mo, rev,
       rev - LAG(rev) OVER (ORDER BY mo) AS change
FROM m
ORDER BY mo;

The drop lives in March, so zoom in. Two cheap questions answer most of it: what statuses do March's orders have (is there a refund distorting things?), and how many orders did March even get (was it a volume problem)? Small queries, big signal.

SELECT status, COUNT(*) AS n
FROM orders
WHERE order_date LIKE '2026-03%'
GROUP BY status;

SELECT COUNT(*) AS march_orders
FROM orders
WHERE order_date LIKE '2026-03%';

Orders tell you what happened; the events and subscriptions tables hint at why. If March saw support tickets and a cancel survey, and a subscription actually cancelled that month, you have a behavioral thread that lines up with the revenue dip.

SELECT event_type, COUNT(*) AS n
FROM events
WHERE event_date LIKE '2026-03%'
GROUP BY event_type;

SELECT sub_id, customer_id, cancel_date
FROM subscriptions
WHERE cancel_date IS NOT NULL;

One more angle before we write. Group customers by signup month - a "cohort" view. If new-customer intake sagged around March, that reinforces the volume story from Part 2. This is a widen-to-sanity-check move, not a new theory.

SELECT substr(signup_date,1,7) AS cohort,
       COUNT(*) AS new_customers
FROM customers
GROUP BY cohort
ORDER BY cohort;

The queries were the easy part. The value an analyst adds is the paragraph a founder reads in 30 seconds. Rule: one claim per finding, each backed by a specific query above. No adjectives, no drama, just what the data shows and one recommendation.

SELECT SUM(oi.quantity*oi.unit_price) AS march_completed_rev
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
  AND o.order_date LIKE '2026-03%';

• Re-run the Part 1 trend query, but change the status filter to include refunded orders. Note how the March number shifts, and write one sentence on why the honest version excludes them.
• Pick a different month (say May) and run the full funnel on it: confirm, drill by status, check events. Is there a story, or is it a quiet month?
• Write a three-sentence brief for your chosen month, one claim per sentence, each naming the query that backs it.
• Extend Part 3: join subscriptions to customers so your churned-subscriber finding includes the customer's name and plan.
• Bring one question to b10: your March queries feel instant on 33 orders - what happens when the table has 33 million rows? Next session is exactly that.

This session is a guided analytics investigation in the spirit of the major training curricula, run on live data instead of screenshots. It leans on every technique from b1-b7. This page covers: