Business analysis with SQL

01The problem

On Friday afternoon, the managing director stops by:

"I keep hearing we're doing well. Are we? Where's the business coming from, and where are we losing it?"

There's no single query for that. The real skill of an analyst isn't knowing every SQL function; it's turning a vague question like this into specific questions, answering each one correctly, and explaining the result in plain language.

02The concept

A reliable way to work:

1. Break the question down into things you can measure. "Are we doing well?" becomes: Is shipment volume growing? Which customers drive revenue? Are we delivering on time? Are customers paying?
2. Find the data for each one. Which tables and columns hold it?
3. Write the query, one step at a time, using CTEs.
4. Sense-check the answer. Does the total match a number you already know? Are there cancelled or unfinished records that should be excluded?
5. Present it: one sentence per finding, with the number that supports it.

03Example

Is volume growing? Compare each month of 2026 with the same month in 2025:

WITH monthly AS (
  SELECT
    strftime('%Y', booking_date) AS year,
    strftime('%m', booking_date) AS month,
    COUNT(*) AS shipments
  FROM shipments
  WHERE status <> 'Cancelled'
  GROUP BY year, month
)
SELECT
  cur.month,
  prev.shipments AS shipments_2025,
  cur.shipments  AS shipments_2026,
  ROUND(100.0 * (cur.shipments - prev.shipments) / prev.shipments, 1) AS change_pct
FROM monthly AS cur
JOIN monthly AS prev ON prev.month = cur.month AND prev.year = '2025'
WHERE cur.year = '2026'
ORDER BY cur.month;

Which customers drive revenue? What share of 2025's charges came from the ten biggest customers:

WITH by_customer AS (
  SELECT customer_id, SUM(freight_charge) AS charged
  FROM shipments
  WHERE status = 'Delivered' AND booking_date BETWEEN '2025-01-01' AND '2025-12-31'
  GROUP BY customer_id
),
ranked AS (
  SELECT charged, ROW_NUMBER() OVER (ORDER BY charged DESC) AS rn
  FROM by_customer
)
SELECT
  ROUND(100.0 * SUM(CASE WHEN rn <= 10 THEN charged ELSE 0 END) / SUM(charged), 1) AS top10_share_pct
FROM ranked;

04Walkthrough

The first query joins the monthly CTE to itself: once as cur (2026) and once as prev (2025), matched on the month number. It only covers months that exist in both years, which is exactly what a fair comparison needs. 100.0 * forces decimal division so the percentage isn't rounded to a whole number too early.

The second query answers a question managers care about more than they usually say: how dependent are we on a few customers? If ten customers bring in a large share of revenue, losing one of them hurts. The query ranks customers by charges, then compares the top ten's total with everyone's total.

Notice the definitions in each: the first excludes cancelled shipments from volume; the second counts only delivered shipments as revenue. Say so when you present the numbers.

A good write-up of these two results would look like:

Volume in 2026 is running [up/down] by about X% on the same months of 2025. Our ten largest customers produced Y% of 2025 revenue, so keeping them matters as much as winning new ones.

05Practice

06Challenge

07Check your understanding