Analysis project in SQL

Why it matters

In a job, nobody asks you to "write a CTE with a CASE expression". They ask a business question in plain language: "Which regions are growing?", "Did the spring promotion work?", "Which customers should account managers call this week?". Turning that question into a correct, readable query, and then into a clear answer, is the real skill. This capstone walks through the whole process once, then the Do tab asks you to finish it.

The question

The sales director asks: "Which regions grew revenue from Q1 to Q2 this year, and by how much?"

The data lives in three tables:

Step 1: pin down the definitions

Before writing SQL, turn every vague word into a rule, and confirm the rules with the person asking:

• Revenue: quantity * unit_price summed over order lines. (Not including tax or shipping, which are not in these tables.)
• Which orders count: everything except status = 'cancelled'.
• Q1 and Q2: January 1 to March 31 and April 1 to June 30, 2026, by order_date.
• Growth: (Q2 - Q1) / Q1, as a percentage.
• Region: the customer's region (a customer's region at the time of the order might differ, but we only have the current value; note this as a limitation).

Write these as comments at the top of the query. They are part of the answer.

Step 2: explore the data

Quick checks first, so surprises do not end up in the final number:

SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT MIN(order_date), MAX(order_date) FROM orders;
SELECT COUNT(*) FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;   -- orphan orders?

Here you would discover that there are cancelled orders to exclude and an order in July that must not leak into Q2. This is the data quality lesson in action.

Step 3: build the query in steps

Order lines with region. Join the three tables and compute line revenue, excluding cancelled orders:

WITH order_lines AS (
  SELECT c.region,
         o.order_date,
         i.quantity * i.unit_price AS line_revenue
  FROM orders AS o
  JOIN customers AS c ON c.id = o.customer_id
  JOIN order_items AS i ON i.order_id = o.id
  WHERE o.status <> 'cancelled'
)
SELECT * FROM order_lines LIMIT 10;   -- check, then continue

One row per region with both quarters. This uses conditional aggregation: a CASE inside SUM puts each line in the right quarter column, and lines outside both quarters add 0.

by_region AS (
  SELECT region,
         SUM(CASE WHEN order_date BETWEEN '2026-01-01' AND '2026-03-31'
                  THEN line_revenue ELSE 0 END) AS q1_revenue,
         SUM(CASE WHEN order_date BETWEEN '2026-04-01' AND '2026-06-30'
                  THEN line_revenue ELSE 0 END) AS q2_revenue
  FROM order_lines
  GROUP BY region
)

BETWEEN includes both ends. That is correct for plain dates like '2026-03-31', but if order_date held timestamps such as '2026-03-31 14:20', that value is greater than '2026-03-31' and would be missed. With timestamps, use order_date >= '2026-01-01' AND order_date < '2026-04-01' instead.

Growth and sort. The final SELECT computes the percentage and orders the regions from fastest to slowest growth:

SELECT region, q1_revenue, q2_revenue,
       ROUND((q2_revenue - q1_revenue) * 100.0 / NULLIF(q1_revenue, 0), 1) AS growth_pct
FROM by_region
ORDER BY growth_pct DESC;

NULLIF(q1_revenue, 0) protects against a region that had no Q1 sales: its growth becomes NULL (undefined) instead of an error or a nonsense number. SQLite and MySQL treat NULL as smaller than any value, so with ORDER BY growth_pct DESC such regions appear last; PostgreSQL treats NULL as larger, so they would appear first unless you add NULLS LAST.

Step 4: sanity-check the result

Before sending anything:

• Does the sum of q1_revenue across regions match a simple total of Q1 revenue computed another way?
• Does any region have very few orders, so a big percentage is really a small change? (East going from 75 to 165 is +120%, but only 90 in money.)
• Are cancelled orders really excluded? Count them separately.

Step 5: communicate the answer

A query result is not an answer. Write two or three sentences a director can act on, with the caveats:

South grew fastest (+144%, 180 to 440), followed by East (+120%, from a small base) and North (+68%). West fell 92%, because one large Q1 order (a standing desk) was not repeated. Cancelled orders are excluded; figures use each customer's current region.

Attach the query, with its definition comments, so anyone can reproduce or audit the numbers.

A reusable checklist

1. Restate the question and agree on definitions.
2. Explore and quality-check the tables involved.
3. Build with CTEs, checking each step.
4. Sanity-check totals and small bases.
5. Write the answer in plain language with caveats, and share the query.

Try it

Open the Do tab. The order-lines step is written; add the cancelled-order filter, split revenue into Q1 and Q2, and compute and sort the growth.