DB Olympics

Queries · q140 · Head of Merchandising · week 1, night 2

Review Engagement Relative to Order Volume

“Is the month-over-month growth in review counts just tracking order volume growth, or is review-per-order engagement actually increasing?”

Manager · asked the questionFreya LindgrenHead of Merchandising. Came from a fashion marketplace's catalog team. Responsible for helping merchants present their products well.
Analyst · planned the reportZara AhmedExploratory: looks at the raw detail of an unfamiliar area before aggregating it.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. Freya Lindgren writes to Zara Ahmed

    The manager, at the start of week 1

    Subject: My first-week priorities — catalog questions

    Hi Zara,

    Great to have you helping me get oriented this week. I know slots are tight, so here's what I most want to understand first. Pick the ones you think will give us the clearest picture given the report budget — happy to defer the rest to next week.

    1. How healthy is our catalog overall, and where is it thin? I'd like a sense of how many products and variants each merchant is actually running, which ones feel well-built out (good imagery, descriptions, variants) versus sparse, and whether that varies a lot across merchants or verticals. I need this to know where to focus my attention first.
    2. Does catalog quality actually move sales? I'm curious whether products with richer content — more images, more reviews, better ratings — sell noticeably better than ones without. I want to build the case (to myself and to merchants) for why investing in catalog presentation is worth it.
    3. What do customers think of the products, and where are the trouble spots? I'd like to see how reviews and ratings are trending, which products or categories are getting poor feedback, and whether there are products with surprisingly few or no reviews despite decent sales. This tells me where to nudge merchants on quality or listing improvements.
    4. Are we using collections and bundles well? I want to know how much merchants are leaning on curated collections and bundles versus just flat product listings, and whether those grouped presentations correlate with better performance. This will shape whether I push merchandising tools harder in my conversations with merchants.
    5. How are our top merchants' catalogs structured compared to the long tail? Since a handful of merchants drive most of the volume, I'd like to understand if their catalogs look meaningfully different (size, categorization, media richness) from smaller merchants — it'll help me figure out what "good" looks like and what advice to bring to the smaller ones.

    Let me know what's realistic to cover by Friday, and flag if any of these need to be split into smaller questions on your end.

    Thanks, Freya

  2. Zara Ahmed plans the night

    The analyst · week 1, night 2, request 2 of 3

    Night 1 findings to react to: (1) In the merchant profile, image-presence (97-100%) and variant richness are almost entirely explained by vertical (apparel ~12-14 variants/product vs B2B ~1-2), not merchant skill — so 'catalog health' comparisons need to control for vertical. A couple of outliers stood out for later: Bytefield's avg_rating of 1.50 and Circuit Harbor's thin 18 orders against 93 products, both worth a Q3 drill-down later. (2) The image-count-bucket report is NOT monotonic — bucket '1 image' (265 EUR/product) beats '2-3' and '4-6' (183, 192), only catching back up at '7+' (521). That's suspicious and likely confounded by vertical mix (B2B products tend to have 1 plain image but high per-order value; apparel has many images but lower per-unit revenue). I want to re-run the richness-vs-sales comparison inside a single vertical to remove that confound before telling Freya 'more images = more sales'. (3) The monthly review trend shows review counts climbing through the series with sharp December spikes each year, but December 2025 stands out with the lowest avg_rating (3.71) and highest pct_1_2_star (25.8%) in the whole 36-month series — a real anomaly worth root-causing. Also, since order volume is growing over the same period, I can't yet tell if rising review counts reflect genuine engagement growth or just mirror order growth — need review-per-order to normalize that. Tonight's three requests: control for vertical on richness-vs-sales, normalize review volume by order volume, and localize the December 2025 rating dip by vertical.

    Review Engagement Relative to Order Volume
    Question
    Is the month-over-month growth in review counts just tracking order volume growth, or is review-per-order engagement actually increasing?
    One row per
    calendar month, 2023-01 through 2025-12
    Columns
    • month
    • orders_count
    • review_count
    • reviews_per_100_orders
    • avg_rating

  3. Bea writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT
  t0.month_label AS month,
  COALESCE(t1.orders_count, 0) AS orders_count,
  COALESCE(t2.review_count, 0) AS review_count,
  ROUND(
    CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0
    ELSE COALESCE(t2.review_count, 0)::decimal / t1.orders_count * 100
    END, 2
  ) AS reviews_per_100_orders,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating
FROM (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
) t0
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', o.placed_at)::date AS month_start,
    COUNT(*) AS orders_count
  FROM orders o
  WHERE o.placed_at IS NOT NULL
    AND o.status NOT IN ('cancelled')
  GROUP BY DATE_TRUNC('month', o.placed_at)::date
) t1 ON t1.month_start = t0.month_start
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', r.created_at)::date AS month_start,
    COUNT(*) AS review_count,
    AVG(r.rating) AS avg_rating
  FROM product_reviews r
  WHERE r.is_approved = true
  GROUP BY DATE_TRUNC('month', r.created_at)::date
) t2 ON t2.month_start = t0.month_start
ORDER BY t0.month_start ASC

    “Query compiles and returns the expected columns. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. The report builds the full 36-month calendar (2023-01 through 2025-12) and left-joins order counts (all tenants, placed_at in month, status ≠ cancelled) and approved review counts/ratings (same definition as the night-1 report), computing reviews-per-100-orders and average rating per month so Merchandising can see whether review engagement is outpacing, tracking, or lagging order growth.”

  4. The report comes back overnight

    As Zara Ahmed saw it, from the small dataset the agents worked on

    month   | orders_count | review_count | reviews_per_100_orders | avg_rating
--------+--------------+--------------+------------------------+-----------
2023-01 |          499 |           31 |                   6.21 |       4.03
2023-02 |          522 |           90 |                  17.24 |       3.82
2023-03 |          611 |           73 |                  11.95 |       3.85
2023-04 |          653 |          107 |                  16.39 |       3.97
2023-05 |          772 |          117 |                  15.16 |       4.05
2023-06 |          713 |          113 |                  15.85 |       3.92
2023-07 |          762 |           92 |                  12.07 |       4.08
2023-08 |          813 |          130 |                  15.99 |       4.07
2023-09 |          877 |          132 |                  15.05 |        4.1
2023-10 |         1049 |          158 |                  15.06 |       3.99
2023-11 |         1787 |          146 |                   8.17 |       4.11
2023-12 |         1540 |          308 |                   20.0 |       3.81
2024-01 |         1275 |          219 |                  17.18 |       4.13
2024-02 |         1077 |          192 |                  17.83 |       4.12
2024-03 |         1166 |          189 |                  16.21 |       4.18
2024-04 |         1194 |          164 |                  13.74 |       4.11
2024-05 |         1309 |          142 |                  10.85 |       4.15
2024-06 |         1265 |          197 |                  15.57 |       4.15
2024-07 |         1331 |          188 |                  14.12 |       4.06
2024-08 |         1271 |          169 |                   13.3 |       4.06
2024-09 |         1315 |          173 |                  13.16 |       4.19
2024-10 |         1457 |          187 |                  12.83 |        4.1
2024-11 |         2056 |          230 |                  11.19 |       4.02
2024-12 |         2546 |          392 |                   15.4 |       3.94
2025-01 |         1768 |          310 |                  17.53 |       3.93
2025-02 |         1370 |          197 |                  14.38 |       4.09
2025-03 |         1586 |          204 |                  12.86 |       4.11
2025-04 |         1520 |          205 |                  13.49 |       4.07
2025-05 |         1633 |          205 |                  12.55 |       4.07
2025-06 |         1461 |          217 |                  14.85 |       3.89
2025-07 |         1596 |          161 |                  10.09 |       4.07
2025-08 |         1502 |          224 |                  14.91 |       4.21
2025-09 |         1659 |          188 |                  11.33 |       3.96
2025-10 |         1828 |          245 |                   13.4 |       4.16
2025-11 |         2550 |          258 |                  10.12 |       4.22
2025-12 |         2994 |          454 |                  15.16 |       3.71
(36 rows)

  5. What came of it

    The memo and the queries that followed; links found by title, so inferred

    It builds on:

  6. It becomes benchmark query q140

    Changed only where the benchmark needs the same result on every system

    • spellingcast (5×)
    • determinismtie-breaker: month_label completes the DISTINCT key of t0
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    SELECT  t0.month_label AS month,  COALESCE(t1.orders_count, 0) AS orders_count,  COALESCE(t2.review_count, 0) AS review_count,  ROUND(    CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0    ELSE COALESCE(t2.review_count, 0)::decimal / t1.orders_count * 100    END, 2  ) AS reviews_per_100_orders,  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating  CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0  ELSE CAST(COALESCE(t2.review_count, 0) AS decimal(18,6)) / t1.orders_count * 100  END AS reviews_per_100_orders,  COALESCE(t2.avg_rating, 0) AS avg_ratingFROM (  SELECT DISTINCT month_label, month_start  FROM calendar_days  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31') t0LEFT JOIN (  SELECT    DATE_TRUNC('month', o.placed_at)::date AS month_start,    CAST(DATE_TRUNC('month', o.placed_at) AS date) AS month_start,    COUNT(*) AS orders_count  FROM orders o  WHERE o.placed_at IS NOT NULL    AND o.status NOT IN ('cancelled')  GROUP BY DATE_TRUNC('month', o.placed_at)::date  GROUP BY CAST(DATE_TRUNC('month', o.placed_at) AS date)) t1 ON t1.month_start = t0.month_startLEFT JOIN (  SELECT    DATE_TRUNC('month', r.created_at)::date AS month_start,    CAST(DATE_TRUNC('month', r.created_at) AS date) AS month_start,    COUNT(*) AS review_count,    AVG(r.rating) AS avg_rating    AVG(CAST(r.rating AS NUMERIC(18,6))) AS avg_rating  FROM product_reviews r  WHERE r.is_approved = true  GROUP BY DATE_TRUNC('month', r.created_at)::date  GROUP BY CAST(DATE_TRUNC('month', r.created_at) AS date)) t2 ON t2.month_start = t0.month_startORDER BY t0.month_start ASCORDER BY t0.month_start ASC NULLS LAST, t0.month_label ASC NULLS LAST

    As each system received it; highlighted, the parts spelled for that system

    SELECT
  t0.month_label AS month,
  COALESCE(t1.orders_count, 0) AS orders_count,
  COALESCE(t2.review_count, 0) AS review_count,
  CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0
  ELSE CAST(COALESCE(t2.review_count, 0) AS decimal(18,6)) / t1.orders_count * 100
  END AS reviews_per_100_orders,
  COALESCE(t2.avg_rating, 0) AS avg_rating
FROM (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
) t0
LEFT JOIN (
  SELECT
    CAST(DATE_TRUNC('month', o.placed_at) AS date) AS month_start,
    COUNT(*) AS orders_count
  FROM orders o
  WHERE o.placed_at IS NOT NULL
    AND o.status NOT IN ('cancelled')
  GROUP BY CAST(DATE_TRUNC('month', o.placed_at) AS date)
) t1 ON t1.month_start = t0.month_start
LEFT JOIN (
  SELECT
    CAST(DATE_TRUNC('month', r.created_at) AS date) AS month_start,
    COUNT(*) AS review_count,
    AVG(CAST(r.rating AS NUMERIC(18,6))) AS avg_rating
  FROM product_reviews r
  WHERE r.is_approved = true
  GROUP BY CAST(DATE_TRUNC('month', r.created_at) AS date)
) t2 ON t2.month_start = t0.month_start
ORDER BY t0.month_start ASC NULLS LAST, t0.month_label ASC NULLS LAST
    10 ms100 ms1 sHyper16.7 msDuckDB35.5 msGizmoSQL49.4 msclickhouse_brew279 msFirebolt70.7 msDataFusion116 msClickHouse62.2 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q140 on the start page, with every configuration.

  7. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Zara Ahmed (analyst for the Head of Merchandising):

Title: Review Engagement Relative to Order Volume
Question: Is the month-over-month growth in review counts just tracking order volume growth, or is review-per-order engagement actually increasing?
Grain: one row per calendar month, 2023-01 through 2025-12
Columns:
- month
- orders_count
- review_count
- reviews_per_100_orders
- avg_rating
Details: orders_count = count of orders across all tenants with placed_at in that calendar month and status not in ('cancelled'). review_count = count of approved product_reviews with created_at in that month (same definition as the night-1 'Monthly Review Volume and Rating Trend' report, so the two reports can be compared side by side). reviews_per_100_orders = review_count / orders_count * 100, rounded to 2 decimals. avg_rating = mean rating of that month's approved reviews, rounded to 2 decimals. Build off the full calendar (calendar_days) so every month appears even if a metric is 0. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.month_label AS month,
  COALESCE(t1.orders_count, 0) AS orders_count,
  COALESCE(t2.review_count, 0) AS review_count,
  ROUND(
    CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0
    ELSE COALESCE(t2.review_count, 0)::decimal / t1.orders_count * 100
    END, 2
  ) AS reviews_per_100_orders,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating
FROM (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
) t0
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', o.placed_at)::date AS month_start,
    COUNT(*) AS orders_count
  FROM orders o
  WHERE o.placed_at IS NOT NULL
    AND o.status NOT IN ('cancelled')
  GROUP BY DATE_TRUNC('month', o.placed_at)::date
) t1 ON t1.month_start = t0.month_start
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', r.created_at)::date AS month_start,
    COUNT(*) AS review_count,
    AVG(r.rating) AS avg_rating
  FROM product_reviews r
  WHERE r.is_approved = true
  GROUP BY DATE_TRUNC('month', r.created_at)::date
) t2 ON t2.month_start = t0.month_start
ORDER BY t0.month_start ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_count, review_count, reviews_per_100_orders, avg_rating
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      SELECT
  t0.month_label AS month,
  COALESCE(t1.orders_count, 0) AS orders_count,
  COALESCE(t2.review_count, 0) AS review_count,
  ROUND(
    CASE WHEN COALESCE(t1.orders_count, 0) = 0 THEN 0
    ELSE COALESCE(t2.review_count, 0)::decimal / t1.orders_count * 100
    END, 2
  ) AS reviews_per_100_orders,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating
FROM (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
) t0
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', o.placed_at)::date AS month_start,
    COUNT(*) AS orders_count
  FROM orders o
  WHERE o.placed_at IS NOT NULL
    AND o.status NOT IN ('cancelled')
  GROUP BY DATE_TRUNC('month', o.placed_at)::date
) t1 ON t1.month_start = t0.month_start
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', r.created_at)::date AS month_start,
    COUNT(*) AS review_count,
    AVG(r.rating) AS avg_rating
  FROM product_reviews r
  WHERE r.is_approved = true
  GROUP BY DATE_TRUNC('month', r.created_at)::date
) t2 ON t2.month_start = t0.month_start
ORDER BY t0.month_start ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report builds the full 36-month calendar (2023-01 through 2025-12) and left-joins order counts (all tenants, `placed_at` in month, status ≠ cancelled) and approved review counts/ratings (same definition as the night-1 report), computing reviews-per-100-orders and average rating per month so Merchandising can see whether review engagement is outpacing, tracking, or lagging order growth.

    Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

    Bea's transcript (260 KB) · Zara Ahmed's transcript (894 KB) · Freya Lindgren's transcript (260 KB) · the night's plan (5 KB) · the night's reports (8 KB) · the week's memo (3 KB)