"Final project: commercial health check"

01The problem

Harbourline Freight's managing director is preparing for a board meeting and asks for a commercial health check:

"Are we growing? Are our customers staying? Are we delivering on our promises? And are we actually getting paid? I want numbers I can defend, with the SQL behind them so the finance team can check them."

That's four questions, and each one needs a pattern from this course. The board will push back on any number that looks odd, so the data has to be checked first and the definitions written down. The full brief and submission are on the course's project page; this lesson gets you started.

02The concept

From questions to patterns

What makes it board-ready

• Definitions first: what counts as revenue (charges on delivered shipments, or cash received?), on time, active and the "as of" date (31 August 2026).
• Like for like: no comparison of a full year with eight months.
• Readable SQL: one CTE per step, named for what it holds, with a comment where a definition matters.
• Reconciled totals: the aging buckets add up to the outstanding total, and the customer list adds up to the company total.
• Sentences, not just tables: each result followed by what it means and what to do about it.

03Example

A first look at the delivery question: on-time rate by mode, 2025 against 2026 (both years by delivery date, as of 31 August 2026).

WITH delivered AS (
  SELECT
    r.mode,
    strftime('%Y', s.delivery_date) AS year,
    -- on time = transit days <= the route's target
    julianday(s.delivery_date) - julianday(s.ship_date) <= r.target_transit_days AS on_time
  FROM shipments AS s
  JOIN routes AS r ON r.route_id = s.route_id
  WHERE s.status = 'Delivered'
)
SELECT
  mode,
  SUM(year = '2025') AS delivered_2025,
  ROUND(100.0 * SUM(CASE WHEN year = '2025' THEN on_time END) / SUM(year = '2025'), 1) AS on_time_2025,
  SUM(year = '2026') AS delivered_2026,
  ROUND(100.0 * SUM(CASE WHEN year = '2026' THEN on_time END) / SUM(year = '2026'), 1) AS on_time_2026
FROM delivered
GROUP BY mode
ORDER BY mode;

Road and sea improved, but air fell from 80.4% to 68.9% on time. Rates are shares, not counts, so comparing a full year with eight months is fair here. The counts still matter, though: air had only 153 and 122 deliveries, so part of a change that size could be noise. The Statistics for Data Analysis course shows how to test whether a change like that is real.

04Walkthrough

1. Write your definitions in a comment block at the top of a SQL file: revenue, on time, active customer and the "as of" date.
2. Run the data-quality checks from lesson 3 and note what you found and decided.
3. Build the year-to-date pivot (lesson 7) and the monthly moving average (lesson 4) to answer "are we growing?".
4. Build the cohort table (lesson 6) and the list of lapsed customers.
5. Build the aging report (lesson 10) and reconcile it with the outstanding total.
6. Open the project brief on the course page and check that every task has a query.

05Practice

06Challenge

07Check your understanding