Answering the four questions

A model is not good because it is normalized, or star-shaped, or documented. It is good because the questions the business actually asks are cheap and unambiguous to answer with it. So this session is the exam, and these are the four questions Bazaar's leadership team asks: which merchant's performance dropped, why sales dropped, why one specific day collapsed, and when the peak transaction hours are.

Three files ship today: sql/20_marts.sql builds the four marts, sql/30_business_questions.sql answers the four questions with the verified numbers written in beside them, and python/answer_questions.py runs the same analyses against bazaar.db, prints readable answers and saves every chart as a 300 DPI PNG plus an SVG for slides.

The most common thing built and called a mart is a subset: fewer columns, fewer rows, same shape. That is a copy, and copies rot. A mart is the star pre-aggregated to the grain a specific set of questions is asked at, with the metric definitions baked in so that two people asking the same question get the same number. The grain is the whole point, which is why Bazaar has four of them and not one.

"Sales dropped 30%" is not an answer, it is the question restated with more precision. Revenue is an identity: revenue = carts x conversion x AOV. So a drop is always one of those three moving, usually one much more than the others, and each of the three sends you to a different team. Compute all three side by side and the cause stops being something people argue about in a meeting.

The marts are already built in every editor on this page, from the same sql/20_marts.sql you can run on your laptop. Before trusting a single answer, read their grains and reconcile them to the star. A mart that does not tie back is not a mart, it is a second opinion.

SELECT 'mart_merchant_daily' AS mart, 'merchant x day' AS grain,
       COUNT(*) AS rows FROM mart_merchant_daily
UNION ALL SELECT 'mart_funnel_daily',         'one day',
       COUNT(*) FROM mart_funnel_daily
UNION ALL SELECT 'mart_payment_health_daily', 'day x payment method',
       COUNT(*) FROM mart_payment_health_daily
UNION ALL SELECT 'mart_hourly_transactions',  'day x hour',
       COUNT(*) FROM mart_hourly_transactions
UNION ALL SELECT 'v_metric_definitions',      'one metric',
       COUNT(*) FROM v_metric_definitions;

SELECT 'star: v_sales_line (is_paid = 1)' AS source,
       ROUND(SUM(net_revenue), 2)         AS net_revenue,
       COUNT(DISTINCT order_id)           AS orders,
       ROUND(SUM(net_revenue) / COUNT(DISTINCT order_id), 2) AS aov
FROM v_sales_line
WHERE is_paid = 1
UNION ALL
SELECT 'mart_merchant_daily',
       ROUND(SUM(net_revenue), 2),
       SUM(orders),
       ROUND(SUM(aov_numerator) / SUM(aov_denominator), 2)
FROM mart_merchant_daily;

Rank the merchants two ways at once, because neither ranking is sufficient alone: absolute dollars is what the business feels, and percent is how bad it is for that merchant. Rank by percent only and you promote tiny merchants; rank by dollars only and you hide a small merchant in freefall. Then split the drop into volume versus order value, because those have different fixes.

WITH windows AS (
  SELECT merchant_id, merchant_name,
    SUM(CASE WHEN activity_date >= '2026-06-16' THEN net_revenue ELSE 0 END) AS rev_recent,
    SUM(CASE WHEN activity_date >= '2026-06-02'
              AND activity_date <  '2026-06-16' THEN net_revenue ELSE 0 END) AS rev_prior,
    SUM(CASE WHEN activity_date >= '2026-06-16' THEN orders ELSE 0 END)      AS ord_recent,
    SUM(CASE WHEN activity_date >= '2026-06-02'
              AND activity_date <  '2026-06-16' THEN orders ELSE 0 END)      AS ord_prior
  FROM mart_merchant_daily
  GROUP BY merchant_id, merchant_name
)
SELECT merchant_id, merchant_name,
       ROUND(rev_prior,  0)             AS revenue_prior_14d,
       ROUND(rev_recent, 0)             AS revenue_recent_14d,
       ROUND(rev_recent - rev_prior, 0) AS dollars_change,
       ROUND(100.0 * (rev_recent - rev_prior) / rev_prior, 1) AS pct_change,
       ROUND(100.0 * (ord_recent - ord_prior) / ord_prior, 1) AS pct_change_orders,
       CASE WHEN rev_recent >= rev_prior THEN 'no drop'
            WHEN ord_recent * 1.0 / ord_prior < 0.85 THEN 'volume driven'
            ELSE 'order-value driven' END AS drop_shape
FROM windows
ORDER BY dollars_change ASC
LIMIT 5;

-- the number the ranking above does not show you
SELECT COUNT(*) AS merchants_down FROM (
  SELECT merchant_id,
         SUM(CASE WHEN activity_date >= '2026-06-16' THEN net_revenue ELSE 0 END)
       - SUM(CASE WHEN activity_date >= '2026-06-02'
                   AND activity_date < '2026-06-16' THEN net_revenue ELSE 0 END) AS delta
  FROM mart_merchant_daily GROUP BY merchant_id) WHERE delta < 0;

The answer, and the trap. M07 Nimbus Audio lost the most in both rankings: 2,609 in dollars and 85.2% in percent, and the split says volume driven, so it sold far fewer orders rather than cheaper ones. But read the second result set: 8 of 12 merchants are down. Stop at "M07 dropped 85%" and you will go and manage M07 while the whole marketplace falls around you. A merchant ranking has no denominator - it cannot tell you whether the cause is one merchant or the platform, because every merchant is measured against itself. M07 is still special, since it falls almost twice as hard as the next one, but it is one of two causes rather than the cause. Finding out which is Q2's job.

The decomposition from Part 2, run for real. One query, one grain, all three factors, both windows. The ratios are computed here at read time from the numerator and denominator columns the mart stores, which is the only reason the two windows are comparable at all.

WITH w AS (
  SELECT CASE WHEN activity_date >= '2026-06-16' THEN 'recent_14d'
              ELSE 'prior_14d' END      AS window_label,
         SUM(carts_created)              AS carts,
         SUM(carts_converted)            AS conversions,
         SUM(paid_orders)                AS paid_orders,
         SUM(net_revenue)                AS net_revenue,
         SUM(payment_attempts)           AS attempts,
         SUM(declines)                   AS declines,
         SUM(abandoned_value)            AS abandoned_value
  FROM mart_funnel_daily
  WHERE activity_date >= '2026-06-02'
  GROUP BY window_label
)
SELECT window_label,
       carts,
       ROUND(100.0 * conversions / carts, 1)            AS conversion_rate_pct,
       paid_orders,
       ROUND(net_revenue, 0)                            AS net_revenue,
       ROUND(net_revenue / NULLIF(paid_orders, 0), 2)   AS aov,
       ROUND(100.0 * declines / NULLIF(attempts, 0), 1) AS decline_rate_pct,
       ROUND(abandoned_value, 0)                        AS abandoned_value
FROM w
ORDER BY window_label DESC;

The decomposition. Revenue is down 30.3%. Carts are down 21.6%, conversion moved 2.3 percentage points, AOV is down 8.1%. So most of the loss is fewer carts - demand that never arrived - and the remainder is smaller orders. Conversion did not break, and the decline rate actually improved from 10.3% to 4.1%, because the earlier window contains the incident Q3 investigates. That is the whole reason to decompose: the raw headline sends everyone to checkout, and the model says go to marketing and to one merchant.

Two hypotheses survive that far, so test both. One query each, and each one either lives or dies.

-- H1 · declines by merchant, recent 14 days
SELECT m.merchant_id, m.merchant_name,
       COUNT(*)                                        AS attempts,
       SUM(f.is_declined)                              AS declines,
       ROUND(100.0 * SUM(f.is_declined) / COUNT(*), 1) AS decline_rate_pct
FROM fact_transaction f
JOIN dim_date d          ON d.date_key = f.date_key
JOIN fact_order_item foi ON foi.order_id = f.order_id AND foi.line_no = 1
JOIN dim_merchant m      ON m.merchant_sk = foi.merchant_sk
WHERE d.full_date >= '2026-06-16'
GROUP BY m.merchant_id, m.merchant_name
ORDER BY decline_rate_pct DESC
LIMIT 5;

-- H2 · did a product stop selling entirely?
SELECT p.merchant_id, p.product_name, p.status,
       ROUND(SUM(CASE WHEN d.full_date <  '2026-06-15'
                      THEN f.net_amount ELSE 0 END), 0) AS revenue_before,
       ROUND(SUM(CASE WHEN d.full_date >= '2026-06-15'
                      THEN f.net_amount ELSE 0 END), 0) AS revenue_after
FROM fact_order_item f
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_date d    ON d.date_key = f.date_key
WHERE p.status = 'out_of_stock'
GROUP BY p.merchant_id, p.product_name, p.status
ORDER BY revenue_before DESC;

H1 dies. The decline ranking puts M10 first at 18.2% and M07 second at 10.0%, with M03 and M08 next at 8.3%. Now read the denominators: those rates sit on 11, 10, 12 and 12 attempts. At eleven attempts a single decline moves the rate by about nine points, so the ranking is noise wearing a percentage sign, and nothing in it can distinguish one merchant from another. The payments hypothesis is not disproved so much as shown to be untestable at this volume - which is the correct verdict, and one you can only reach because the query printed its denominator next to its rate.

H2 survives. M07's hero product, Studio Headphones, earned 3,646 before 2026-06-15 and exactly 0 after it. Not less. Zero. A revenue series that goes to exactly zero on a date is almost never demand and almost always supply or a switch somebody flipped, and here the product status says out_of_stock. That is a supply failure, and on its own it explains the bulk of one merchant's collapse.

Final diagnosis: two independent causes. A platform-wide demand drop - carts down 21.6%, traced to a paid-search pullback from 2026-06-18 - plus M07 losing its hero SKU to a stockout. Neither is a checkout problem, which is exactly where a room without a model would have started guessing.

Day-level attribution, and the shape of the answer is the lesson. Look at the day with its neighbours, and watch which parts of the funnel are normal and which are not.

SELECT activity_date,
       carts_created,
       carts_converted,
       paid_orders,
       ROUND(net_revenue, 0)                                    AS net_revenue,
       payment_attempts,
       declines,
       ROUND(100.0 * declines / NULLIF(payment_attempts, 0), 1) AS decline_rate_pct,
       ROUND(declined_amount, 0)                                AS money_that_failed
FROM mart_funnel_daily
WHERE activity_date BETWEEN '2026-06-07' AND '2026-06-13'
ORDER BY activity_date;

-- and the reason code, from the payment-health mart
SELECT top_decline_reason,
       SUM(declines)                      AS declines,
       ROUND(SUM(declined_amount), 0)     AS value
FROM mart_payment_health_daily
WHERE activity_date = '2026-06-10'
  AND declines > 0
GROUP BY top_decline_reason
ORDER BY declines DESC;

The answer. Carts were normal at 24, conversions were normal at 13, and orders were placed. Revenue still collapsed to 484 against neighbours of 1,338 and 1,967, because 9 of 17 payment attempts failed - a 52.9% decline rate against 13.3% the day before and 6.3% the day after - taking 740 SGD of money with them. The reason code is gateway_timeout on all nine. So demand was fine and checkout broke: an infrastructure incident, not a market event, and the fix is an on-call ticket rather than a pricing review.

Why this question is answerable at all. The whole diagnosis rests on one sentence: people wanted to buy, they tried to buy, and the money did not settle. That sentence needs three different grains to be true at once - carts (what was wanted), orders (what was placed) and payment attempts (what was paid) - and Bazaar keeps them as three separate facts. Flatten them into one table, or keep a paid boolean on the order instead of an attempt table, and this sentence has nowhere to live. The day would show up as "revenue down 75%" with no way to tell it apart from a bad market, which is the version of this incident most companies actually get.

The question behind every staffing roster, on-call rotation and deploy-freeze window. Answered across all 90 days at once, which is only cheap because hour is a dimension rather than something parsed out of a timestamp on every read. Then split weekday from weekend, because a single blended peak would staff the wrong hour.

SELECT hour_label,
       daypart,
       SUM(attempts)                                   AS attempts,
       SUM(approvals)                                  AS approvals,
       ROUND(100.0 * SUM(declines) / SUM(attempts), 1) AS decline_rate_pct,
       ROUND(100.0 * SUM(attempts) /
             (SELECT SUM(attempts) FROM mart_hourly_transactions), 1) AS pct_of_all_attempts
FROM mart_hourly_transactions
GROUP BY hour_label, daypart
ORDER BY attempts DESC
LIMIT 6;

-- weekday vs weekend, because one blended peak would mislead a roster
SELECT hour_label,
       SUM(CASE WHEN is_weekend = 0 THEN attempts ELSE 0 END) AS weekday_attempts,
       SUM(CASE WHEN is_weekend = 1 THEN attempts ELSE 0 END) AS weekend_attempts,
       SUM(attempts)                                         AS all_attempts
FROM mart_hourly_transactions
GROUP BY hour_label
ORDER BY all_attempts DESC
LIMIT 8;

The answer. A broad evening peak from 19:00 to 22:59 carrying about a third of all payment attempts, with a secondary lunch shoulder at 12:00 to 13:59 and a nearly dead overnight. So on-call cover belongs in the evening and deploy windows belong before dawn.

And the split matters. Weekday attempts peak at 20:00 with 64, weekend attempts peak at 21:00 with 41. A single blended answer would have named one hour and staffed the wrong one on two days out of every seven. This is the small, unglamorous version of the same lesson Q1 taught: an aggregate hides the thing you were about to act on, and the fix is always to keep the dimension that separates the cases - here is_weekend, sitting in dim_date since session b6 precisely so that nobody has to compute it.

• Write down the four questions your own leadership actually asks, in their words. Then try to answer each one against your model and time yourself. Anything that takes more than ten minutes is a modeling gap, not a SQL gap.
• Decompose one metric you own into an identity the way revenue decomposes into carts x conversion x AOV. Then check whether all the factors exist at one grain anywhere in your warehouse. Usually one is missing, and that absence is your next mart.
• Find a rate in a dashboard you use and hunt for its denominator. If the denominator is not on the same screen, add it, and see whether anybody's mind changes.
• Take a drop somebody is already investigating and write two hypotheses that could explain it. Write the query that would kill each one before you run either. If a hypothesis has no killing query, it is an opinion.
• Run python python/answer_questions.py locally and keep the four 300 DPI charts. Bring the Q2 decomposition chart to b9, where the same model gets handed to an AI analyst.

Full source map in materials/official-course-map.md. This page covers: