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

You now know the whole toolkit: SELECT, WHERE, ORDER BY, aggregates, GROUP BY, HAVING, joins, CASE, subqueries, CTEs and window functions. This lesson is about using them together to answer a real business question, which is the actual job.

A method for vague questions

1. Break the question down into things you can measure. "Are we doing well?" becomes four questions:
   • Is shipment volume growing?
   • Which customers drive revenue, and how dependent are we on them?
   • Are we delivering on time?
   • Are customers paying, and how fast?
2. Agree definitions before you query (see below).
3. Find the data for each one: which tables and columns hold it?
4. Write the query, one step at a time, using CTEs.
5. Sense-check the answer against something you already know.
6. Present it: one sentence per finding, with the number that supports it.

Definitions first

The same data gives different answers depending on what you count. Decide, and write it down:

Sense-checking

Before you trust a result, check it against something simple. Do the parts add up to the whole?

SELECT status, COUNT(*) AS shipments
FROM shipments
GROUP BY status;

The four statuses should add up to the total number of shipments:

SELECT
  (SELECT COUNT(*) FROM shipments) AS total,
  (SELECT SUM(n) FROM (SELECT COUNT(*) AS n FROM shipments GROUP BY status)) AS sum_of_parts;

Both 2,683. Other quick checks: is any total negative that shouldn't be? Are there dates outside the range you expected? Does a join return more rows than the table you started from? A two-minute check catches most mistakes before your manager does.

Comparing like with like

2026 data runs to the end of August. Comparing all of 2025 with eight months of 2026 would make 2026 look like a collapse. Compare the same months in both years. Joining a monthly summary to itself, once as this year and once as last year, matched on the month, does exactly that.

03Example

1. Is volume growing? Each month of 2026 against 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;

And the eight months together:

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
  SUM(prev.shipments) AS jan_aug_2025,
  SUM(cur.shipments)  AS jan_aug_2026,
  ROUND(100.0 * (SUM(cur.shipments) - SUM(prev.shipments)) / SUM(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';

Up 2.9%: 1,019 shipments against 990.

2. How dependent are we on a few customers? The top ten customers' share of 2025 revenue:

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
  COUNT(*) AS customers,
  ROUND(100.0 * SUM(CASE WHEN rn <= 10 THEN charged ELSE 0 END) / SUM(charged), 1) AS top10_share_pct
FROM ranked;

Ten of 96 customers produced about a third of the revenue.

3. Are we delivering on time? By booking year:

SELECT
  strftime('%Y', s.booking_date) AS year,
  COUNT(*) AS delivered,
  ROUND(100.0 * SUM(CASE WHEN julianday(s.delivery_date) - julianday(s.ship_date) <= r.target_transit_days THEN 1 ELSE 0 END) / COUNT(*), 1) AS on_time_pct
FROM shipments AS s
JOIN routes AS r ON r.route_id = s.route_id
WHERE s.status = 'Delivered'
GROUP BY year;

75.6% in 2025, 78.4% so far in 2026: better, but still about one delivery in five is late.

4. Are customers paying? What's still owed on delivered shipments, summarising payments first to avoid double counting:

WITH paid AS (
  SELECT shipment_id, SUM(amount) AS paid
  FROM payments
  GROUP BY shipment_id
)
SELECT
  COUNT(*)                                     AS shipments_owing,
  SUM(s.freight_charge - COALESCE(p.paid, 0))  AS amount_owed
FROM shipments AS s
LEFT JOIN paid AS p ON p.shipment_id = s.shipment_id
WHERE s.status = 'Delivered'
  AND s.freight_charge - COALESCE(p.paid, 0) > 0;

And how quickly customers pay once a shipment is delivered:

SELECT
  ROUND(AVG(julianday(p.payment_date) - julianday(s.delivery_date)), 1) AS avg_days_to_pay
FROM payments AS p
JOIN shipments AS s ON s.shipment_id = p.shipment_id;

04Walkthrough

Each query uses one or two ideas from the course:

The answers become a short write-up for the managing director. Every sentence has its number and its definition:

Volume is up 2.9% on the same eight months of last year (1,019 shipments against 990, excluding cancellations).

Revenue is concentrated: our ten largest customers produced about a third (32.5%) of 2025's delivered revenue, so keeping them matters as much as winning new ones.

Reliability is improving but still a weakness: 78.4% of 2026 deliveries arrived within the route's target, up from 75.6%.

Collections: customers pay about 13 days after delivery on average, but ₦1.41 billion is still owed on 244 delivered shipments. Finance should chase the largest balances first.

That's the analyst's job: a vague question in, four clear answers out, each backed by a query anyone can rerun.

Common mistakes

05Practice

06Challenge

07More practice

Optional drills on the same skills. They don't count towards the certificate, but each one is a small, realistic request from someone at Harbourline. Do as many as you need until the pattern feels automatic.

08Check your understanding