Monday, 9:04 AM. The CEO's message: 'Board meeting Thursday. I need to know: is revenue actually growing, who are our best customers, which products carry us, and are people coming BACK? Numbers, not vibes.' No notebook, no BI tool set up — you have a database, SQL, and three days. This lesson is those three days.
What you'll learn
What
A complete business analysis conducted entirely in SQL against the e-commerce schema you know (customers, orders, order_items, products). You'll follow the professional arc: translate vague stakeholder questions into precise queries, profile the data before trusting it, build the four deliverables (revenue trend, customer segmentation, product Pareto, cohort retention), and package findings with the caveats that make them credible. Every technique comes from earlier lessons — this is where they compose.
Why
Interviews and jobs don't ask 'write a LEFT JOIN' — they hand you a fuzzy business question and watch whether you can decompose it, defend your definitions, and ship numbers someone can act on. The gap between knowing SQL and doing analysis is exactly this composition skill. Completing this project gives you a portfolio-grade artifact and the confidence that no stakeholder question needs to wait for 'the data team'.
Where it's used
Every analytics take-home exercise, the first month of every data job, board-deck preparation, and any moment someone senior says 'can you pull the numbers on that?'
Where this runs in production
E-commerce analyst interviews hand candidates an orders schema and questions like 'which categories drive repeat purchasing?' — this project is that exercise, rehearsed.
DTC brands live on repeat purchase rate; monthly cohort retention tables — the exact query you'll write — are standing agenda items in their growth reviews.
Retailers track how much revenue their top SKUs and top members represent; your Pareto query is the same math that justifies their famously curated product count.
Never report from data you haven't profiled. The 15-minute checklist from SQL for EDA, applied always: row counts per table; date range actually covered (MIN/MAX of created_at — is the last month partial?); NULL rates on the columns your analysis leans on; status value distribution (what fraction of orders are cancelled?); duplicate checks on keys; and referential spot-checks (order_items rows without a matching product?). Ten minutes here prevents the worst professional outcome: presenting numbers, then retracting them.
Individual SQL skills are trades — plumbing (joins), electrical (window functions), framing (aggregation). A project makes you the general contractor: sequencing the trades, inspecting each stage before building on it, and delivering something a client can walk into. Nobody hires a contractor because they own a saw; they hire the ability to turn 'we want a kitchen' into a finished room. This lesson is your first kitchen.
-- The project's backbone: one clean base, four analyses on top
WITH base_orders AS (
SELECT o.order_id, o.customer_id, o.created_at,
SUM(oi.quantity * oi.price_at_purchase) AS order_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'shipped' -- definition: revenue = shipped
AND o.created_at >= '2025-07-01' -- definition: last 12 full months
AND o.created_at < '2026-07-01' -- half-open, boundary-exact
GROUP BY o.order_id, o.customer_id, o.created_at
)
SELECT ... -- every deliverable selects FROM base_ordersArchitecture, not just a query: encode the definitions ONCE in a base CTE, then build all four deliverables on top of it. When the CFO asks 'what if we include pending orders?', you change one WHERE line and every number updates consistently — versus hunting through four scripts hoping you edited them all the same way. This is the CTE-pipeline pattern doing governance work.
1) Revenue trend: DATE_TRUNC('month') + SUM, then LAG for month-over-month growth — the string-and-date and window lessons. 2) Customer value: per-customer aggregates (recency, frequency, monetary), then NTILE or CASE to segment — GROUP BY plus CASE lessons. 3) Product concentration: revenue share per product with SUM() OVER () and a running cumulative share — window functions answering 'do 20% of products drive 80% of revenue?'. 4) Retention: first-order month per customer (MIN + DATE_TRUNC) joined back to all their orders — the cohort pattern from SQL for EDA, now producing the month-over-month comeback rates.
A deliverable is a number PLUS its meaning: not '412,000', but 'monthly revenue grew from ~$95k to ~$118k over the year (+24%), though growth stalled in the last quarter (-2% MoM average)'. For each deliverable, force yourself to write the one-sentence headline a busy executive retains. If you can't write the sentence, the query isn't done — you have data, not a finding yet.
If today is July 17, July's revenue covers 17 days — chart it next to full months and revenue appears to crash 45%, guaranteeing an alarmed executive. Every time-series deliverable must either exclude the current partial period (created_at < DATE_TRUNC('month', CURRENT_DATE)) or label it explicitly. The symmetric trap at the start: your earliest month may be partial too (when did data collection begin?). Check MIN(created_at) — it's one query and it's saved a thousand careers.
Credible analysis states its edges: 'revenue = shipped orders only (cancelled/pending excluded, ~6% of order volume)'; 'June 2026 cohort has one month of history — its retention is not yet comparable'; 'product returns are not modeled in this data'. Stating limits doesn't weaken the work — it's what separates analysis from a number someone will disprove in the meeting. The rule: every caveat you find in profiling either gets fixed in the query or written in the deliverable. Silent is the only wrong option.
The project's five stages. Click each to see what it produces and which earlier lessons power it.
Before any deliverable: pin the definitions, then verify the data can support them.
Definitions first — they're the contract the whole analysis honors. Then the profile: the date probe confirms the window is fully covered (and exposes partial edge months); the status distribution quantifies what 'shipped only' excludes. That 6.2% cancelled isn't discarded silently — it becomes a written caveat.
Your task
Mini end-to-end: from the base tables, produce the executive one-liner table — one row per month with revenue AND distinct active customers, shipped orders only, June 2026 window excluded from nothing (both months shown are complete). Fill the blanks: the join key, the status definition, the truncation grain, and the distinct-count target.
month | revenue | active_customers ---------+---------+------------------ 2026-05 | 310.00 | 2 2026-06 | 145.00 | 1 (2 rows)
Write your solution in the editor on the right, then hit Run.
Why does the project encode filters like status='shipped' in one base CTE instead of repeating them in each deliverable's query?
Your monthly revenue chart shows a 45% 'crash' in the current month. The most likely explanation?
In the Pareto deliverable, one row computes both ROUND(100.0*revenue/SUM(revenue) OVER (),1) and the same with SUM(revenue) OVER (ORDER BY revenue DESC). What's the difference?
In the retention grid, the June 2026 cohort's month-3 cell is blank. What does professional practice demand?
What's the strongest response?
The CEO asks a fifth question live: 'What share of each month's revenue comes from REPEAT customers (not on their first-ever order month)?' Using base_orders(order_id, customer_id, created_at, order_revenue), write the full query: per month, total revenue and repeat_share_pct — revenue from customers whose first-order month (MIN over their history) is EARLIER than that month, as a rounded percentage. Order by month.
Walk me through how you'd approach an open-ended take-home: 'here's our orders database — tell us how the business is doing.'
I'd work the arc this project drills. First, definitions: decide and WRITE DOWN what revenue means (which statuses, which price field), the time window, and the grain — because every downstream number inherits these and the review discussion will start there. Second, profile before trusting: date coverage (MIN/MAX — are edge months partial?), status distribution, NULL rates on load-bearing columns, duplicate keys, and orphaned foreign keys; each finding either gets handled in the query or written as a caveat. Third, architecture: one base CTE encoding the definitions, with every analysis selecting from it — so all numbers agree by construction. Fourth, the four canonical lenses: growth (monthly trend + MoM via LAG), customers (RFM segmentation — who drives revenue), products (Pareto concentration via cumulative window shares), and retention (first-order cohorts × months-since). Fifth, findings as sentences: each table compressed to one headline an executive can retell, plus the caveat block. What I'd deliberately NOT do: chart the current partial month, report undefined cohort cells as zero, or present a single number without its definition — those are the three classic take-home disqualifiers.
Your analysis says one thing; another team's dashboard says another. How do you handle the discrepancy — technically and politically?
Technically: reconcile definitions before touching either number. I diff the pipelines along the usual axes — status scope (pending? cancelled? refunded?), price source (list vs transacted), join fan-out (order-level vs item-level aggregation), time boundaries (timezone, half-open vs BETWEEN, partial periods), and dedup rules. Then the decisive move: re-run MY pipeline under THEIR definition. If I can reproduce their number, both systems are internally consistent and the difference is pure policy; if I can't, one of us has a genuine bug and the diff usually points at it (fan-out doubling and boundary days are the common culprits). Politically: frame it as convergence, not victory — 'both numbers are right under their own definitions; here's the one-line difference; which definition should the company standardize on?' hands leadership a decision instead of a dispute. Then the durable fix: the agreed definition gets encoded in one shared layer (a dbt model or governed view) so the same fight doesn't recur quarterly. Metric discrepancies are one of the highest-frequency real-world analyst situations — handling one calmly is worth more than any syntax knowledge.
This project used only SQL. Where would you draw the line between SQL and Python in a real version, and why?
SQL owns everything through the aggregated result tables: filtering, joining, cohort construction, window math. It runs where the data lives (no million-row transfers), it's set-based and declarative (the engine optimizes), and it's auditable — a reviewer reads one query and sees the whole definition of 'retention'. Python takes over at three natural seams: visualization (the retention triangle wants a heatmap; matplotlib/seaborn beat ASCII tables), statistics (is the 26%→34% retention improvement significant, or cohort-size noise? — that's a proportions test SQL can't express cleanly), and modeling (my RFM table is literally a feature matrix; churn prediction from it is sklearn's job). The anti-pattern I avoid in both directions: dragging raw rows into pandas to re-implement GROUP BY — slower, memory-bound, and unreviewable; and torturing SQL into statistical tests or plotting duty it wasn't built for. Rule of thumb: reduce with SQL until the data fits comfortably in memory AND the remaining work is statistical or visual — hand off exactly there. That handoff line is also precisely where this curriculum goes next.
Common Mistakes to Avoid
1) Querying before defining — 'revenue' without a written definition (statuses, price source, window) guarantees a reconciliation fight later. 2) Skipping the profile pass — partial edge months, cancelled orders, and orphaned rows contaminate every deliverable silently. 3) Charting the current partial month — the fake 45% crash that derails the meeting. 4) Duplicating filter logic across deliverables instead of one base CTE — slides drift apart and can't be trusted together. 5) Reporting undefined cohort cells as 0% — 'no data yet' and 'zero retention' are opposite findings. 6) Delivering tables without headline sentences — if you can't state the finding in one sentence, the analysis isn't finished.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'Play a CEO with a vague question and make me extract precise definitions before you let me query.' • 'Review my base CTE — what definition decisions am I making implicitly?' • 'Give me a metric discrepancy scenario and coach me through reconciling it.' • 'Quiz me on which deliverable (trend/RFM/Pareto/retention) answers each of 10 stakeholder questions.' • 'Interview mode: run me through this exact take-home, pushing back on my findings like a skeptical CFO.'
Glossary
Stakeholder brief — the fuzzy business questions an analysis must translate into precise definitions. Definitions block — the written contract (metric scope, grain, window) every query honors. Profiling — verifying coverage, NULLs, duplicates, and referential health before reporting. Base CTE — the single query layer encoding all definitions; deliverables select from it. Grain — the unit one row represents (order, customer-month, cohort). MoM growth — month-over-month change, computed with LAG. RFM — Recency/Frequency/Monetary customer segmentation. NTILE — window function dealing rows into equal buckets (deciles). Pareto analysis — cumulative-share ranking revealing concentration ('20% drives 80%'). Cohort — customers grouped by first-order period, tracked over months-since. Retention grid — cohorts × month-offset table of return rates. Partial period — an incomplete current/edge month; exclude or label, never compare. Reconciliation — reproducing another team's number under their definitions to isolate policy vs bug. Headline sentence — the one-line finding an executive retells; the test of a finished deliverable.
Recommended Resources
• Do it for real: load the Olist Brazilian e-commerce dataset (Kaggle) or the Chinook database and re-run this project's full arc against unfamiliar data — profile, define, four deliverables, caveats. That write-up IS a portfolio piece. • Read: 'Good Charts' (Berinato) for turning your result tables into the visuals the next domain teaches. • Reference: dbt's docs on metrics/semantic layers — the industrial version of your base-CTE definitions block. • Community: browse take-home write-ups on GitHub tagged 'SQL analysis' to calibrate what strong finished work looks like. • Next in DSM: the Data Visualization domain — your retention grids and trend tables become charts that persuade a room.
Recap
✓ Analysis starts with translation: written definitions (metric scope, grain, window) before any query — and profiling before any trust. ✓ One base CTE encodes the definitions; four deliverables select from it, so every slide agrees by construction. ✓ The canonical lenses: trend (DATE_TRUNC + LAG), customer value (RFM + NTILE), concentration (cumulative window shares), retention (first-order cohorts × months-since). ✓ Truth-telling mechanics: exclude partial periods, distinguish 'no data yet' from zero, attach every caveat you found. ✓ Discrepancies are definition diffs until proven otherwise — reconcile by reproducing the other number, then make the definition a policy decision. ✓ A deliverable = table + headline sentence + caveats; SQL reduces, Python visualizes and models from there. 🎉 CONGRATULATIONS — you've completed the entire SQL & Databases course! From SELECT to serializable transactions to a board-ready analysis: 22 lessons, every core skill of professional analytics SQL. You can profile an unknown database, join anything to anything, window your way through rankings and running totals, design and reason about schemas, and turn executive questions into defensible numbers. Next on your DSM path: the Data Visualization domain, where these result tables learn to persuade.
Run your code to see the output here.