A multi-table business question is never one SELECT. "Which regions are behind their monthly revenue target, and who inside them is responsible?" touches four tables, needs a month key, needs a comparison against a target, and needs a leaderboard inside each region. The skill is not any single technique — it is decomposing the question into steps, each of which is one honest query, and then reassembling them.
How do you answer a multi-table business question end to end?
The furniture sales business from module 11 now has a fifth and sixth table: customers, with a segment, and a region_targets table holding a revenue target for every region and every month. The question the board asked is the one above. Breaking it into pieces is the whole job.
Step one: the revenue, per region, per month
Three joins and a GROUP BY, with the month key extracted from the sale date. This is module 04 and module 11 working together:
SELECT rg.region_name, strftime('%Y-%m', s.sale_date) AS month_key,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_id, rg.region_name, month_key
ORDER BY rg.region_name, month_key;
region_name month_key revenue
----------- --------- -------
East 2025-03 456.0
East 2025-04 1416.0
East 2025-05 1083.0
North 2025-03 3053.9
North 2025-04 859.5
North 2025-05 2279.0
South 2025-03 1549.5
South 2025-04 1701.0
South 2025-05 1428.6
West 2025-03 1262.0
West 2025-04 2942.25
West 2025-05 840.0
Twelve rows — four regions times three months. Note the two joins that exist only to reach the region: sales to reps, reps to regions. That chain is unavoidable, and it is exactly the kind of thing module 26 taught to check.
Step two: put the target beside the actual
The target table is keyed by region and month, and the revenue result is keyed by region and month, so the join is on both columns:
WITH monthly AS (
SELECT r.region_id, rg.region_name, strftime('%Y-%m', s.sale_date) AS month_key,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY r.region_id, rg.region_name, month_key
),
targets AS (
SELECT region_id, month_key, target_revenue FROM region_targets
)
SELECT m.region_name, m.month_key, m.revenue, t.target_revenue,
ROUND(m.revenue - t.target_revenue, 2) AS variance
FROM monthly m
JOIN targets t ON t.region_id = m.region_id AND t.month_key = m.month_key
ORDER BY variance;
region_name month_key revenue target_revenue variance
----------- --------- ------- -------------- ---------
North 2025-04 859.5 1000.0 -140.5
West 2025-05 840.0 950.0 -110.0
South 2025-05 1428.6 1500.0 -71.4
East 2025-03 456.0 500.0 -44.0
South 2025-03 1549.5 1500.0 49.5
West 2025-03 1262.0 1200.0 62.0
East 2025-04 1416.0 1300.0 116.0
East 2025-05 1083.0 950.0 133.0
South 2025-04 1701.0 1500.0 201.0
North 2025-03 3053.9 2800.0 253.9
West 2025-04 2942.25 2600.0 342.25
North 2025-05 2279.0 1600.0 679.0
Twelve rows again, ordered by variance so the worst month is at the top. Two CTEs, each doing one job, and the final SELECT doing the comparison. Notice what has not happened yet: nobody has been told who is responsible for the shortfall. That is the third step.
Step three: who is responsible inside each region?
The same revenue calculation, grouped one level finer — by rep — and ranked within the region:
SELECT rg.region_name, r.rep_name,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue,
RANK() OVER (PARTITION BY rg.region_id
ORDER BY SUM(s.quantity * s.unit_price * (1 - s.discount)) DESC) AS rnk
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
ORDER BY rg.region_name, rnk;
region_name rep_name revenue rnk
----------- ------------- ------- ---
East Greta Vogel 2955.0 1
North Bruno Kessler 3273.5 1
North Ada Whitfield 2918.9 2
South Carla Nunes 2503.5 1
South Dev Patel 2175.6 2
West Elif Demir 2627.25 1
West Farid Haddad 2417.0 2
North is behind in April, and Bruno Kessler out-earned Ada Whitfield across the quarter — so the conversation is not simply "someone underperformed". That nuance is exactly why the ranking belongs in the answer and not in the reader's head.
The whole answer, in one statement
Now the steps are assembled. The monthly revenue and the targets are joined, the percentage of target is computed, and a correlated subquery names the top rep for each region and month:
WITH monthly AS (
SELECT r.region_id, rg.region_name, strftime('%Y-%m', s.sale_date) AS month_key,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY r.region_id, rg.region_name, month_key
),
targets AS (
SELECT region_id, month_key, target_revenue FROM region_targets
)
SELECT m.region_name, m.month_key,
ROUND(m.revenue, 2) AS revenue,
t.target_revenue,
ROUND(m.revenue - t.target_revenue, 2) AS variance,
ROUND(100.0 * m.revenue / t.target_revenue, 1) AS pct_of_target,
(SELECT r2.rep_name
FROM sales s2 JOIN reps r2 ON r2.rep_id = s2.rep_id
WHERE r2.region_id = m.region_id AND strftime('%Y-%m', s2.sale_date) = m.month_key
GROUP BY r2.rep_id, r2.rep_name
ORDER BY SUM(s2.quantity * s2.unit_price * (1 - s2.discount)) DESC
LIMIT 1) AS top_rep
FROM monthly m
JOIN targets t ON t.region_id = m.region_id AND t.month_key = m.month_key
ORDER BY m.region_name, m.month_key;
region_name month_key revenue target_revenue variance pct_of_target top_rep
----------- --------- ------- -------------- -------- ------------- -----------
East 2025-03 456.0 500.0 -44.0 91.2 Greta Vogel
East 2025-04 1416.0 1300.0 116.0 108.9 Greta Vogel
East 2025-05 1083.0 950.0 133.0 114.0 Greta Vogel
North 2025-03 3053.9 2800.0 253.9 109.1 Ada Whitfield
North 2025-04 859.5 1000.0 -140.5 86.0 Bruno Kessler
North 2025-05 2279.0 1600.0 679.0 142.4 Bruno Kessler
South 2025-03 1549.5 1500.0 49.5 103.3 Dev Patel
South 2025-04 1701.0 1500.0 201.0 113.4 Dev Patel
South 2025-05 1428.6 1500.0 -71.4 95.2 Carla Nunes
West 2025-03 1262.0 1200.0 62.0 105.2 Elif Demir
West 2025-04 2942.25 2600.0 342.25 113.2 Farid Haddad
West 2025-05 840.0 950.0 -110.0 88.4 Elif Demir
One statement answering the question in three parts: the money, the gap, and the person. The structure is the thing to take away, and it has four moves. Extract the period key once, in a CTE, so every later step agrees on what a month is. Aggregate at the grain the question needs — region and month here, rep and region for the leaderboard. Join the fact to the target on the keys both tables share. Decorate with the window and correlated functions that turn a number into an answer.
And the discipline that makes it reviewable: every intermediate result is a query you can run on its own. The monthly CTE is step one, the targets CTE is a table you already had, and the final SELECT is a join between two things you have both already verified. If the final number is wrong, you can run the parts and find out which one is wrong — which is the real difference between a query that answers a question and a query that merely returns rows.
Quick reference
- Break the question into steps, each of which is one honest query you can verify alone.
- Extract the period key once and reuse it, so every step agrees on the grain.
- Aggregate at the grain the question needs, then join facts to targets on their shared keys.
- Decorate last: window functions and correlated subqueries turn numbers into answers.
- Check the row count at every step — a join that fans out changes every number downstream.
Analysis drill: extend the report with a fourth part — the percentage of the company's revenue that each region's month represents — and write one sentence explaining why the company total has to be computed in a separate query level from the region filter.