Queries · q347 · Head of Merchandising · week 2, night 1
“Within Cragsley Outfitters, is the poor-rating problem concentrated in particular kinds of products, or spread evenly across the catalog?”
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 2
Subject: Good start — let's dig into the risk areas
Hi Zara,
This is really useful, and a few things jumped out that I want to understand better before Friday.
The Cragsley Outfitters finding worries me most — our #2 merchant by revenue with real quality concentration, and some of those poorly-rated items are still selling briskly. That combination (strong sales, weak ratings, sizable product count) feels like the kind of thing that erodes trust quietly until it doesn't. I'd like to understand it better: is this concentrated in certain kinds of products, has it been building over time, and does it look like it's about to hit sales or is demand just lagging behind reputation?
Second, the December rating dip needs a clearer story. You flagged it as needing a second month to confirm — I agree, but I'd also like to understand whether there's something explainable behind it (seasonal returns, a particular product push, shipping delays around the holidays) rather than just watching and waiting. If Fennel & Flint and Wiesgrund Wohnen are worth a check-in, I want to go into that conversation knowing roughly what happened.
Third, I was surprised that going from zero to one image makes such a big difference but more images after that don't. Before I turn that into a merchant talking point, I want to know if we're actually looking at a real presentation effect or just a stand-in for something else — are the zero-image listings systematically different in some way, like older, or a different kind of product?
Finally, on bundles: you said it's white space but we can't test performance yet because usage is too rare. I'd like to understand it a bit more anecdotally before we design a pilot — among the handful of merchants that do use bundles, is there anything that distinguishes them, even descriptively, from the ones that don't?
Thanks again — this is exactly the kind of clarity I needed.
Freya
The analyst · week 2, night 1, request 1 of 3
Freya's reply raises four follow-up threads: Cragsley's quality concentration, the December rating dip's root cause (especially given COO's Albion Parcel carrier breakdown and CX's WISMO/late-delivery spike in Nov-Dec, which could well be the real explanation rather than a catalog issue), the zero-vs-one-image effect (is it causal or a proxy for something else like product age), and what distinguishes bundle-users descriptively. That's a lot for one week, so tonight I'm spending all 3 slots on Cragsley since it's the most urgent (our #2 merchant, flagged independently by Support Ops as having the worst response times too) and because Freya asked three distinct sub-questions about it that each deserve their own grain: which product categories concentrate the poor ratings, whether the problem is new or longstanding, and whether sales for the poor-rated products are already slowing or still growing despite the bad reviews. I'll tackle the December dip (cross-referencing carrier/shipping data), the image-effect confound, and bundle-user profiling on the remaining three nights.
The engineer · 1 dry run on the empty schema, then the submission
SELECT c.name AS category_name, pc.product_count AS product_count, COALESCE(pr.products_5plus, 0) AS products_with_5plus_reviews, COALESCE(pr.poor_rated_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(pr.products_5plus,0) = 0 THEN NULL ELSE ROUND(pr.poor_rated_count::numeric / pr.products_5plus * 100, 1) END AS pct_poor_rated_products, ROUND(pr.avg_rating, 2) AS avg_rating_of_reviewed_products, COALESCE(rv.units_sold, 0) AS units_sold_last_12mo, ROUND(COALESCE(rv.revenue_eur, 0), 0) AS revenue_eur_last_12mo FROM categories c JOIN ( SELECT p.primary_category_id, COUNT(*) AS product_count FROM products p WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND p.primary_category_id IS NOT NULL GROUP BY p.primary_category_id ) pc ON pc.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, COUNT(*) FILTER (WHERE rc.review_count >= 5) AS products_5plus, COUNT(*) FILTER (WHERE rc.review_count >= 5 AND rc.avg_rating < 3.0) AS poor_rated_count, AVG(rc.avg_rating) FILTER (WHERE rc.review_count >= 5) AS avg_rating FROM products p JOIN ( SELECT product_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating FROM product_reviews WHERE is_approved = true GROUP BY product_id ) rc ON rc.product_id = p.id WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') GROUP BY p.primary_category_id ) pr ON pr.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, SUM(oi.quantity) AS units_sold, SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE oi.line_total / er.rate END) AS revenue_eur FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN product_variants pv ON pv.id = oi.product_variant_id JOIN products p ON p.id = pv.product_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = oi.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' AND p.status = 'active' GROUP BY p.primary_category_id ) rv ON rv.primary_category_id = c.id WHERE c.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, category_name
“Looks good, compiles with the expected columns. Submitting.”
“Submitted. The report gives one row per product category for Cragsley Outfitters with product counts, review-based poor-rating metrics, and trailing-12-month units/revenue (converted to EUR), ordered by poor-rated product count to surface where the rating problem is concentrated.”
As Zara Ahmed saw it, from the small dataset the agents worked on
category_name | product_count | products_with_5plus_reviews | poor_rated_product_count | pct_poor_rated_products | avg_rating_of_reviewed_products | units_sold_last_12mo | revenue_eur_last_12mo ---------------+---------------+-----------------------------+--------------------------+-------------------------+---------------------------------+----------------------+---------------------- Tops | 83 | 8 | 3 | 37.5 | 3.43 | 594 | 23513.0 Loungewear | 40 | 3 | 2 | 66.7 | 3.22 | 255 | 17596.0 Outerwear | 79 | 4 | 2 | 50.0 | 3.04 | 378 | 58451.0 Shirts | 34 | 4 | 2 | 50.0 | 3.69 | 153 | 9670.0 Loungewear | 38 | 5 | 2 | 40.0 | 3.68 | 252 | 14215.0 Trousers | 87 | 5 | 2 | 40.0 | 3.68 | 417 | 34736.0 Sneakers | 74 | 6 | 2 | 33.3 | 3.8 | 390 | 51254.0 Outerwear | 82 | 7 | 2 | 28.6 | 4.02 | 327 | 64103.0 Dresses | 86 | 4 | 1 | 25.0 | 4.05 | 394 | 29861.0 Sandals | 80 | 6 | 1 | 16.7 | 4.12 | 334 | 20103.0 Trousers | 84 | 6 | 1 | 16.7 | 4.17 | 319 | 24561.0 Socks | 82 | 9 | 1 | 11.1 | 3.89 | 911 | 10764.0 Tops | 82 | 9 | 1 | 11.1 | 4.02 | 753 | 11310.0 Scarves & Hats | 166 | 16 | 1 | 6.3 | 4.59 | 947 | 38029.0 Boots | 78 | 3 | 0 | 0.0 | 4.74 | 198 | 35650.0 Knitwear | 40 | 2 | 0 | 0.0 | 4.83 | 282 | 22132.0 Knitwear | 42 | 4 | 0 | 0.0 | 4.68 | 177 | 13170.0 Tops | 40 | 4 | 0 | 0.0 | 4.36 | 135 | 3554.0 Accessories | 1 | 0 | 0 | NULL | NULL | 28 | 1654.0 (19 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT c.name AS category_name, pc.product_count AS product_count, COALESCE(pr.products_5plus, 0) AS products_with_5plus_reviews, COALESCE(pr.poor_rated_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(pr.products_5plus,0) = 0 THEN NULL ELSE ROUND(pr.poor_rated_count::numeric / pr.products_5plus * 100, 1) ELSE CAST(pr.poor_rated_count AS numeric(18,6)) / pr.products_5plus * 100 END AS pct_poor_rated_products, ROUND(pr.avg_rating, 2) AS avg_rating_of_reviewed_products, pr.avg_rating AS avg_rating_of_reviewed_products, COALESCE(rv.units_sold, 0) AS units_sold_last_12mo, ROUND(COALESCE(rv.revenue_eur, 0), 0) AS revenue_eur_last_12mo COALESCE(rv.revenue_eur, 0) AS revenue_eur_last_12moFROM categories cJOIN ( SELECT p.primary_category_id, COUNT(*) AS product_count SELECT p.primary_category_id AS primary_category_id, COUNT(*) AS product_count FROM products p WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND p.primary_category_id IS NOT NULL GROUP BY p.primary_category_id) pc ON pc.primary_category_id = c.idLEFT JOIN ( SELECT p.primary_category_id, SELECT p.primary_category_id AS primary_category_id, COUNT(*) FILTER (WHERE rc.review_count >= 5) AS products_5plus, COUNT(*) FILTER (WHERE rc.review_count >= 5 AND rc.avg_rating < 3.0) AS poor_rated_count, AVG(rc.avg_rating) FILTER (WHERE rc.review_count >= 5) AS avg_rating FROM products p JOIN ( SELECT product_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating SELECT product_id, COUNT(*) AS review_count, AVG(CAST(rating AS NUMERIC(18,6))) AS avg_rating FROM product_reviews WHERE is_approved = true GROUP BY product_id ) rc ON rc.product_id = p.id WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') GROUP BY p.primary_category_id) pr ON pr.primary_category_id = c.idLEFT JOIN ( SELECT p.primary_category_id, SELECT p.primary_category_id AS primary_category_id, SUM(oi.quantity) AS units_sold, SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE oi.line_total / er.rate END) AS revenue_eur SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE CAST(oi.line_total / er.rate AS NUMERIC(18,6)) END) AS revenue_eur FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN product_variants pv ON pv.id = oi.product_variant_id JOIN products p ON p.id = pv.product_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = oi.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' AND p.status = 'active' GROUP BY p.primary_category_id) rv ON rv.primary_category_id = c.idWHERE c.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters')ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, category_nameORDER BY poor_rated_product_count DESC NULLS FIRST, pct_poor_rated_products DESC NULLS LAST, category_name NULLS LAST, c.id NULLS LAST
SELECT c.name AS category_name, pc.product_count AS product_count, COALESCE(pr.products_5plus, 0) AS products_with_5plus_reviews, COALESCE(pr.poor_rated_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(pr.products_5plus,0) = 0 THEN NULL ELSE CAST(pr.poor_rated_count AS numeric(18,6)) / pr.products_5plus * 100 END AS pct_poor_rated_products, pr.avg_rating AS avg_rating_of_reviewed_products, COALESCE(rv.units_sold, 0) AS units_sold_last_12mo, COALESCE(rv.revenue_eur, 0) AS revenue_eur_last_12mo FROM categories c JOIN ( SELECT p.primary_category_id AS primary_category_id, COUNT(*) AS product_count FROM products p WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND p.primary_category_id IS NOT NULL GROUP BY p.primary_category_id ) pc ON pc.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id AS primary_category_id, COUNT(*) FILTER (WHERE rc.review_count >= 5) AS products_5plus, COUNT(*) FILTER (WHERE rc.review_count >= 5 AND rc.avg_rating < 3.0) AS poor_rated_count, AVG(rc.avg_rating) FILTER (WHERE rc.review_count >= 5) AS avg_rating FROM products p JOIN ( SELECT product_id, COUNT(*) AS review_count, AVG(CAST(rating AS NUMERIC(18,6))) AS avg_rating FROM product_reviews WHERE is_approved = true GROUP BY product_id ) rc ON rc.product_id = p.id WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') GROUP BY p.primary_category_id ) pr ON pr.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id AS primary_category_id, SUM(oi.quantity) AS units_sold, SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE CAST(oi.line_total / er.rate AS NUMERIC(18,6)) END) AS revenue_eur FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN product_variants pv ON pv.id = oi.product_variant_id JOIN products p ON p.id = pv.product_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = oi.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' AND p.status = 'active' GROUP BY p.primary_category_id ) rv ON rv.primary_category_id = c.id WHERE c.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') ORDER BY poor_rated_product_count DESC NULLS FIRST, pct_poor_rated_products DESC NULLS LAST, category_name NULLS LAST, c.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: Cragsley Outfitters: Poor Ratings by Product Category Question: Within Cragsley Outfitters, is the poor-rating problem concentrated in particular kinds of products, or spread evenly across the catalog? Grain: one row per product category (products.primary_category_id -> categories.name) for Cragsley Outfitters only Columns: - category_name - product_count - products_with_5plus_reviews - poor_rated_product_count - pct_poor_rated_products - avg_rating_of_reviewed_products - units_sold_last_12mo - revenue_eur_last_12mo Details: Filter to tenant = Cragsley Outfitters (join tenants on name). products_with_5plus_reviews = count of distinct products in that category with at least 5 approved product_reviews (all-time). poor_rated_product_count = count of those with an all-time average approved rating below 3.0. pct_poor_rated_products = poor_rated_product_count / products_with_5plus_reviews * 100 (blank if denominator 0). avg_rating_of_reviewed_products = mean all-time approved rating across products_with_5plus_reviews in the category. units_sold_last_12mo and revenue_eur_last_12mo come from order_items joined to orders (placed_at 2025-01-01 to 2025-12-31, status not cancelled), revenue converted to EUR via exchange_rates as of placed_at date, summed over all active products in the category (not just the reviewed ones). Round rating to 2 decimals, pct to 1 decimal, revenue to whole EUR. Order by poor_rated_product_count descending, tie-break by pct_poor_rated_products descending then category_name. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT c.name AS category_name, pc.product_count AS product_count, COALESCE(pr.products_5plus, 0) AS products_with_5plus_reviews, COALESCE(pr.poor_rated_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(pr.products_5plus,0) = 0 THEN NULL ELSE ROUND(pr.poor_rated_count::numeric / pr.products_5plus * 100, 1) END AS pct_poor_rated_products, ROUND(pr.avg_rating, 2) AS avg_rating_of_reviewed_products, COALESCE(rv.units_sold, 0) AS units_sold_last_12mo, ROUND(COALESCE(rv.revenue_eur, 0), 0) AS revenue_eur_last_12mo FROM categories c JOIN ( SELECT p.primary_category_id, COUNT(*) AS product_count FROM products p WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND p.primary_category_id IS NOT NULL GROUP BY p.primary_category_id ) pc ON pc.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, COUNT(*) FILTER (WHERE rc.review_count >= 5) AS products_5plus, COUNT(*) FILTER (WHERE rc.review_count >= 5 AND rc.avg_rating < 3.0) AS poor_rated_count, AVG(rc.avg_rating) FILTER (WHERE rc.review_count >= 5) AS avg_rating FROM products p JOIN ( SELECT product_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating FROM product_reviews WHERE is_approved = true GROUP BY product_id ) rc ON rc.product_id = p.id WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') GROUP BY p.primary_category_id ) pr ON pr.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, SUM(oi.quantity) AS units_sold, SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE oi.line_total / er.rate END) AS revenue_eur FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN product_variants pv ON pv.id = oi.product_variant_id JOIN products p ON p.id = pv.product_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = oi.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' AND p.status = 'active' GROUP BY p.primary_category_id ) rv ON rv.primary_category_id = c.id WHERE c.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, category_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: category_name, product_count, products_with_5plus_reviews, poor_rated_product_count, pct_poor_rated_products, avg_rating_of_reviewed_products, units_sold_last_12mo, revenue_eur_last_12mo
submitSELECT c.name AS category_name, pc.product_count AS product_count, COALESCE(pr.products_5plus, 0) AS products_with_5plus_reviews, COALESCE(pr.poor_rated_count, 0) AS poor_rated_product_count, CASE WHEN COALESCE(pr.products_5plus,0) = 0 THEN NULL ELSE ROUND(pr.poor_rated_count::numeric / pr.products_5plus * 100, 1) END AS pct_poor_rated_products, ROUND(pr.avg_rating, 2) AS avg_rating_of_reviewed_products, COALESCE(rv.units_sold, 0) AS units_sold_last_12mo, ROUND(COALESCE(rv.revenue_eur, 0), 0) AS revenue_eur_last_12mo FROM categories c JOIN ( SELECT p.primary_category_id, COUNT(*) AS product_count FROM products p WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND p.primary_category_id IS NOT NULL GROUP BY p.primary_category_id ) pc ON pc.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, COUNT(*) FILTER (WHERE rc.review_count >= 5) AS products_5plus, COUNT(*) FILTER (WHERE rc.review_count >= 5 AND rc.avg_rating < 3.0) AS poor_rated_count, AVG(rc.avg_rating) FILTER (WHERE rc.review_count >= 5) AS avg_rating FROM products p JOIN ( SELECT product_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating FROM product_reviews WHERE is_approved = true GROUP BY product_id ) rc ON rc.product_id = p.id WHERE p.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') GROUP BY p.primary_category_id ) pr ON pr.primary_category_id = c.id LEFT JOIN ( SELECT p.primary_category_id, SUM(oi.quantity) AS units_sold, SUM(CASE WHEN oi.currency_code = 'EUR' THEN oi.line_total ELSE oi.line_total / er.rate END) AS revenue_eur FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN product_variants pv ON pv.id = oi.product_variant_id JOIN products p ON p.id = pv.product_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = oi.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND o.status <> 'cancelled' AND p.status = 'active' GROUP BY p.primary_category_id ) rv ON rv.primary_category_id = c.id WHERE c.tenant_id = (SELECT id FROM tenants WHERE name = 'Cragsley Outfitters') ORDER BY poor_rated_product_count DESC, pct_poor_rated_products DESC NULLS LAST, category_name
Submitted to tonight's batch. Your work on this request is done.