Eight lessons, about an hour and a half. You'll query Sahara Mart's real transaction database — nine branches, eight weeks, over 7,000 transactions — and learn to trust what you get back from it.
Open the Sahara Mart database and you'll find four tables: branches, products, transactions, and promotions. Before you write a single query against them, spend five minutes understanding what each row actually represents — this is the SQL equivalent of module 1's discipline: know the question, and here, know the data, before you touch the tool.
| branches | One row per branch — 9 rows total. branch_id is the key everything else joins against. |
| products | One row per SKU — 25 rows. sku is the key. Note: unit_price lives here, not in transactions. |
| transactions | One row per sale — 7,395 rows. This is the grain that matters: one row is one transaction, not one day, not one branch-day. |
| promotions | One row per promotion period — 5 rows, and critically, some SKUs have more than one promotion row with overlapping dates. |
The single most important question about any table is: what does one row actually mean? Get that wrong and every aggregate you build on top of it is wrong too.
That last row in the table above is not incidental — it is the whole reason lesson 3.3 exists. Two promotion rows can genuinely both apply to the same SKU on overlapping dates in this database, on purpose, because that happens in Sahara Mart's real promotions calendar when Head Office and a regional manager both schedule a discount without checking each other's calendar first.
Before querying an unfamiliar table, run SELECT * FROM transactions LIMIT 5; and just look. Then run SELECT COUNT(*) FROM transactions; and SELECT COUNT(DISTINCT transaction_id) FROM transactions; — if those two numbers differ, you already know something about duplicates before you've written a real query.
The basic shape you'll write more than any other: SELECT columns FROM table WHERE condition ORDER BY column;. Straightforward — the part worth real attention is "filtering honestly," because WHERE clauses are where wrong numbers most often sneak in quietly.
SELECT * FROM transactions WHERE transaction_type = 'SALE';
Correct — this excludes VOID rows, which is almost always what you want for a revenue total. The trap is forgetting this filter exists at all, three queries later, once you've copy-pasted a WHERE clause from an earlier query that filtered on something else and dropped this condition without noticing.
Date filtering deserves its own care. WHERE txn_date = '2026-07-13' matches one exact day; WHERE txn_date >= '2026-07-06' AND txn_date < '2026-07-13' matches a week — note the strict less-than on the end date, which avoids the classic off-by-one error of accidentally including the first day of the following week.
"Honestly" here means the filter matches what you'd tell Chidi you filtered, in plain language. A query that silently excludes VOID transactions is filtering correctly but reporting nothing about it — say so in whatever you hand back, even in one clause: "excluding voided transactions."
This is the lesson the promotions table exists for. Watch what happens when you join transactions to promotions the way it looks natural to write it.
SELECT SUM(t.revenue) FROM transactions t JOIN promotions p ON t.sku = p.sku WHERE t.sku = 'SKM001';
SKM001 has two promotion rows with overlapping dates. Every transaction row for SKM001 now matches both promotion rows, so it appears twice in the joined result — and SUM(t.revenue) is now roughly double the true figure. The query runs without error. Nothing warns you. The number is simply wrong.
A JOIN that matches more than one row on the other side silently multiplies every value from this side. SQL will never warn you — it will just hand back a bigger, wrong number with total confidence.
If the real intent is "was this transaction covered by a promotion active on that date," the join needs the date range in its ON clause, not just the SKU: JOIN promotions p ON t.sku = p.sku AND t.txn_date >= p.start_date AND t.txn_date < p.end_date. This still isn't fully safe against the two overlapping July promotions — a transaction on 22 July for SKM001 genuinely falls inside both — which means the real answer to "which discount applied" may require a business rule (e.g. "the higher discount always wins") that no query alone can supply. That is exactly the kind of judgement call worth surfacing to Chidi rather than silently picking one.
After any JOIN, compare COUNT(*) before and after. If the row count grew, some row on the joined-in side matched more than once — investigate before trusting anything summed downstream of it. This single check catches the majority of wrong-join errors before they reach a report.
SELECT branch_id, SUM(revenue) FROM transactions GROUP BY branch_id; is the SQL equivalent of a pivot table, and it's usually the first place a wrong-but-plausible number gets produced, because a wrong GROUP BY still returns nine tidy rows that look completely reasonable on a screen.
Group VOID transactions in along with SALE and every branch's total is inflated — and because branches with more VOID activity are inflated by different amounts, the relative ranking between branches can shift too, not just the absolute numbers. This passes a casual glance every time, because nine plausible-looking numbers in a plausible order is exactly what a reviewer expects to see.
SELECT sku, SUM(qty) FROM transactions WHERE transaction_type='SALE' GROUP BY sku HAVING SUM(qty) > 500;
WHERE filters rows before grouping (here, excluding VOIDs). HAVING filters groups after aggregation (here, keeping only high-volume SKUs). Swap them — putting the qty condition in WHERE — and it fails, because WHERE can't reference an aggregate that doesn't exist yet at that stage of the query.
Chidi's actual question is rarely "what's the total" — it's usually "how does this compare," which needs a value from a different row visible alongside the current one. That's what window functions are for, and they solve it without collapsing the detail the way GROUP BY does.
SELECT branch_id, week, revenue, RANK() OVER (PARTITION BY week ORDER BY revenue DESC) AS branch_rank FROM weekly_branch_revenue;
PARTITION BY resets the ranking for each week separately — without it, you'd get one ranking across every branch-week combination mixed together, which answers a different, less useful question.
SELECT txn_date, daily_revenue, SUM(daily_revenue) OVER (ORDER BY txn_date) AS running_total FROM daily_revenue;
Each row still shows its own day's number, with the cumulative total riding alongside it — impossible to get from a plain GROUP BY, which would only give you the final total, discarding every day along the way.
The difference between GROUP BY and a window function is exactly the difference between "collapse the detail into a total" and "keep every row, and add a total column."
A Common Table Expression — the WITH clause — names an intermediate result and lets you build a complex query as a sequence of readable steps instead of one deeply nested block. This matters more for the human reading it later than for the database running it.
| Nested | A subquery inside a subquery inside a WHERE clause, requiring the reader to work from the innermost parentheses outward to understand what's happening. |
| With CTEs | WITH clean_sales AS (...), branch_totals AS (SELECT branch_id, SUM(revenue) FROM clean_sales GROUP BY branch_id) SELECT * FROM branch_totals ORDER BY 2 DESC; — each named step reads top to bottom, in the order a person would naturally explain it out loud. |
The rule of thumb: if you'd need to explain a query out loud to Chidi, and that explanation has more than one "and then," write it as a CTE with one named step per "and then." Six months from now, you — or whoever inherits this query — will read the CTE names first and understand the shape of the query before reading a single line of logic.
Sahara Mart's transactions table has thousands of rows now, and will have millions once every branch's till is properly connected. A query that runs fine today can quietly become the query that locks up the reporting database for everyone else once the row count grows — and the fixes are mostly simple, once you know to look for them.
If a query is going to scan the whole transactions table, run it against a date-filtered sample first, confirm the logic is right, then widen it — rather than testing a brand-new complex query directly against the full history during business hours.
Whether it's a job interview or Chidi standing behind you asking for a number live, the skill is the same: narrating your reasoning while you build the query, rather than typing in silence and hoping the first attempt is right.
"First I need this month's date range, and I need to exclude VOIDs — that's my WHERE clause. Then I need Category, which isn't in transactions, so that's a JOIN to products on sku. Then I group by branch and sum revenue, filtered to Beverages only. Let me check the row count before and after the join first, in case beverages products fan out unexpectedly against anything." — then actually run that check before trusting the final number.
Notice that the narration is doing real work, not just filling silence — it surfaces the WHERE clause decision, the JOIN, and the fan-out check as things to verify, in the order a careful analyst would actually think through them. An interviewer, or Chidi, hears every judgement call being made rather than getting a query dropped on them with no visibility into whether the traps from this whole module were considered.
Have the Sahara Mart database open in a SQL tool that can read a SQLite file — DB Browser for SQLite, DBeaver, or any tool that opens .db files directly.
Eight questions Chidi has genuinely asked. For each, write the query, run it against the Sahara Mart database, and write one line explaining exactly what it counts — including any filter decisions you made.
Whether the habits from all eight lessons show up together on real, slightly messy data — filtering honestly, catching a fan-out join before it corrupts a total, choosing GROUP BY versus a window function correctly, and writing something the next analyst could actually read. Every one of these questions has a plausible-looking wrong answer that a rushed query would happily return.
Keep your query file — module 4 turns to data cleaning and judgement calls on messier, less structured data than this clean database, and several of the same instincts (know what a row means, check before you trust) carry straight over.