A complete sales analytics project is not one clever query. It is a set of deliverables, each answering one question, each built on a schema designed for that question, and each verified on its own before it is combined. This capstone assembles the course into the pack a sales director would be handed.
What goes into a complete sales analytics project?
The brief: a furniture company wants a monthly review pack covering four regions — target versus actual, who carried it, the product mix, the trend, the customer segment, and who needs attention.
The schema the project rests on
Six tables, one kind of fact each. The one that shapes every query is the fact table, one row per sale, and here is the shape the engine actually holds for it:
PRAGMA table_info(sales);
cid name type notnull dflt_value pk
--- ----------- ------- ------- ---------- --
0 sale_id INTEGER 0 NULL 1
1 rep_id INTEGER 1 NULL 0
2 customer_id INTEGER 1 NULL 0
3 product_id INTEGER 1 NULL 0
4 sale_date TEXT 1 NULL 0
5 quantity INTEGER 1 NULL 0
6 unit_price REAL 1 NULL 0
7 discount REAL 1 0 0
The three reference columns point at the reps, the customers and the product lines, and the discount column is the only one with a default of its own. The other five tables are regions, reps, customers, product_lines and a region_targets table keyed by region and month. Three decisions carry the project: revenue is computed not stored, the month key is derived not stored, and the targets sit in their own table.
Deliverable one: the executive summary
Actual against target per region and month, with the variance ranked worst-first.
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
),
variance AS (
SELECT m.region_id, m.region_name, m.month_key, m.revenue, t.target_revenue,
m.revenue - t.target_revenue AS gap
FROM monthly m JOIN targets t
ON t.region_id = m.region_id AND t.month_key = m.month_key
)
SELECT region_name, month_key, ROUND(revenue, 2) AS revenue, target_revenue,
ROUND(gap, 2) AS variance,
ROUND(100.0 * revenue / target_revenue, 1) AS pct_of_target,
RANK() OVER (ORDER BY gap DESC) AS best_variance
FROM variance
ORDER BY gap DESC;
region_name month_key revenue target_revenue variance pct_of_target best_variance
----------- --------- ------- -------------- -------- ------------- -------------
North 2025-05 2279.0 1600.0 679.0 142.4 1
West 2025-04 2942.25 2600.0 342.25 113.2 2
North 2025-03 3053.9 2800.0 253.9 109.1 3
South 2025-04 1701.0 1500.0 201.0 113.4 4
East 2025-05 1083.0 950.0 133.0 114.0 5
East 2025-04 1416.0 1300.0 116.0 108.9 6
West 2025-03 1262.0 1200.0 62.0 105.2 7
South 2025-03 1549.5 1500.0 49.5 103.3 8
East 2025-03 456.0 500.0 -44.0 91.2 9
South 2025-05 1428.6 1500.0 -71.4 95.2 10
West 2025-05 840.0 950.0 -110.0 88.4 11
North 2025-04 859.5 1000.0 -140.5 86.0 12
monthly defines the grain once, targets isolates the comparison data, and variance is the only place the subtraction happens.
Deliverable two: who carried the region
A share of the region plus a running total, so a reader can see how quickly it was covered.
WITH rep_revenue AS (
SELECT rg.region_id, rg.region_name, r.rep_id, r.rep_name,
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 rg.region_id, rg.region_name, r.rep_id, r.rep_name
)
SELECT region_name, rep_name, ROUND(revenue, 2) AS revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region_id), 1) AS pct_of_region,
ROUND(SUM(revenue) OVER (PARTITION BY region_id ORDER BY revenue DESC), 2) AS running_region_total,
RANK() OVER (PARTITION BY region_id ORDER BY revenue DESC) AS rnk
FROM rep_revenue
ORDER BY region_name, rnk;
region_name rep_name revenue pct_of_region running_region_total rnk
----------- ------------- ------- ------------- --------------------- ---
East Greta Vogel 2955.0 100.0 2955.0 1
North Bruno Kessler 3273.5 52.9 3273.5 1
North Ada Whitfield 2918.9 47.1 6192.4 2
South Carla Nunes 2503.5 53.5 2503.5 1
South Dev Patel 2175.6 46.5 4679.1 2
West Elif Demir 2627.25 52.1 2627.25 1
West Farid Haddad 2417.0 47.9 5044.25 2
Three windows over one grouped result: a share of the partition total, a cumulative total down the ranked list, and the rank itself. The running total answers what the other columns cannot: in North one rep reached 52.9 percent alone.
Deliverable three: the product mix
A pivot, built with conditional aggregation.
SELECT rg.region_name,
ROUND(SUM(CASE WHEN p.category = 'furniture' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS furniture,
ROUND(SUM(CASE WHEN p.category = 'storage' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS storage,
ROUND(SUM(CASE WHEN p.category = 'accessories' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS accessories,
ROUND(SUM(CASE WHEN p.category NOT IN ('furniture','storage','accessories') THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS other
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
JOIN product_lines p ON p.product_id = s.product_id
GROUP BY rg.region_id, rg.region_name
ORDER BY rg.region_name;
region_name furniture storage accessories other
----------- --------- ------- ----------- -----
East 1923.0 456.0 0.0 576.0
North 1125.0 1065.0 2228.4 1774.0
South 1126.5 1605.0 1038.6 909.0
West 1382.25 2040.0 360.0 1262.0
East sells no accessories at all — a 0.0 rather than a blank, the difference between "we sold none" and "no data". North's 2228.40 is the largest cell in the pack, and invisible in the long format.
Deliverable four: the trend
Revenue per month per region, with the running total and a two-month moving average.
WITH monthly AS (
SELECT 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 rg.region_name, month_key
)
SELECT region_name, month_key, ROUND(revenue, 2) AS revenue,
ROUND(SUM(revenue) OVER (PARTITION BY region_name ORDER BY month_key), 2) AS running_total,
ROUND(AVG(revenue) OVER (PARTITION BY region_name ORDER BY month_key
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS moving_avg
FROM monthly
ORDER BY region_name, month_key;
region_name month_key revenue running_total moving_avg
----------- --------- ------- ------------- ----------
East 2025-03 456.0 456.0 456.0
East 2025-04 1416.0 1872.0 936.0
East 2025-05 1083.0 2955.0 1249.5
North 2025-03 3053.9 3053.9 3053.9
North 2025-04 859.5 3913.4 1956.7
North 2025-05 2279.0 6192.4 1569.25
South 2025-03 1549.5 1549.5 1549.5
South 2025-04 1701.0 3250.5 1625.25
South 2025-05 1428.6 4679.1 1564.8
West 2025-03 1262.0 1262.0 1262.0
West 2025-04 2942.25 4204.25 2102.13
West 2025-05 840.0 5044.25 1891.13
North's moving average falls from 1956.70 to 1569.25 even though May revenue of 2279.00 beats April's 859.50, because the average still carries March's exceptional 3053.90.
Deliverable five: who needs attention
The reps below their own region's average. This one needs a separate query level, because a window function cannot be filtered in the statement that computes it.
WITH rep_revenue AS (
SELECT rg.region_id, rg.region_name, r.rep_id, r.rep_name,
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 rg.region_id, rg.region_name, r.rep_id, r.rep_name
),
region_avg AS (
SELECT region_id, AVG(revenue) AS avg_revenue FROM rep_revenue GROUP BY region_id
)
SELECT rr.region_name, rr.rep_name, ROUND(rr.revenue, 2) AS revenue,
ROUND(ra.avg_revenue, 2) AS region_average,
ROUND(rr.revenue - ra.avg_revenue, 2) AS gap_to_average
FROM rep_revenue rr
JOIN region_avg ra ON ra.region_id = rr.region_id
WHERE rr.revenue < ra.avg_revenue
ORDER BY gap_to_average;
region_name rep_name revenue region_average gap_to_average
----------- ------------- ------- --------------- ---------------
North Ada Whitfield 2918.9 3096.2 -177.3
South Dev Patel 2175.6 2339.55 -163.95
West Farid Haddad 2417.0 2522.13 -105.13
One rep per region, each below their own region average. Written as a window function in the same query level this would be wrong — the average would be computed over the already-filtered rows. The fix is to compute the window one level up.
Deliverable six: the customer segment
The last cut asks a different question: not who sold, but to whom.
SELECT c.segment, COUNT(DISTINCT c.customer_id) AS customers,
COUNT(s.sale_id) AS orders,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue,
ROUND(AVG(s.quantity * s.unit_price * (1 - s.discount)), 2) AS avg_order_value
FROM sales s JOIN customers c ON c.customer_id = s.customer_id
GROUP BY c.segment ORDER BY revenue DESC;
segment customers orders revenue avg_order_value
------------- --------- ------ ------- ---------------
business 3 12 6619.75 551.65
consumer 3 11 6570.0 597.27
institutional 2 9 5681.0 631.22
Business and consumer are level on revenue, but the averages tell different stories: institutional customers buy least often and buy most when they do. A conclusion a revenue column alone would hide.
What made this project work?
- One schema, designed for the questions. Every deliverable is a different shape of the same normalised data.
- One grain per query, stated explicitly. Region and month for the summary, rep and region for the leaderboard, category for the mix.
- Named steps in named CTEs. Every intermediate result can be run alone, so a wrong number is traceable to one cause.
- Each technique where it belongs. Windows for comparisons, aggregates for totals.
- Derived fields, never stored ones. Revenue and variance cannot go stale.
- Verification at every step. Row counts checked and totals reconciled.
Each deliverable answers one question, written down before the SQL is. The pack is not six queries — it is six answers.
Quick reference
- Design the schema for the questions, not for the report.
- State the grain of every query and aggregate at exactly that grain.
- Compute windows in a separate level from any filter that depends on them.
- Use CTEs to name each step so a wrong number has one place to be wrong.
- Verify each deliverable before assembling the pack.
Capstone drill: extend the pack with a seventh deliverable — the top three products per region, using a window function over the grouped product revenue — and state in one sentence which ranking function you would use and why the answer must not contain gaps.