DB Olympics

Queries · q138 · Head of Merchandising · week 1, night 1

Monthly Review Volume and Rating Trend

“How is customer review volume and average rating trending over time across the platform?”

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 1, request 3 of 3

    Freya has 5 broad asks; I am splitting them into clean one-grain reports rather than covering everything at once. Tonight I want foundational views that tell me where to dig deeper on nights 2 to 4: (1) a per-merchant catalog profile that speaks to Q1 catalog health and size, and seeds Q5 top vs long tail since it is ordered by revenue; (2) a richness vs sales bucket report that gives a first, non survivorship biased read on Q2 using image count as the richness proxy; (3) a monthly review and rating trend to start on Q3. I am deliberately leaving collections and bundles (Q4) and the detailed top vs tail structural comparison (Q5) for later nights once I see what these three reports reveal. Each report is a single grain and stays under 12 columns.

    Monthly Review Volume and Rating Trend
    Question
    How is customer review volume and average rating trending over time across the platform?
    One row per
    calendar month from 2023-01 through 2025-12
    Columns
    • month
    • review_count
    • avg_rating
    • pct_1_2_star
    • pct_5_star
    • pct_verified_purchase

  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)
    WITH months AS (
  SELECT DISTINCT t0.month_label AS month, t0.month_start AS month_start
  FROM calendar_days t0
  WHERE t0.month_start >= DATE '2023-01-01' AND t0.month_start < DATE '2026-01-01'
),
reviews AS (
  SELECT t1.month_label AS month, t0.rating AS rating, t0.is_verified_purchase AS is_verified_purchase
  FROM product_reviews t0
  JOIN calendar_days t1 ON t1.calendar_date = CAST(t0.created_at AS date)
  WHERE t0.is_approved = true
),
m_count AS (
  SELECT t2.month AS month, COUNT(*) AS review_count
  FROM reviews t2
  GROUP BY t2.month
),
m_avg AS (
  SELECT t3.month AS month, AVG(t3.rating) AS avg_rating
  FROM reviews t3
  GROUP BY t3.month
),
m_low AS (
  SELECT t4.month AS month, SUM(CASE WHEN t4.rating IN (1,2) THEN 1 ELSE 0 END) AS low_count
  FROM reviews t4
  GROUP BY t4.month
),
m_five AS (
  SELECT t5.month AS month, SUM(CASE WHEN t5.rating = 5 THEN 1 ELSE 0 END) AS five_count
  FROM reviews t5
  GROUP BY t5.month
),
m_verified AS (
  SELECT t6.month AS month, SUM(CASE WHEN t6.is_verified_purchase THEN 1 ELSE 0 END) AS verified_count
  FROM reviews t6
  GROUP BY t6.month
)
SELECT
  t0.month AS month,
  COALESCE(t1.review_count, 0) AS review_count,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating,
  ROUND(100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_1_2_star,
  ROUND(100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_5_star,
  ROUND(100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_verified_purchase
FROM months t0
LEFT JOIN m_count t1 ON t1.month = t0.month
LEFT JOIN m_avg t2 ON t2.month = t0.month
LEFT JOIN m_low t3 ON t3.month = t0.month
LEFT JOIN m_five t4 ON t4.month = t0.month
LEFT JOIN m_verified t5 ON t5.month = t0.month
ORDER BY t0.month_start

    “Query compiles with 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 covers all 36 months from 2023-01 through 2025-12, pulling approved product reviews across all tenants, with review volume, average rating, and the three requested share metrics, each rounded as specified.”

  4. The report comes back overnight

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

    month   | review_count | avg_rating | pct_1_2_star | pct_5_star | pct_verified_purchase
--------+--------------+------------+--------------+------------+----------------------
2023-01 |           31 |       4.03 |         16.1 |       58.1 |                 100.0
2023-02 |           90 |       3.82 |         21.1 |       47.8 |                 100.0
2023-03 |           73 |       3.85 |         23.3 |       50.7 |                 100.0
2023-04 |          107 |       3.97 |         18.7 |       55.1 |                 100.0
2023-05 |          117 |       4.05 |         17.9 |       60.7 |                 100.0
2023-06 |          113 |       3.92 |         21.2 |       54.0 |                 100.0
2023-07 |           92 |       4.08 |         15.2 |       60.9 |                 100.0
2023-08 |          130 |       4.07 |         14.6 |       53.8 |                 100.0
2023-09 |          132 |        4.1 |         14.4 |       58.3 |                 100.0
2023-10 |          158 |       3.99 |         19.0 |       55.1 |                 100.0
2023-11 |          146 |       4.11 |         13.0 |       59.6 |                 100.0
2023-12 |          308 |       3.81 |         22.7 |       50.6 |                 100.0
2024-01 |          219 |       4.13 |         14.2 |       60.3 |                 100.0
2024-02 |          192 |       4.12 |         12.0 |       56.8 |                 100.0
2024-03 |          189 |       4.18 |         12.7 |       62.4 |                 100.0
2024-04 |          164 |       4.11 |         15.2 |       57.9 |                 100.0
2024-05 |          142 |       4.15 |         12.0 |       56.3 |                 100.0
2024-06 |          197 |       4.15 |         12.2 |       59.9 |                 100.0
2024-07 |          188 |       4.06 |         15.4 |       56.9 |                 100.0
2024-08 |          169 |       4.06 |         16.0 |       58.0 |                 100.0
2024-09 |          173 |       4.19 |         11.0 |       60.7 |                 100.0
2024-10 |          187 |        4.1 |         14.4 |       58.8 |                 100.0
2024-11 |          230 |       4.02 |         18.3 |       57.0 |                 100.0
2024-12 |          392 |       3.94 |         16.1 |       50.8 |                 100.0
2025-01 |          310 |       3.93 |         19.7 |       55.2 |                 100.0
2025-02 |          197 |       4.09 |         14.2 |       57.4 |                 100.0
2025-03 |          204 |       4.11 |         12.7 |       55.9 |                 100.0
2025-04 |          205 |       4.07 |         15.1 |       56.6 |                 100.0
2025-05 |          205 |       4.07 |         15.6 |       56.1 |                 100.0
2025-06 |          217 |       3.89 |         20.3 |       53.0 |                 100.0
2025-07 |          161 |       4.07 |         15.5 |       58.4 |                 100.0
2025-08 |          224 |       4.21 |         13.4 |       62.5 |                 100.0
2025-09 |          188 |       3.96 |         14.9 |       46.8 |                 100.0
2025-10 |          245 |       4.16 |         12.2 |       60.4 |                 100.0
2025-11 |          258 |       4.22 |         12.8 |       64.3 |                 100.0
2025-12 |          454 |       3.71 |         25.8 |       46.7 |                 100.0
(36 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q138

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

    • determinismtie-breaker: month_start does not determine month_label in the schema
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • 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
    WITH months AS (  SELECT DISTINCT t0.month_label AS month, t0.month_start AS month_start  FROM calendar_days t0  WHERE t0.month_start >= DATE '2023-01-01' AND t0.month_start < DATE '2026-01-01'),reviews AS (  SELECT t1.month_label AS month, t0.rating AS rating, t0.is_verified_purchase AS is_verified_purchase  FROM product_reviews t0  JOIN calendar_days t1 ON t1.calendar_date = CAST(t0.created_at AS date)  WHERE t0.is_approved = true),m_count AS (  SELECT t2.month AS month, COUNT(*) AS review_count  FROM reviews t2  GROUP BY t2.month),m_avg AS (  SELECT t3.month AS month, AVG(t3.rating) AS avg_rating  SELECT t3.month AS month, AVG(CAST(t3.rating AS NUMERIC(18,6))) AS avg_rating  FROM reviews t3  GROUP BY t3.month),m_low AS (  SELECT t4.month AS month, SUM(CASE WHEN t4.rating IN (1,2) THEN 1 ELSE 0 END) AS low_count  FROM reviews t4  GROUP BY t4.month),m_five AS (  SELECT t5.month AS month, SUM(CASE WHEN t5.rating = 5 THEN 1 ELSE 0 END) AS five_count  FROM reviews t5  GROUP BY t5.month),m_verified AS (  SELECT t6.month AS month, SUM(CASE WHEN t6.is_verified_purchase THEN 1 ELSE 0 END) AS verified_count  FROM reviews t6  GROUP BY t6.month)SELECT  t0.month AS month,  COALESCE(t1.review_count, 0) AS review_count,  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating,  ROUND(100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_1_2_star,  ROUND(100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_5_star,  ROUND(100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_verified_purchase  COALESCE(t2.avg_rating, 0) AS avg_rating,  100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0) AS pct_1_2_star,  100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0) AS pct_5_star,  100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0) AS pct_verified_purchaseFROM months t0LEFT JOIN m_count t1 ON t1.month = t0.monthLEFT JOIN m_avg t2 ON t2.month = t0.monthLEFT JOIN m_low t3 ON t3.month = t0.monthLEFT JOIN m_five t4 ON t4.month = t0.monthLEFT JOIN m_verified t5 ON t5.month = t0.monthORDER BY t0.month_startORDER BY t0.month_start NULLS LAST, t0.month NULLS LAST

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

    WITH months AS (
  SELECT DISTINCT t0.month_label AS month, t0.month_start AS month_start
  FROM calendar_days t0
  WHERE t0.month_start >= DATE '2023-01-01' AND t0.month_start < DATE '2026-01-01'
),
reviews AS (
  SELECT t1.month_label AS month, t0.rating AS rating, t0.is_verified_purchase AS is_verified_purchase
  FROM product_reviews t0
  JOIN calendar_days t1 ON t1.calendar_date = CAST(t0.created_at AS date)
  WHERE t0.is_approved = true
),
m_count AS (
  SELECT t2.month AS month, COUNT(*) AS review_count
  FROM reviews t2
  GROUP BY t2.month
),
m_avg AS (
  SELECT t3.month AS month, AVG(CAST(t3.rating AS NUMERIC(18,6))) AS avg_rating
  FROM reviews t3
  GROUP BY t3.month
),
m_low AS (
  SELECT t4.month AS month, SUM(CASE WHEN t4.rating IN (1,2) THEN 1 ELSE 0 END) AS low_count
  FROM reviews t4
  GROUP BY t4.month
),
m_five AS (
  SELECT t5.month AS month, SUM(CASE WHEN t5.rating = 5 THEN 1 ELSE 0 END) AS five_count
  FROM reviews t5
  GROUP BY t5.month
),
m_verified AS (
  SELECT t6.month AS month, SUM(CASE WHEN t6.is_verified_purchase THEN 1 ELSE 0 END) AS verified_count
  FROM reviews t6
  GROUP BY t6.month
)
SELECT
  t0.month AS month,
  COALESCE(t1.review_count, 0) AS review_count,
  COALESCE(t2.avg_rating, 0) AS avg_rating,
  100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0) AS pct_1_2_star,
  100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0) AS pct_5_star,
  100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0) AS pct_verified_purchase
FROM months t0
LEFT JOIN m_count t1 ON t1.month = t0.month
LEFT JOIN m_avg t2 ON t2.month = t0.month
LEFT JOIN m_low t3 ON t3.month = t0.month
LEFT JOIN m_five t4 ON t4.month = t0.month
LEFT JOIN m_verified t5 ON t5.month = t0.month
ORDER BY t0.month_start NULLS LAST, t0.month NULLS LAST
    10 ms100 ms1 s10 sHyper14.5 msDuckDB52.1 msGizmoSQL127 msclickhouse_brew1.22 sFirebolt45.2 msDataFusion174 msClickHouse77.5 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q138 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: Monthly Review Volume and Rating Trend
Question: How is customer review volume and average rating trending over time across the platform?
Grain: one row per calendar month from 2023-01 through 2025-12
Columns:
- month
- review_count
- avg_rating
- pct_1_2_star
- pct_5_star
- pct_verified_purchase
Details: Table: product_reviews filtered to is_approved = true, grouped by calendar month of created_at (use calendar_days.month_label or an equivalent month truncation, applied consistently). Include all tenants combined. review_count = count of approved reviews that month. pct_1_2_star = share of that month's reviews with rating in (1,2). pct_5_star = share with rating = 5. pct_verified_purchase = share with is_verified_purchase = true. Round avg_rating to 2 decimals, percentages to 1 decimal. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH months AS (
  SELECT DISTINCT t0.month_label AS month, t0.month_start AS month_start
  FROM calendar_days t0
  WHERE t0.month_start >= DATE '2023-01-01' AND t0.month_start < DATE '2026-01-01'
),
reviews AS (
  SELECT t1.month_label AS month, t0.rating AS rating, t0.is_verified_purchase AS is_verified_purchase
  FROM product_reviews t0
  JOIN calendar_days t1 ON t1.calendar_date = CAST(t0.created_at AS date)
  WHERE t0.is_approved = true
),
m_count AS (
  SELECT t2.month AS month, COUNT(*) AS review_count
  FROM reviews t2
  GROUP BY t2.month
),
m_avg AS (
  SELECT t3.month AS month, AVG(t3.rating) AS avg_rating
  FROM reviews t3
  GROUP BY t3.month
),
m_low AS (
  SELECT t4.month AS month, SUM(CASE WHEN t4.rating IN (1,2) THEN 1 ELSE 0 END) AS low_count
  FROM reviews t4
  GROUP BY t4.month
),
m_five AS (
  SELECT t5.month AS month, SUM(CASE WHEN t5.rating = 5 THEN 1 ELSE 0 END) AS five_count
  FROM reviews t5
  GROUP BY t5.month
),
m_verified AS (
  SELECT t6.month AS month, SUM(CASE WHEN t6.is_verified_purchase THEN 1 ELSE 0 END) AS verified_count
  FROM reviews t6
  GROUP BY t6.month
)
SELECT
  t0.month AS month,
  COALESCE(t1.review_count, 0) AS review_count,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating,
  ROUND(100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_1_2_star,
  ROUND(100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_5_star,
  ROUND(100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_verified_purchase
FROM months t0
LEFT JOIN m_count t1 ON t1.month = t0.month
LEFT JOIN m_avg t2 ON t2.month = t0.month
LEFT JOIN m_low t3 ON t3.month = t0.month
LEFT JOIN m_five t4 ON t4.month = t0.month
LEFT JOIN m_verified t5 ON t5.month = t0.month
ORDER BY t0.month_start
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, review_count, avg_rating, pct_1_2_star, pct_5_star, pct_verified_purchase
    • BeaQuery compiles with the expected columns. Submitting.
    • Bea calls submit
      WITH months AS (
  SELECT DISTINCT t0.month_label AS month, t0.month_start AS month_start
  FROM calendar_days t0
  WHERE t0.month_start >= DATE '2023-01-01' AND t0.month_start < DATE '2026-01-01'
),
reviews AS (
  SELECT t1.month_label AS month, t0.rating AS rating, t0.is_verified_purchase AS is_verified_purchase
  FROM product_reviews t0
  JOIN calendar_days t1 ON t1.calendar_date = CAST(t0.created_at AS date)
  WHERE t0.is_approved = true
),
m_count AS (
  SELECT t2.month AS month, COUNT(*) AS review_count
  FROM reviews t2
  GROUP BY t2.month
),
m_avg AS (
  SELECT t3.month AS month, AVG(t3.rating) AS avg_rating
  FROM reviews t3
  GROUP BY t3.month
),
m_low AS (
  SELECT t4.month AS month, SUM(CASE WHEN t4.rating IN (1,2) THEN 1 ELSE 0 END) AS low_count
  FROM reviews t4
  GROUP BY t4.month
),
m_five AS (
  SELECT t5.month AS month, SUM(CASE WHEN t5.rating = 5 THEN 1 ELSE 0 END) AS five_count
  FROM reviews t5
  GROUP BY t5.month
),
m_verified AS (
  SELECT t6.month AS month, SUM(CASE WHEN t6.is_verified_purchase THEN 1 ELSE 0 END) AS verified_count
  FROM reviews t6
  GROUP BY t6.month
)
SELECT
  t0.month AS month,
  COALESCE(t1.review_count, 0) AS review_count,
  ROUND(COALESCE(t2.avg_rating, 0), 2) AS avg_rating,
  ROUND(100.0 * COALESCE(t3.low_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_1_2_star,
  ROUND(100.0 * COALESCE(t4.five_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_5_star,
  ROUND(100.0 * COALESCE(t5.verified_count, 0) / NULLIF(t1.review_count, 0), 1) AS pct_verified_purchase
FROM months t0
LEFT JOIN m_count t1 ON t1.month = t0.month
LEFT JOIN m_avg t2 ON t2.month = t0.month
LEFT JOIN m_low t3 ON t3.month = t0.month
LEFT JOIN m_five t4 ON t4.month = t0.month
LEFT JOIN m_verified t5 ON t5.month = t0.month
ORDER BY t0.month_start
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report covers all 36 months from 2023-01 through 2025-12, pulling approved product reviews across all tenants, with review volume, average rating, and the three requested share metrics, each rounded as specified.

    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 (266 KB) · Zara Ahmed's transcript (894 KB) · Freya Lindgren's transcript (260 KB) · the night's plan (4 KB) · the night's reports (20 KB) · the week's memo (3 KB)