Queries · q141 · Head of Merchandising · week 1, night 2
“Which merchant vertical drove the platform-wide rating dip and spike in negative reviews seen in December 2025?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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.
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
The analyst · week 1, night 2, request 3 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.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.industry, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')::numeric, 2) AS dec2024_avg_rating, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01')::numeric, 2) AS dec2025_avg_rating, ROUND( (AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') - AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01'))::numeric , 2) AS rating_change_pts, ROUND( 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) , 1) AS dec2025_pct_1_2_star FROM product_reviews pr JOIN products p ON p.id = pr.product_id AND p.tenant_id = pr.tenant_id JOIN tenants t ON t.id = pr.tenant_id WHERE pr.is_approved = true AND ( (pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') OR (pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') ) GROUP BY t.industry ORDER BY rating_change_pts ASC NULLS LAST, t.industry ASC
“Query compiles and produces the expected columns. Submitting.”
“Submitted. The report groups approved product reviews by tenants.industry, comparing full-calendar-month December 2024 vs December 2025 counts and average ratings, with the rating delta and the Dec-2025 1-2 star share, ordered worst-decline first.”
As Zara Ahmed saw it, from the small dataset the agents worked on
industry | dec2024_review_count | dec2024_avg_rating | dec2025_review_count | dec2025_avg_rating | rating_change_pts | dec2025_pct_1_2_star --------------------------+----------------------+--------------------+----------------------+--------------------+-------------------+--------------------- Jewelry & Accessories | 2 | 5.00 | 1 | 3.00 | -2.00 | 0.0 Office Supplies (B2B) | 5 | 5.00 | 9 | 3.67 | -1.33 | 33.3 Pet Supplies | 10 | 4.50 | 18 | 3.61 | -0.89 | 33.3 Books & Stationery | 5 | 4.40 | 4 | 3.75 | -0.65 | 25.0 Consumer Electronics | 13 | 4.15 | 17 | 3.65 | -0.51 | 29.4 Home & Living | 42 | 3.86 | 59 | 3.36 | -0.50 | 35.6 Apparel & Footwear | 207 | 3.78 | 219 | 3.68 | -0.11 | 25.1 Beauty | 24 | 3.83 | 43 | 3.72 | -0.11 | 23.3 Food & Coffee | 65 | 4.29 | 61 | 4.20 | -0.10 | 16.4 Sports & Outdoor | 15 | 3.80 | 18 | 3.78 | -0.02 | 27.8 Industrial Supplies (B2B) | 4 | 4.00 | 5 | 4.00 | 0.00 | 20.0 (11 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.industry, t.industry AS industry, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')::numeric, 2) AS dec2024_avg_rating, AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_avg_rating, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01')::numeric, 2) AS dec2025_avg_rating, ROUND( (AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') - AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01'))::numeric , 2) AS rating_change_pts, ROUND( 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) , 1) AS dec2025_pct_1_2_star AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_avg_rating, (AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01')- AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')) AS rating_change_pts, 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) AS dec2025_pct_1_2_starFROM product_reviews prJOIN products p ON p.id = pr.product_id AND p.tenant_id = pr.tenant_idJOIN tenants t ON t.id = pr.tenant_idWHERE pr.is_approved = true AND ( (pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') OR (pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') )GROUP BY t.industryORDER BY rating_change_pts ASC NULLS LAST, t.industry ASCORDER BY rating_change_pts ASC NULLS LAST, t.industry ASC NULLS LAST
SELECT t.industry AS industry, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_review_count, AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_avg_rating, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_review_count, AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_avg_rating, (AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') - AVG(CAST(pr.rating AS NUMERIC(18,6))) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')) AS rating_change_pts, 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) AS dec2025_pct_1_2_star FROM product_reviews pr JOIN products p ON p.id = pr.product_id AND p.tenant_id = pr.tenant_id JOIN tenants t ON t.id = pr.tenant_id WHERE pr.is_approved = true AND ( (pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') OR (pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') ) GROUP BY t.industry ORDER BY rating_change_pts ASC NULLS LAST, t.industry ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Zara Ahmed (analyst for the Head of Merchandising): Title: December Rating Dip by Vertical: 2024 vs 2025 Question: Which merchant vertical drove the platform-wide rating dip and spike in negative reviews seen in December 2025? Grain: one row per tenant industry (vertical) Columns: - industry - dec2024_review_count - dec2024_avg_rating - dec2025_review_count - dec2025_avg_rating - rating_change_pts - dec2025_pct_1_2_star Details: Join product_reviews (is_approved = true) to products to tenants for industry. Compare the full calendar months of December 2024 (2024-12-01 to 2024-12-31) and December 2025 (2025-12-01 to 2025-12-31) based on product_reviews.created_at. dec2024_review_count / dec2024_avg_rating and dec2025_review_count / dec2025_avg_rating are the review count and mean rating for that industry in each month. rating_change_pts = dec2025_avg_rating minus dec2024_avg_rating, rounded to 2 decimals (negative means it got worse). dec2025_pct_1_2_star = share of that industry's Dec 2025 approved reviews rated 1 or 2, rounded to 1 decimal. Order by rating_change_pts ascending so the vertical with the biggest decline is first. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.industry, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')::numeric, 2) AS dec2024_avg_rating, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01')::numeric, 2) AS dec2025_avg_rating, ROUND( (AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') - AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01'))::numeric , 2) AS rating_change_pts, ROUND( 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) , 1) AS dec2025_pct_1_2_star FROM product_reviews pr JOIN products p ON p.id = pr.product_id AND p.tenant_id = pr.tenant_id JOIN tenants t ON t.id = pr.tenant_id WHERE pr.is_approved = true AND ( (pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') OR (pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') ) GROUP BY t.industry ORDER BY rating_change_pts ASC NULLS LAST, t.industry ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: industry, dec2024_review_count, dec2024_avg_rating, dec2025_review_count, dec2025_avg_rating, rating_change_pts, dec2025_pct_1_2_star
submitSELECT t.industry, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') AS dec2024_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01')::numeric, 2) AS dec2024_avg_rating, COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') AS dec2025_review_count, ROUND(AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01')::numeric, 2) AS dec2025_avg_rating, ROUND( (AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') - AVG(pr.rating) FILTER (WHERE pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01'))::numeric , 2) AS rating_change_pts, ROUND( 100.0 * COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01' AND pr.rating <= 2) / NULLIF(COUNT(*) FILTER (WHERE pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01'), 0) , 1) AS dec2025_pct_1_2_star FROM product_reviews pr JOIN products p ON p.id = pr.product_id AND p.tenant_id = pr.tenant_id JOIN tenants t ON t.id = pr.tenant_id WHERE pr.is_approved = true AND ( (pr.created_at >= DATE '2024-12-01' AND pr.created_at < DATE '2025-01-01') OR (pr.created_at >= DATE '2025-12-01' AND pr.created_at < DATE '2026-01-01') ) GROUP BY t.industry ORDER BY rating_change_pts ASC NULLS LAST, t.industry ASC
Submitted to tonight's batch. Your work on this request is done.