Module 06 Activity
Scenario
You need one dataset that answers several questions at once: order value, customer country, and basket size. Written as a single nested query it will be correct or incorrect, and you will not be able to tell which. You are going to build it so that each stage proves itself.
Task
- Build the query as CTEs, one stage at a time: normalised customers, then orders joined to those
customers, then line items rolled up per order, then the final assembly.
- After each stage, record the row count before writing the next one.
- Attach the rolled-up line items with a
LEFT JOINand explain in one line why an inner join would be
wrong here.
- Then run a reconciliation: does the summed line value of each order equal its
order_total? - Report what you find. Do not adjust anything to make it agree.
Deliverable
A staged query with a comment above each CTE saying what it produces and at what grain, plus a checkpoint log of the row count after each stage, plus your finding from step 4.
Check your work
| Stage | Rows |
|---|---|
clean_customers | 4,812 |
order_base | 1,000 |
item_rollup | 1,000 |
| final assembly | 1,000 |
If order_base returns anything other than 1,000, the customer join is duplicating orders. If the final assembly returns 3,400, you joined order_items directly instead of joining the rollup.
Step 4 is the real lesson. The reconciliation fails. For 999 of the 1,000 orders, the sum of price * quantity across the line items does not equal order_total. Order 501 is the only one where the two agree, at ₹1,000.00 either way.
This is not something for you to fix. It is a finding: the booked order value and the basket detail are recorded independently in this system, so one cannot be derived from the other. Your report should say so in a sentence - "order value and line-item value do not reconcile for 999 of 1,000 orders; I have used order_total as the revenue measure and treated line items as basket detail only."
Why this matters more than the SQL
An analyst who silently picks whichever number looks better has produced an unreviewable result. An analyst who states the discrepancy has produced a result someone can act on - including the possibility that the discrepancy itself is the most valuable thing they found.