Capstone: live SQL + modeling mock

10-5   min  clarify definitions (GMV, buyer, timezone, refunds)25-20  min  SQL A+B with CTEs; dry-run grain320-30 min  SQL C windows; edge cases430-40 min  schema D refunds + constraints540-50 min  warehouse E facts/dims + SCD note650-60 min  EXPLAIN/index optional; recap assumptions78If 45 min: drop EXPLAIN; keep A-D solid.

1WITH paid_items AS (2  SELECT3    i.order_item_id,4    o.order_id,5    o.buyer_id,6    DATE_TRUNC('month', o.paid_at AT TIME ZONE 'UTC') AS month,7    i.net_cents8  FROM order_items i9  JOIN orders o ON o.order_id = i.order_id10  WHERE o.status IN ('paid', 'fulfilled', 'refunded_partial')11    AND o.paid_at IS NOT NULL12),13monthly AS (14  SELECT15    month,16    SUM(net_cents) AS gmv_cents,17    COUNT(DISTINCT buyer_id) AS buyers18  FROM paid_items19  GROUP BY 120)21SELECT * FROM monthly ORDER BY month;

1WITH first_paid AS (2  SELECT buyer_id, MIN(paid_at) AS first_paid_at3  FROM orders4  WHERE paid_at IS NOT NULL5  GROUP BY 16),7orders_tagged AS (8  SELECT9    o.order_id,10    o.buyer_id,11    DATE_TRUNC('month', o.paid_at) AS month,12    CASE13      WHEN DATE_TRUNC('month', o.paid_at)14         = DATE_TRUNC('month', f.first_paid_at)15      THEN 'first_time'16      ELSE 'repeat'17    END AS buyer_type_for_order18  FROM orders o19  JOIN first_paid f ON f.buyer_id = o.buyer_id20  WHERE o.paid_at IS NOT NULL21)22SELECT23  month,24  buyer_type_for_order,25  COUNT(DISTINCT buyer_id) AS buyers,26  COUNT(*) AS orders27FROM orders_tagged28GROUP BY 1, 229ORDER BY 1, 2;

1WITH spend AS (2  SELECT3    o.buyer_id,4    p.category_id,5    SUM(i.net_cents) AS spend_cents6  FROM orders o7  JOIN order_items i ON i.order_id = o.order_id8  JOIN products p ON p.product_id = i.product_id9  WHERE o.paid_at >= NOW() - INTERVAL '90 days'10  GROUP BY 1, 211),12ranked AS (13  SELECT *,14    ROW_NUMBER() OVER (15      PARTITION BY buyer_id16      ORDER BY spend_cents DESC, category_id  -- stable tie-break17    ) AS rn18  FROM spend19)20SELECT buyer_id, category_id, spend_cents21FROM ranked22WHERE rn = 1;

1CREATE TABLE refunds (2  refund_id      BIGSERIAL PRIMARY KEY,3  order_id       BIGINT NOT NULL REFERENCES orders(order_id),4  order_item_id  BIGINT REFERENCES order_items(order_item_id), -- null = whole-order refund5  amount_cents   BIGINT NOT NULL CHECK (amount_cents > 0),6  reason_code    TEXT,7  status         TEXT NOT NULL CHECK (status IN ('pending','succeeded','failed')),8  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()9);10-- App + periodic job: sum(succeeded refunds) <= order/item net11-- Partial unique not required; allow multiple partial refunds12CREATE INDEX refunds_order_idx ON refunds (order_id, created_at);

1-- Finance mart grain: one row per order_item per day of recognition (simplified)2fact_finance_item_recognized (...)34-- Product analytics grain: one row per paid order_item (event time)5fact_product_order_items (6  order_item_id, paid_date_key, buyer_key, product_key,7  net_cents, qty8)910-- Refunds as separate fact at refund line grain (not negative items silently)11fact_refunds (refund_id, refund_date_key, order_item_key, amount_cents)1213-- GMV net of refunds = sum items - sum refunds with documented timing rules14-- dim_buyer SCD2 for segment; facts store buyer_key version at purchase

1Axis                 4 (strong)                         1 (no hire)2-------------------  ---------------------------------  ------------------------3Definitions          locks GMV/buyer/time before SQL    jumps into joins4Grain control        CTEs named; no fan-out             SUM after item fan-out5SQL correctness      windows/joins null-safe            NOT IN / WHERE on LEFT6Schema judgment      constraints + refund race          floats, no keys7Warehouse sense      fact grain + SCD note              OLTP copy-paste8Communication        narrates checks aloud              silent coding910Hire bar: ≥3 on all axes. One axis at 1 → reject risk.