Queries · q144 · Head of Merchandising · week 1, night 3
“How much are merchants leaning on curated collections and bundles rather than flat product listings, and does that line up with stronger sales?”
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 3, request 3 of 3
Night 2 takeaways: (1) Even within Apparel & Footwear alone, the richness story is not a clean monotonic 'more images = more sales' curve — the big jump is between 0 images (109 EUR/product, and these products also have far fewer variants, ~4 vs ~13, suggesting they're incomplete/new listings) and 1+ images (355-399 EUR/product); beyond that, 2-3/4-6/7+ bounce around without a clear ladder. So the honest message for Freya is 'baseline completeness matters a lot, marginal richness less so' rather than 'more is always better'. (2) Reviews-per-100-orders is noisy but flat (~10-20%) across the whole 3-year window with no trend — so the rising raw review counts are explained by rising order volume, not growing review engagement; December 2025's high avg_rating=3.71 dip is a ratings-quality story, not a volume-of-reviews story (its reviews-per-100-orders, 15.16, is unremarkable). (3) The vertical breakdown shows Home & Living had the sharpest, most volume-credible decline (3.86→3.36, n=42→59, 35.6% 1-2 star), while Apparel & Footwear's decline is milder (-0.11) but carries about half of all December reviews (219 of ~454), so it matters in absolute terms too. Tonight I want to localize the Home & Living dip to specific merchants (one bad actor vs widespread), get a concrete worst-rated-products list platform-wide to ground Q3's 'trouble spots' ask, and open up Q4 (collections/bundles adoption vs performance), which I haven't touched yet. I'm holding the few-reviews-despite-good-sales angle and the dedicated top-vs-long-tail structural comparison (Q5) for night 4, since night 3's findings may reshape what's worth asking there.
The engineer · 1 dry run on the empty schema, then the submission
WITH tenant_revenue AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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 ), active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id ), products_in_collection AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), products_in_bundle AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id ), bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, ROUND(100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, ROUND(100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_bundle, ROUND(tr.revenue_eur) AS revenue_eur_last_12mo, ROUND(tr.revenue_eur / NULLIF(tr.orders_cnt,0)) AS avg_order_value_eur_last_12mo FROM tenant_revenue tr JOIN tenants t ON t.id = tr.tenant_id LEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_id LEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_id LEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_id LEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_id LEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_id ORDER BY revenue_eur_last_12mo DESC, merchant_name ASC LIMIT 50
“Query compiles and outputs the requested 8 columns at the right grain. Submitting.”
“Submitted. The query builds tenant-level revenue (EUR, 2025 orders excluding cancelled) and catalog-adoption metrics (collections, bundles, and the share of active products appearing in each) via separate grouped derived tables joined on tenant_id, then ranks by revenue and returns the top 50 merchants with readable column names as requested.”
As Zara Ahmed saw it, from the small dataset the agents worked on
merchant_name | industry | active_collection_count | pct_active_products_in_a_collection | active_bundle_count | pct_active_products_in_a_bundle | revenue_eur_last_12mo | avg_order_value_eur_last_12mo -----------------------+---------------------------+-------------------------+-------------------------------------+---------------------+---------------------------------+-----------------------+------------------------------ Cragsley Outfitters | Apparel & Footwear | 8 | 92.7 | 0 | 0.0 | 695716.0 | 200.0 OfficeHub Nordic | Office Supplies (B2B) | 8 | 81.4 | 0 | 0.0 | 399338.0 | 2391.0 Castell Fasteners | Industrial Supplies (B2B) | 8 | 84.2 | 0 | 0.0 | 376054.0 | 2411.0 Deskline Supplies | Office Supplies (B2B) | 7 | 100.0 | 0 | 0.0 | 285178.0 | 3802.0 Marrowby & Vane | Apparel & Footwear | 8 | 93.3 | 0 | 0.0 | 275391.0 | 165.0 Cinder Peak Sports Co. | Sports & Outdoor | 8 | 87.3 | 0 | 0.0 | 122665.0 | 374.0 Fennel & Flint | Home & Living | 8 | 81.7 | 1 | 0.0 | 64080.0 | 169.0 Petalcourt Beauty | Beauty | 8 | 75.8 | 2 | 0.0 | 60927.0 | 90.0 Kayak Corner | Sports & Outdoor | 8 | 91.1 | 0 | 0.0 | 39065.0 | 387.0 Fell & Fjell | Sports & Outdoor | 8 | 86.5 | 0 | 0.0 | 35677.0 | 452.0 Grind Collective | Food & Coffee | 8 | 84.8 | 3 | 1.3 | 24732.0 | 23.0 Oakhurst Tailoring | Apparel & Footwear | 8 | 83.3 | 0 | 0.0 | 24024.0 | 155.0 Velvet Ash | Apparel & Footwear | 8 | 88.2 | 0 | 0.0 | 23591.0 | 181.0 Kibblewick | Pet Supplies | 6 | 100.0 | 0 | 0.0 | 22531.0 | 35.0 Coastline Interiors | Home & Living | 8 | 75.3 | 4 | 3.7 | 20669.0 | 139.0 Circuit Harbor | Consumer Electronics | 5 | 100.0 | 3 | 5.4 | 18807.0 | 1045.0 Willow & Wren | Home & Living | 8 | 81.3 | 2 | 0.0 | 18796.0 | 198.0 Honeycomb Larder | Food & Coffee | 8 | 81.8 | 3 | 3.9 | 17170.0 | 28.0 Juniper Apothecary | Beauty | 8 | 81.1 | 2 | 1.9 | 17052.0 | 114.0 Nordic Bloom Cosmetics | Beauty | 8 | 80.9 | 4 | 2.9 | 16425.0 | 122.0 Bytefield | Consumer Electronics | 7 | 100.0 | 4 | 6.5 | 12458.0 | 566.0 The Balm Room | Beauty | 8 | 80.6 | 2 | 2.8 | 12420.0 | 123.0 Thistlecombe | Apparel & Footwear | 5 | 99.4 | 0 | 0.0 | 11735.0 | 135.0 Meadow Row | Apparel & Footwear | 6 | 98.5 | 0 | 0.0 | 9825.0 | 409.0 Fjordline Wear | Apparel & Footwear | 8 | 70.5 | 0 | 0.0 | 7915.0 | 147.0 Feather & Fin | Pet Supplies | 8 | 81.5 | 0 | 0.0 | 6696.0 | 31.0 Saffron & Sage | Beauty | 8 | 83.0 | 4 | 1.9 | 6675.0 | 59.0 Courtside Collective | Sports & Outdoor | 5 | 98.8 | 0 | 0.0 | 6640.0 | 349.0 Lamplight Living | Home & Living | 8 | 66.7 | 3 | 3.2 | 6631.0 | 138.0 Tail Wag Supply | Pet Supplies | 8 | 65.1 | 0 | 0.0 | 5728.0 | 64.0 Roast Republic | Food & Coffee | 8 | 86.1 | 4 | 8.3 | 5488.0 | 108.0 Hus & Hem | Home & Living | 8 | 78.0 | 3 | 0.0 | 5173.0 | 152.0 Driftwood Home | Home & Living | 8 | 78.4 | 3 | 3.9 | 4093.0 | 164.0 Haven Linen | Home & Living | 8 | 90.9 | 2 | 0.0 | 3994.0 | 166.0 Tiny Atelier | Home & Living | 8 | 81.3 | 1 | 0.0 | 3750.0 | 117.0 Brick & Bobbin | Apparel & Footwear | 8 | 97.5 | 0 | 0.0 | 3589.0 | 144.0 Purrfect Pantry | Pet Supplies | 8 | 55.3 | 0 | 0.0 | 3544.0 | 49.0 Greyfriars Supply | Apparel & Footwear | 4 | 98.8 | 0 | 0.0 | 3149.0 | 225.0 Silverthread | Jewelry & Accessories | 6 | 98.6 | 2 | 2.9 | 3002.0 | 231.0 Stoneware Studio | Home & Living | 8 | 75.0 | 3 | 2.3 | 2953.0 | 118.0 Rosehip Lab | Beauty | 3 | 98.8 | 2 | 4.9 | 2857.0 | 99.0 Paperplane Press | Books & Stationery | 8 | 72.2 | 4 | 2.8 | 2808.0 | 54.0 Barkley & Co. | Pet Supplies | 7 | 100.0 | 0 | 0.0 | 2740.0 | 76.0 Little Larch Kids | Apparel & Footwear | 5 | 98.6 | 0 | 0.0 | 2583.0 | 117.0 Alpine Loft | Sports & Outdoor | 3 | 98.8 | 0 | 0.0 | 2430.0 | 1215.0 Halden Boots | Apparel & Footwear | 4 | 98.4 | 0 | 0.0 | 2280.0 | 207.0 Folio & Fern | Books & Stationery | 8 | 98.1 | 2 | 1.9 | 1964.0 | 46.0 Signalbox | Consumer Electronics | 3 | 100.0 | 2 | 7.1 | 1440.0 | 288.0 Glasshouse Grooming | Beauty | 3 | 98.7 | 2 | 9.2 | 1397.0 | 155.0 Copper Lane | Apparel & Footwear | 3 | 98.5 | 0 | 0.0 | 1004.0 | 167.0 (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 tenant_revenue AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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),active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id),products_in_collection AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id),products_in_bundle AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id),collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id),bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id)SELECT t.name AS merchant_name, t.industry, t.industry AS industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, ROUND(100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_collection, 100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, ROUND(100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_bundle, ROUND(tr.revenue_eur) AS revenue_eur_last_12mo, ROUND(tr.revenue_eur / NULLIF(tr.orders_cnt,0)) AS avg_order_value_eur_last_12mo 100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_bundle, tr.revenue_eur AS revenue_eur_last_12mo, tr.revenue_eur / NULLIF(tr.orders_cnt,0) AS avg_order_value_eur_last_12moFROM tenant_revenue trJOIN tenants t ON t.id = tr.tenant_idLEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_idLEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_idLEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_idLEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_idLEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_idORDER BY revenue_eur_last_12mo DESC, merchant_name ASCLIMIT 50ORDER BY revenue_eur_last_12mo DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id NULLS LAST{{fetch_first(50)}}
WITH tenant_revenue AS ( SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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 ), active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id ), products_in_collection AS ( SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), products_in_bundle AS ( SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id ), bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, 100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, 100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_bundle, tr.revenue_eur AS revenue_eur_last_12mo, tr.revenue_eur / NULLIF(tr.orders_cnt,0) AS avg_order_value_eur_last_12mo FROM tenant_revenue tr JOIN tenants t ON t.id = tr.tenant_id LEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_id LEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_id LEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_id LEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_id LEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_id ORDER BY revenue_eur_last_12mo DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH tenant_revenue AS ( SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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 ), active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id ), products_in_collection AS ( SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), products_in_bundle AS ( SELECT p.tenant_id AS tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id ), bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, 100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, 100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0) AS pct_active_products_in_a_bundle, tr.revenue_eur AS revenue_eur_last_12mo, tr.revenue_eur / NULLIF(tr.orders_cnt,0) AS avg_order_value_eur_last_12mo FROM tenant_revenue tr JOIN tenants t ON t.id = tr.tenant_id LEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_id LEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_id LEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_id LEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_id LEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_id ORDER BY revenue_eur_last_12mo DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id NULLS LAST LIMIT 50
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: Collections and Bundles Adoption by Merchant Question: How much are merchants leaning on curated collections and bundles rather than flat product listings, and does that line up with stronger sales? Grain: one row per merchant (tenant), same top-50-by-revenue set as the Merchant Catalog Profile report Columns: - merchant_name - industry - active_collection_count - pct_active_products_in_a_collection - active_bundle_count - pct_active_products_in_a_bundle - revenue_eur_last_12mo - avg_order_value_eur_last_12mo Details: active_collection_count = count of non-deleted collections (deleted_at is null) for the tenant. pct_active_products_in_a_collection = share of the tenant's active, non-deleted products that appear in at least one collection_products row pointing to a non-deleted collection. active_bundle_count = count of product_bundles with is_active = true for the tenant. pct_active_products_in_a_bundle = share of the tenant's active, non-deleted products that have at least one of their variants referenced in bundle_items belonging to an active bundle. revenue_eur_last_12mo and avg_order_value_eur_last_12mo use the same order window and EUR conversion as the Merchant Catalog Profile report (orders placed 2025-01-01 to 2025-12-31, status not cancelled, exchange_rates base_code=EUR quote_code=order currency_code as_of placed_at date); avg_order_value_eur_last_12mo = revenue_eur_last_12mo / orders_last_12mo. Round percentages to 1 decimal, money to whole EUR. Order by revenue_eur_last_12mo descending, tie-break by merchant_name; print the top 50 (same ~120-tenant universe) and note total rows returned. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH tenant_revenue AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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 ), active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id ), products_in_collection AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), products_in_bundle AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id ), bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, ROUND(100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, ROUND(100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_bundle, ROUND(tr.revenue_eur) AS revenue_eur_last_12mo, ROUND(tr.revenue_eur / NULLIF(tr.orders_cnt,0)) AS avg_order_value_eur_last_12mo FROM tenant_revenue tr JOIN tenants t ON t.id = tr.tenant_id LEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_id LEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_id LEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_id LEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_id LEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_id ORDER BY revenue_eur_last_12mo DESC, merchant_name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, industry, active_collection_count, pct_active_products_in_a_collection, active_bundle_count, pct_active_products_in_a_bundle, revenue_eur_last_12mo, avg_order_value_eur_last_12mo
submitWITH tenant_revenue AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS orders_cnt FROM orders o 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 ), active_products AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' AND deleted_at IS NULL GROUP BY tenant_id ), products_in_collection AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN collection_products cp ON cp.product_id = p.id AND cp.tenant_id = p.tenant_id JOIN collections c ON c.id = cp.collection_id AND c.tenant_id = p.tenant_id AND c.deleted_at IS NULL WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), products_in_bundle AS ( SELECT p.tenant_id, COUNT(DISTINCT p.id) AS cnt FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.tenant_id = p.tenant_id JOIN bundle_items bi ON bi.variant_id = pv.id AND bi.tenant_id = pv.tenant_id JOIN product_bundles pb ON pb.id = bi.bundle_id AND pb.tenant_id = pv.tenant_id AND pb.is_active = true WHERE p.status = 'active' AND p.deleted_at IS NULL GROUP BY p.tenant_id ), collection_counts AS ( SELECT tenant_id, COUNT(*) AS active_collection_count FROM collections WHERE deleted_at IS NULL GROUP BY tenant_id ), bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry, COALESCE(cc.active_collection_count,0) AS active_collection_count, ROUND(100.0 * COALESCE(pic.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_collection, COALESCE(bc.active_bundle_count,0) AS active_bundle_count, ROUND(100.0 * COALESCE(pib.cnt,0) / NULLIF(ap.active_product_count,0), 1) AS pct_active_products_in_a_bundle, ROUND(tr.revenue_eur) AS revenue_eur_last_12mo, ROUND(tr.revenue_eur / NULLIF(tr.orders_cnt,0)) AS avg_order_value_eur_last_12mo FROM tenant_revenue tr JOIN tenants t ON t.id = tr.tenant_id LEFT JOIN active_products ap ON ap.tenant_id = tr.tenant_id LEFT JOIN products_in_collection pic ON pic.tenant_id = tr.tenant_id LEFT JOIN products_in_bundle pib ON pib.tenant_id = tr.tenant_id LEFT JOIN collection_counts cc ON cc.tenant_id = tr.tenant_id LEFT JOIN bundle_counts bc ON bc.tenant_id = tr.tenant_id ORDER BY revenue_eur_last_12mo DESC, merchant_name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.