Unit 13.01: Building the analysis-ready dataset

Unit ID: SQL-M13-U02 - Estimated active time: 35-45 minutes Objective: assemble one clean, documented dataset that answers all eight questions.

Build it in stages, as CTEs

Module 6's discipline, applied to the whole deliverable:

WITH clean_customers AS (
  -- Normalisation rule, applied once (Module 9)
  SELECT customer_id,
         COALESCE(country, 'UNKNOWN')  AS country,
         LOWER(TRIM(city))             AS city
  FROM customers
),
order_base AS (
  -- One row per order, with customer attributes attached.
  -- Grain check: 1000 in, 1000 out - no fan-out (Module 5)
  SELECT o.order_id, o.status, o.order_total,
         CAST(o.placed_at AS DATE) AS placed_on,
         c.country, c.city
  FROM orders o
  JOIN clean_customers c ON c.customer_id = o.customer_id
),
item_rollup AS (
  -- Collapse the many-side BEFORE joining (Module 5)
  SELECT order_id, COUNT(*) AS item_count, SUM(price * quantity) AS item_value
  FROM order_items
  GROUP BY order_id
)
SELECT b.*,
       COALESCE(r.item_count, 0) AS item_count,
       COALESCE(r.item_value, 0) AS item_value
FROM order_base b
LEFT JOIN item_rollup r ON r.order_id = b.order_id;

Three named stages, each independently runnable, with the two riskiest decisions - normalisation and pre-aggregation - visible in the query rather than assumed.

Verify the grain immediately

-- fragment: runs inside the WITH block above
-- Must be 1000. If it is 3400, the item join was not pre-aggregated.
SELECT COUNT(*) FROM order_base;   -- 1000

This single check is the difference between the correct ₹27,01,463 and the inflated ₹91,47,789.

Answer the questions from the one dataset

-- fragment: runs inside the WITH block above
-- Q1 and Q2
SELECT COUNT(*)                                                        AS all_orders,      -- 1000
       COUNT(*) FILTER (WHERE status = 'completed')                    AS completed,       -- 988
       SUM(order_total)                                                AS booked_revenue,  -- 2701463.00
       SUM(order_total) FILTER (WHERE status = 'completed')            AS completed_revenue -- 2685905.00
FROM order_base;

Answering everything from one prepared dataset is what keeps the figures mutually consistent. Eight separately written queries will eventually disagree with each other.

Practice

Add a stage that flags orders paid in instalments, and confirm it does not change the row count.

-- fragment: one CTE, to be added to the WITH block above
payment_rollup AS (
  SELECT order_id, COUNT(*) AS payment_count, SUM(amount) AS paid
  FROM payments
  GROUP BY order_id
)

Takeaway

One documented dataset, built in named stages, with a grain check after every join. Every question is then answered from the same foundation.

---

Mark complete