Module 13 Activity
Scenario
This is the deliverable the whole course has been building toward. You are handing an analysis to a sceptical reviewer who will not be able to ask you anything. Everything they need to trust it - or to reject it - must be in what you send.
Task
Work through the five stages in order. Do not start writing the memo before stage 3 is done.
- Frame eight questions. Each one must fix a population, a grain, and a measure. Use the eight in
Unit 13.01 or substitute your own to the same standard.
- Build one analysis-ready dataset as staged CTEs, with a grain check after every join. Answer all
eight questions from that single dataset.
- Reconcile every figure you intend to report: parts against wholes, at least one independent source,
and a magnitude check.
- Write the evidence memo - the headline figures, the population each covers, the checks you ran, and
the limitations.
- Review your own work as a sceptic, using the checklist below, and fix what it surfaces.
Deliverable
Three files:
capstone.sql- the staged query, commented with the grain at each stagecapstone-memo.md- at most one page, with every figure carrying its populationcapstone-checks.sql- the reconciliation queries and their expected results
Check your work
Your dataset must hold 1,000 rows. Anything else means a join changed the grain.
| Question | Answer |
|---|---|
| Orders placed / completed | 1,000 / 988 |
| Booked revenue | ₹27,01,463 |
| Completed revenue | ₹26,85,905 |
| Average completed order value | ₹2,718.53 |
| Customers who never ordered | 3,812 |
| Items sold | 3,400 |
| Payments received minus revenue owed | ₹0.00 |
| Customer country coverage | 93.8% |
Marking rubric
| Criterion | What earns it |
|---|---|
| Questions are answerable | Each names population, grain, and measure |
| Dataset is correct | Grain checked after every join; 1,000 rows |
| Figures reconcile | Parts sum to wholes; one independent source agrees |
| Limitations are stated | Missing countries, pending orders, and the line-item discrepancy all appear |
| Memo is self-contained | A reader can verify every number without asking a question |
| Normalisation is documented | The city rule is stated, applied once, and its gaps acknowledged |
The sceptic's checklist
Read your own memo and ask, for each number: what population is this over? What would make it wrong? Which check would have caught that? If any figure has no answer to the third question, it is not yet evidence - it is just output. Three limitations belong in every honest version of this memo: the 300 customers with no country, the 12 pending orders, and the 999 orders whose line items do not reconcile to their order total.