A business question that touches several tables is never one SELECT. The board asked which regions fell short of their monthly revenue target, and who inside them is accountable for the gap. That question needs four tables, a month key, a comparison against a target, and a leaderboard inside each region. The real skill is not any single technique. It is the habit of pulling the question apart into steps, where every step is an honest query, and then putting the steps back together.
This is the first lesson in the SQL Projects for Practice module. The next lesson in this module is about "What Query Conventions Keep Team SQL Maintainable?".
How do you answer a multi-table business question end to end?
The furniture sales business from Module 11 now has a fifth and a sixth table. The customers table carries a segment for every customer, and the region_targets table holds a revenue target for every region and every month. Nothing else changes, and that matters, because the joins you already know still work exactly as they did before.
The question the board asked is the one above, and it sounds simple when you read it out loud. It is not simple in SQL, because it asks for three different things at once: a number, a comparison, and a name.
Breaking it into pieces is the whole job. Each piece becomes a query you can run on its own and check, and then you join the pieces back together in the order the board asked the question.
Step one: the revenue for each region and month
This step uses three joins and a GROUP BY, and it pulls the month key out of the sale date. This is Module 04 and Module 11 working together. The month key is what turns a list of sales into a series of months you can compare.
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.0The result has twelve rows, which is four regions times three months. Check that number before you go any further, because every later step depends on it.
Notice the two joins that exist only to reach the region. One joins sales to reps, and the other joins reps to regions. That chain is unavoidable, and it is exactly the kind of thing Module 26 taught you to check. A join that fans out would quietly double every revenue figure.
Step two: put the target beside the actual
The targets table is keyed by region and month. The revenue result is keyed by region and month too, so the join uses both columns. Join on one column only and the same target would be matched to every month.
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.0You get twelve rows again, now ordered by variance so that the worst month sits at the top. The board can read the problem before it reads the numbers.
There are two CTEs, and each one does a single job. The final SELECT does the comparison. That split is what makes the query easy to test, because each CTE can be run on its own.
Notice what has not happened yet. Nobody has been told who is responsible for the shortfall. A region can miss its target because one rep had a bad month, or because the whole region struggled. Those are different conversations, and only the third step tells them apart.
Step three: who is responsible inside each region?
This uses the same revenue calculation, grouped one level finer by rep, and then 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 2North is behind in April, and Bruno Kessler out-earned Ada Whitfield across the quarter. So the conversation is not simply that somebody underperformed. It is about a month that the whole region had.
That nuance is exactly why the ranking belongs in the answer and not in the reader's head. Leave it out and the reader is left to guess who to ask.
The whole answer, in one statement
Now the steps are assembled. The monthly revenue joins to the targets, the percentage of the target is computed, and a correlated subquery names the top rep for each region and each month. The subquery runs once per row, which is fine at twelve rows and worth thinking about at a million.
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 DemirOne statement answers the question in three parts: the money, the gap, and the person.
The structure is the thing to take away, and it comes down to four moves.
First, you extract the period key once, inside a CTE, so that every later step agrees on what a month is. If two steps each build their own month key in a different format, the join between them silently drops rows.
Second, you aggregate at the grain the question needs. That is region and month here, and rep and region for the leaderboard. An aggregate at the wrong grain is the most common cause of a wrong total.
Third, you join the fact to the target on the keys that both tables share. Never on a name that only looks like a key.
Fourth, you decorate with the window and the correlated functions that turn a number into an answer. The decoration comes last, so that the base numbers underneath it are already right.
There is also a discipline that makes the query 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. Nothing in the final statement is doing two jobs.
If the final number is wrong, you can run the parts and find out which part is wrong. That is the real difference between a query that answers a question and a query that merely returns rows. One of them you can debug in minutes.
Quick reference
- You break the question into steps, and each step is an honest query you can verify on its own.
- You extract the period key once and you reuse it, so that every step agrees on the grain.
- You aggregate at the grain the question needs, and then you join the facts to the targets on their shared keys.
- You decorate last. The window functions and the correlated subqueries turn the numbers into answers.
- You check the row count at every step, because a join that fans out changes every number that comes after it.
Analysis drill: you extend the report with a fourth part, which is the percentage of the company's revenue that each region's month represents. Then you write one sentence that explains why the company total has to be computed in a separate query level from the region filter.