Queries · q145 · Head of Merchandising · week 1, night 4
“Which merchants have the largest share of their reviewed catalog sitting at genuinely poor ratings, rather than a few isolated bad products?”
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 4, request 1 of 3
Night 3 takeaways: (1) The Home & Living December dip report shows most of the 13 merchants have only 1-4 reviews that month, so most of the apparent 'decline' is single bad reviews swinging a tiny average, not a systemic issue — only Fennel & Flint (14 reviews, 3.14 avg, -0.97 vs baseline) and Wiesgrund Wohnen (12 reviews, 3.5 avg, -0.6) have enough volume to be a credible, actionable signal. (2) The platform-wide worst-rated-products list is dominated by just a handful of merchants repeating over and over: Cragsley Outfitters (our #2 merchant by revenue) accounts for roughly 20 of the 50 worst-rated products, Brennquell Kaffee and Marrowby & Vane also recur heavily, and some of these badly-rated items still sell well (e.g. Aeropress Go Pour-Over Kit at Brennquell: 128 units sold, 2.83 avg rating; Corduroy Linen Shirt at Cragsley: 110 units, 2.86 rating) — meaning poor feedback hasn't yet hurt demand but is a real risk. I want to quantify this concentration properly at the merchant level rather than relying on counting rows in a product list. (3) Collections are already near-universal (most merchants show 65-100% of active products in a collection, with a suspicious clustering around exactly 8 collections per merchant — possibly a template default), so they're not a differentiator worth pushing further. Bundles, by contrast, are almost entirely unused even by our biggest merchants (0% for Cragsley, OfficeHub, Castell, Deskline, Marrowby, Cinder Peak) — this is real white space Freya could push. For this final night before the memo, I'm prioritizing: (A) quantifying the poor-rating concentration per top merchant to turn the anecdote into a rankable list, (B) the still-unanswered 'high sales but few/no reviews' piece of Q3, and (C) the dedicated top-merchants-vs-long-tail structural comparison for Q5, which I haven't produced yet and is one of Freya's five named questions.
The engineer · 1 dry run on the empty schema, then the submission
WITH revenue AS ( SELECT o.tenant_id, SUM( o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END ) AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' GROUP BY o.tenant_id ), top50 AS ( SELECT tenant_id, revenue_eur, ROW_NUMBER() OVER (ORDER BY revenue_eur DESC) AS rn FROM revenue ), product_stats AS ( SELECT p.tenant_id, p.id AS product_id, COUNT(pr.id) AS review_count, AVG(pr.rating) AS avg_rating FROM products p JOIN product_reviews pr ON pr.product_id = p.id AND pr.is_approved = true GROUP BY p.tenant_id, p.id HAVING COUNT(pr.id) >= 5 ), merchant_agg AS ( SELECT tenant_id, COUNT(*) AS products_with_5plus_reviews, COUNT(*) FILTER (WHERE avg_rating < 3.0) AS poor_rated_product_count FROM product_stats GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(ma.products_with_5plus_reviews, 0) AS products_with_5plus_reviews, COALESCE(ma.poor_rated_product_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(ma.products_with_5plus_reviews, 0) = 0 THEN NULL ELSE ROUND(ma.poor_rated_product_count::numeric / ma.products_with_5plus_reviews * 100, 1) END AS pct_poor_rated_products, ROUND(r.revenue_eur, 2) AS revenue_eur_last_12mo FROM top50 r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN merchant_agg ma ON ma.tenant_id = r.tenant_id WHERE r.rn <= 50 ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, merchant_name
“Query compiles. Submitting.”
“Submitted. Summary of the approach:
- Top-50 set: Re-derived independently using the same revenue definition as requested (orders placed in 2025, status ≠ cancelled, converted to EUR via exchange_rates at placed_at date), ranked by revenue descending, capped at 50.
- Review metrics: For each product, counted approved reviews and average rating; kept only products with ≥5 approved reviews. Per merchant, counted those products and how many averaged below 3.0.
- Percentage: poor_rated_product_count / products_with_5plus_reviews * 100, rounded to 1 decimal, left blank (NULL) when the denominator is 0.
- Ordering: poor-rated count desc, then pct desc, then merchant name, capped at 50 rows (matches the top-50 set size exactly, so no rows are lost to the page limit).
One caveat noted to the analyst: since I didn't have access to the actual stored Merchant Catalog Profile query, I rebuilt the top-50 revenue logic from the spec rather than joining its output directly — it should match as long as that report uses the same formula.”
As Zara Ahmed saw it, from the small dataset the agents worked on
merchant_name | industry | products_with_5plus_reviews | poor_rated_product_count | pct_poor_rated_products | revenue_eur_last_12mo ------------------------------+---------------------------+-----------------------------+--------------------------+-------------------------+---------------------- Cragsley Outfitters | Apparel & Footwear | 105 | 23 | 21.9 | 695715.59 Brennquell Kaffee | Food & Coffee | 61 | 8 | 13.1 | 110605.14 Marrowby & Vane | Apparel & Footwear | 28 | 5 | 17.9 | 275390.8 Studio Vrij | Apparel & Footwear | 10 | 4 | 40.0 | 107895.83 Honeycomb Larder | Food & Coffee | 4 | 2 | 50.0 | 17169.59 Spice Route Box | Food & Coffee | 5 | 2 | 40.0 | 16621.02 Grind Collective | Food & Coffee | 10 | 2 | 20.0 | 24732.45 Sourdough Club | Food & Coffee | 4 | 1 | 25.0 | 29078.06 Petalcourt Beauty | Beauty | 7 | 1 | 14.3 | 60927.41 Ampfeld Elektronik | Consumer Electronics | 6 | 0 | 0.0 | 488140.14 Ballycorran Industrial Supply | Industrial Supplies (B2B) | 4 | 0 | 0.0 | 530869.92 Castell Fasteners | Industrial Supplies (B2B) | 1 | 0 | 0.0 | 376053.99 Cinder Peak Sports Co. | Sports & Outdoor | 1 | 0 | 0.0 | 122664.88 Fennel & Flint | Home & Living | 1 | 0 | 0.0 | 64080.39 Gratlinie Outdoor | Sports & Outdoor | 4 | 0 | 0.0 | 93270.92 Kayak Corner | Sports & Outdoor | 1 | 0 | 0.0 | 39065.09 Kibblewick | Pet Supplies | 1 | 0 | 0.0 | 22531.09 Kontor24 Bürobedarf | Office Supplies (B2B) | 5 | 0 | 0.0 | 837423.2 Linden & Loom | Home & Living | 3 | 0 | 0.0 | 36505.73 Midland Safety Wear | Industrial Supplies (B2B) | 2 | 0 | 0.0 | 300676.75 OfficeHub Nordic | Office Supplies (B2B) | 3 | 0 | 0.0 | 399338.22 Rue Lafayette Studio | Apparel & Footwear | 2 | 0 | 0.0 | 27219.69 Wiesgrund Wohnen | Home & Living | 2 | 0 | 0.0 | 36991.03 Ampere & Co. | Consumer Electronics | 0 | 0 | NULL | 28717.13 Basecamp Nord | Sports & Outdoor | 0 | 0 | NULL | 9674.26 Bytefield | Consumer Electronics | 0 | 0 | NULL | 12458.03 Circuit Harbor | Consumer Electronics | 0 | 0 | NULL | 18806.68 Coastline Interiors | Home & Living | 0 | 0 | NULL | 20669.23 Deskline Supplies | Office Supplies (B2B) | 0 | 0 | NULL | 285178.35 Fell & Fjell | Sports & Outdoor | 0 | 0 | NULL | 35677.12 Hanse Industriebedarf | Industrial Supplies (B2B) | 0 | 0 | NULL | 113320.38 Hautnah | Beauty | 0 | 0 | NULL | 9170.72 Juniper Apothecary | Beauty | 0 | 0 | NULL | 17051.87 Meadow Row | Apparel & Footwear | 0 | 0 | NULL | 9825.26 Noordzee Surfwear | Apparel & Footwear | 0 | 0 | NULL | 10540.22 Nordic Bloom Cosmetics | Beauty | 0 | 0 | NULL | 16425.2 Oakhurst Tailoring | Apparel & Footwear | 0 | 0 | NULL | 24024.22 Ostwind Mode | Apparel & Footwear | 0 | 0 | NULL | 20838.76 Papeterie Grossiste | Office Supplies (B2B) | 0 | 0 | NULL | 40038.04 Pixelhaus | Consumer Electronics | 0 | 0 | NULL | 17011.55 Polder Packaging | Industrial Supplies (B2B) | 0 | 0 | NULL | 95255.14 Ridgeback Gear | Sports & Outdoor | 0 | 0 | NULL | 26906.57 Sorella Skin | Beauty | 0 | 0 | NULL | 9556.98 Strandgut | Apparel & Footwear | 0 | 0 | NULL | 12612.16 The Balm Room | Beauty | 0 | 0 | NULL | 12420.47 Thistlecombe | Apparel & Footwear | 0 | 0 | NULL | 11735.12 Velo Noord | Sports & Outdoor | 0 | 0 | NULL | 9650.03 Velvet Ash | Apparel & Footwear | 0 | 0 | NULL | 23591.19 Willow & Wren | Home & Living | 0 | 0 | NULL | 18796.06 Wohnglück | Home & Living | 0 | 0 | NULL | 20892.89 (50 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
WITH revenue AS ( SELECT o.tenant_id, o.tenant_id AS tenant_id, SUM( o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END ) AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' GROUP BY o.tenant_id),top50 AS ( SELECT tenant_id, revenue_eur, ROW_NUMBER() OVER (ORDER BY revenue_eur DESC) AS rn ROW_NUMBER() OVER (ORDER BY revenue_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rn FROM revenue),product_stats AS ( SELECT p.tenant_id, p.tenant_id AS tenant_id, p.id AS product_id, COUNT(pr.id) AS review_count, AVG(pr.rating) AS avg_rating AVG(CAST(pr.rating AS NUMERIC(18,6))) AS avg_rating FROM products p JOIN product_reviews pr ON pr.product_id = p.id AND pr.is_approved = true GROUP BY p.tenant_id, p.id HAVING COUNT(pr.id) >= 5),merchant_agg AS ( SELECT tenant_id, COUNT(*) AS products_with_5plus_reviews, COUNT(*) FILTER (WHERE avg_rating < 3.0) AS poor_rated_product_count FROM product_stats GROUP BY tenant_id)SELECT t.name AS merchant_name, t.industry, t.industry AS industry, COALESCE(ma.products_with_5plus_reviews, 0) AS products_with_5plus_reviews, COALESCE(ma.poor_rated_product_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(ma.products_with_5plus_reviews, 0) = 0 THEN NULL ELSE ROUND(ma.poor_rated_product_count::numeric / ma.products_with_5plus_reviews * 100, 1) ELSE CAST(ma.poor_rated_product_count AS numeric(18,6)) / ma.products_with_5plus_reviews * 100 END AS pct_poor_rated_products, ROUND(r.revenue_eur, 2) AS revenue_eur_last_12mo r.revenue_eur AS revenue_eur_last_12moFROM top50 rJOIN tenants t ON t.id = r.tenant_idLEFT JOIN merchant_agg ma ON ma.tenant_id = r.tenant_idWHERE r.rn <= 50ORDER BY poor_rated_product_count DESC, poor_rated_product_count DESC NULLS FIRST, pct_poor_rated_products DESC NULLS LAST, merchant_name merchant_name NULLS LAST, t.id NULLS LAST
WITH revenue AS ( SELECT o.tenant_id AS tenant_id, SUM( o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END ) AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' GROUP BY o.tenant_id ), top50 AS ( SELECT tenant_id, revenue_eur, ROW_NUMBER() OVER (ORDER BY revenue_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rn FROM revenue ), product_stats AS ( SELECT p.tenant_id AS tenant_id, p.id AS product_id, COUNT(pr.id) AS review_count, AVG(CAST(pr.rating AS NUMERIC(18,6))) AS avg_rating FROM products p JOIN product_reviews pr ON pr.product_id = p.id AND pr.is_approved = true GROUP BY p.tenant_id, p.id HAVING COUNT(pr.id) >= 5 ), merchant_agg AS ( SELECT tenant_id, COUNT(*) AS products_with_5plus_reviews, COUNT(*) FILTER (WHERE avg_rating < 3.0) AS poor_rated_product_count FROM product_stats GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS industry, COALESCE(ma.products_with_5plus_reviews, 0) AS products_with_5plus_reviews, COALESCE(ma.poor_rated_product_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(ma.products_with_5plus_reviews, 0) = 0 THEN NULL ELSE CAST(ma.poor_rated_product_count AS numeric(18,6)) / ma.products_with_5plus_reviews * 100 END AS pct_poor_rated_products, r.revenue_eur AS revenue_eur_last_12mo FROM top50 r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN merchant_agg ma ON ma.tenant_id = r.tenant_id WHERE r.rn <= 50 ORDER BY poor_rated_product_count DESC NULLS FIRST, pct_poor_rated_products DESC NULLS LAST, merchant_name NULLS LAST, t.id 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: Poorly-Rated Product Concentration by Merchant Question: Which merchants have the largest share of their reviewed catalog sitting at genuinely poor ratings, rather than a few isolated bad products? Grain: one row per merchant (tenant), same top-50-by-revenue set used in the Merchant Catalog Profile report Columns: - merchant_name - industry - products_with_5plus_reviews - poor_rated_product_count - pct_poor_rated_products - revenue_eur_last_12mo Details: Tables: products, tenants, product_reviews (is_approved = true). For each merchant, products_with_5plus_reviews = count of distinct products with at least 5 approved reviews (all-time). poor_rated_product_count = count of those products whose average approved-review rating is below 3.0. pct_poor_rated_products = poor_rated_product_count / products_with_5plus_reviews * 100, rounded to 1 decimal (blank if denominator is 0). revenue_eur_last_12mo reuses the same definition as the Merchant Catalog Profile report (orders placed 2025-01-01 to 2025-12-31, status not cancelled, EUR conversion via exchange_rates as of placed_at date) so it can be cross-referenced with that report. Order by poor_rated_product_count descending, tie-break by pct_poor_rated_products descending then merchant_name; print up to 50 rows and note the total returned. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH revenue AS ( SELECT o.tenant_id, SUM( o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END ) AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' GROUP BY o.tenant_id ), top50 AS ( SELECT tenant_id, revenue_eur, ROW_NUMBER() OVER (ORDER BY revenue_eur DESC) AS rn FROM revenue ), product_stats AS ( SELECT p.tenant_id, p.id AS product_id, COUNT(pr.id) AS review_count, AVG(pr.rating) AS avg_rating FROM products p JOIN product_reviews pr ON pr.product_id = p.id AND pr.is_approved = true GROUP BY p.tenant_id, p.id HAVING COUNT(pr.id) >= 5 ), merchant_agg AS ( SELECT tenant_id, COUNT(*) AS products_with_5plus_reviews, COUNT(*) FILTER (WHERE avg_rating < 3.0) AS poor_rated_product_count FROM product_stats GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(ma.products_with_5plus_reviews, 0) AS products_with_5plus_reviews, COALESCE(ma.poor_rated_product_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(ma.products_with_5plus_reviews, 0) = 0 THEN NULL ELSE ROUND(ma.poor_rated_product_count::numeric / ma.products_with_5plus_reviews * 100, 1) END AS pct_poor_rated_products, ROUND(r.revenue_eur, 2) AS revenue_eur_last_12mo FROM top50 r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN merchant_agg ma ON ma.tenant_id = r.tenant_id WHERE r.rn <= 50 ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, merchant_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, industry, products_with_5plus_reviews, poor_rated_product_count, pct_poor_rated_products, revenue_eur_last_12mo
submitWITH revenue AS ( SELECT o.tenant_id, SUM( o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END ) AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' GROUP BY o.tenant_id ), top50 AS ( SELECT tenant_id, revenue_eur, ROW_NUMBER() OVER (ORDER BY revenue_eur DESC) AS rn FROM revenue ), product_stats AS ( SELECT p.tenant_id, p.id AS product_id, COUNT(pr.id) AS review_count, AVG(pr.rating) AS avg_rating FROM products p JOIN product_reviews pr ON pr.product_id = p.id AND pr.is_approved = true GROUP BY p.tenant_id, p.id HAVING COUNT(pr.id) >= 5 ), merchant_agg AS ( SELECT tenant_id, COUNT(*) AS products_with_5plus_reviews, COUNT(*) FILTER (WHERE avg_rating < 3.0) AS poor_rated_product_count FROM product_stats GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(ma.products_with_5plus_reviews, 0) AS products_with_5plus_reviews, COALESCE(ma.poor_rated_product_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(ma.products_with_5plus_reviews, 0) = 0 THEN NULL ELSE ROUND(ma.poor_rated_product_count::numeric / ma.products_with_5plus_reviews * 100, 1) END AS pct_poor_rated_products, ROUND(r.revenue_eur, 2) AS revenue_eur_last_12mo FROM top50 r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN merchant_agg ma ON ma.tenant_id = r.tenant_id WHERE r.rn <= 50 ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, merchant_name
Submitted to tonight's batch. Your work on this request is done.