Queries · q352 · Head of Merchandising · week 2, night 2
“Among the handful of merchants that actually use product bundles, is there anything descriptively distinctive about them compared to the typical merchant?”
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 2, request 3 of 3
Night 1 findings on Cragsley: the poor ratings concentrate in soft-goods categories (Loungewear 66.7%/40.0% poor, Outerwear 50.0%/28.6%, Shirts 50.0%, Tops/Trousers 37-40%) while Boots, Knitwear, Scarves & Hats, Sandals are clean (0-17%) — looks like a fit/fabric-quality issue in specific garment types, not catalog-wide. Note: category names repeat (Tops, Loungewear, Outerwear, Trousers, Knitwear each appear twice) — likely duplicate category records, a data-quality wrinkle worth flagging separately but not urgent. The rating trend shows this is NOT new: pct_1_2_star spikes every December (25.0% in 2023, 22.1% in 2024, 25.7% in 2025) alongside every November/December order surge — a recurring seasonal pattern, not a 2025-specific crisis, which tempers how I should frame the Dec 2025 dip for Cragsley specifically. The sales-trajectory report shows the poor-tier's absolute revenue did grow (896→6,177 EUR/month) but its share of Cragsley's total shrank from ~23% to ~6% as the rest of the catalog scaled much faster — so it's 'stagnating while the business grows around it,' not yet a demand collapse. That closes out the Cragsley thread well enough for the memo. Tonight I'm moving to the other three asks: (1) test the shipping/carrier hypothesis for the Dec rating dip by looking at delivery performance alongside review sentiment for Fennel & Flint and Wiesgrund Wohnen specifically, since COO's team flagged an Albion Parcel carrier breakdown in exactly this window; (2) test whether zero-image products are systematically older/draft/different-vertical rather than just under-photographed, by looking at age, status and vertical mix by image bucket without restricting to active products only (the earlier report did, which could have hidden this); (3) get a raw, merchant-level profile of the handful of merchants that actually use bundles, to look for descriptive patterns before even thinking about a pilot design.
The engineer · 1 dry run on the empty schema, then the submission
WITH bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ), product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id ), variant_counts AS ( SELECT p.tenant_id, COUNT(pv.id)::decimal / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id ), bundled_products AS ( SELECT DISTINCT p.tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active' ), bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id ), order_stats AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS order_count 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 ) SELECT t.name AS merchant_name, t.industry, bc.active_bundle_count, ROUND(100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0), 1) AS pct_active_products_in_a_bundle, pc.active_product_count, ROUND(vc.avg_variants_per_product, 2) AS avg_variants_per_product, ROUND(os.revenue_eur, 2) AS revenue_eur_last_12mo, ROUND(os.revenue_eur / NULLIF(os.order_count, 0), 2) AS avg_order_value_eur_last_12mo FROM bundle_counts bc JOIN tenants t ON t.id = bc.tenant_id LEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_id LEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_id LEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_id LEFT JOIN order_stats os ON os.tenant_id = bc.tenant_id ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC LIMIT 50
“Query compiles cleanly with the expected 8 columns at tenant grain. Submitting.”
“Submitted. Summary of what the report does:
- Grain: one row per tenant with ≥1 active product_bundles row.
- active_bundle_count: count of active bundles per tenant.
- active_product_count / avg_variants_per_product: scoped to products.status='active', variants restricted to is_active=true.
- pct_active_products_in_a_bundle: share of those active products whose variants appear as a component (bundle_items.variant_id) of an active bundle.
- revenue_eur_last_12mo / avg_order_value_eur_last_12mo: orders placed 2025-01-01–2025-12-31, status ≠ cancelled, converted to EUR via same-day exchange_rates (EUR→currency).
- Ordered by pct descending, then revenue descending, with tenant id as a final deterministic tiebreak; capped at 50 rows as requested.
I flagged in the submit note that I inferred the exact scoping/"in a bundle" definition since I didn't have the text of the earlier "Collections and Bundles Adoption" / "Merchant Catalog Profile" reports to copy verbatim — worth a quick check against those if Zara wants bit-for-bit consistency.”
As Zara Ahmed saw it, from the small dataset the agents worked on
merchant_name | industry | active_bundle_count | pct_active_products_in_a_bundle | active_product_count | avg_variants_per_product | revenue_eur_last_12mo | avg_order_value_eur_last_12mo -----------------------+-----------------------+---------------------+---------------------------------+----------------------+--------------------------+-----------------------+------------------------------ Marginalia | Books & Stationery | 3 | 18.0 | 61 | 2.02 | NULL | NULL Maison Terre | Home & Living | 4 | 15.5 | 71 | 1.7 | NULL | NULL Olive & Oak Pantry | Food & Coffee | 4 | 13.5 | 74 | 1.22 | 118.55 | 118.55 Kaffeekontor Nord | Food & Coffee | 3 | 11.4 | 70 | 1.17 | NULL | NULL Petal & Pestle | Beauty | 3 | 11.1 | 18 | 2.0 | NULL | NULL Chocolaterie Mireille | Food & Coffee | 4 | 10.1 | 79 | 1.48 | NULL | NULL Pixelhaus | Consumer Electronics | 3 | 9.6 | 73 | 2.29 | NULL | NULL Glasshouse Grooming | Beauty | 2 | 9.2 | 76 | 2.33 | 1397.46 | 155.27 Goldkind | Jewelry & Accessories | 3 | 8.9 | 79 | 1.95 | NULL | NULL Roast Republic | Food & Coffee | 4 | 8.3 | 36 | 1.89 | 5488.11 | 107.61 Aurum & Ash | Jewelry & Accessories | 2 | 8.1 | 37 | 3.03 | NULL | NULL Dewdrop Studio | Beauty | 4 | 7.4 | 68 | 2.09 | 271.34 | 54.27 Signalbox | Consumer Electronics | 2 | 7.1 | 70 | 2.27 | 1439.85 | 287.97 Hautnah | Beauty | 4 | 6.8 | 44 | 2.34 | NULL | NULL Buchstabe | Books & Stationery | 4 | 6.7 | 45 | 2.13 | NULL | NULL Bytefield | Consumer Electronics | 4 | 6.5 | 46 | 2.33 | 12458.03 | 566.27 Northwire Audio | Consumer Electronics | 3 | 6.5 | 31 | 2.48 | NULL | NULL Velours Paris | Beauty | 4 | 6.5 | 46 | 2.65 | NULL | NULL Ondine Botanicals | Beauty | 3 | 6.3 | 63 | 2.3 | NULL | NULL Circuit Harbor | Consumer Electronics | 3 | 5.4 | 93 | 2.24 | 18806.68 | 1044.82 Rosehip Lab | Beauty | 2 | 4.9 | 82 | 2.26 | 2857.4 | 98.53 Brewline Coffee | Food & Coffee | 1 | 4.8 | 84 | 1.55 | NULL | NULL Tidepool Jewels | Jewelry & Accessories | 1 | 4.8 | 83 | 5.8 | NULL | NULL Teapot Society | Food & Coffee | 1 | 4.5 | 66 | 1.38 | 78.81 | 78.81 Lumio Tech | Consumer Electronics | 1 | 4.3 | 69 | 1.94 | NULL | NULL Honeycomb Larder | Food & Coffee | 3 | 3.9 | 77 | 1.0 | 17169.59 | 28.15 Driftwood Home | Home & Living | 3 | 3.9 | 51 | 2.08 | 4093.15 | 163.73 Klara Beauty | Beauty | 1 | 3.8 | 53 | 1.85 | NULL | NULL Coastline Interiors | Home & Living | 4 | 3.7 | 81 | 2.37 | 20669.23 | 138.72 Kabelwerk | Consumer Electronics | 1 | 3.7 | 81 | 2.46 | NULL | NULL Lamplight Living | Home & Living | 3 | 3.2 | 63 | 1.98 | 6630.61 | 138.14 Mirelle | Beauty | 4 | 3.1 | 32 | 1.84 | NULL | NULL Nordic Bloom Cosmetics | Beauty | 4 | 2.9 | 68 | 2.22 | 16425.2 | 121.67 Silverthread | Jewelry & Accessories | 2 | 2.9 | 70 | 3.96 | 3001.87 | 230.91 Sorella Skin | Beauty | 1 | 2.9 | 69 | 2.03 | NULL | NULL The Balm Room | Beauty | 2 | 2.8 | 36 | 2.28 | 12420.47 | 122.97 Paperplane Press | Books & Stationery | 4 | 2.8 | 36 | 2.14 | 2808.37 | 54.01 Inkwell & Quire | Books & Stationery | 1 | 2.5 | 81 | 2.02 | 646.82 | 43.12 The Pencil Case | Books & Stationery | 1 | 2.5 | 79 | 2.14 | 25.95 | 6.49 Stoneware Studio | Home & Living | 3 | 2.3 | 44 | 2.41 | 2952.89 | 118.12 Fern & Fable | Home & Living | 4 | 2.3 | 44 | 1.86 | NULL | NULL Oak & Ember | Home & Living | 1 | 2.2 | 90 | 2.17 | NULL | NULL Juniper Apothecary | Beauty | 2 | 1.9 | 53 | 1.96 | 17051.87 | 114.44 Saffron & Sage | Beauty | 4 | 1.9 | 53 | 1.75 | 6674.76 | 59.07 Folio & Fern | Books & Stationery | 2 | 1.9 | 54 | 2.15 | 1963.85 | 45.67 Grind Collective | Food & Coffee | 3 | 1.3 | 79 | 1.13 | 24732.45 | 22.61 Ampfeld Elektronik | Consumer Electronics | 3 | 1.1 | 178 | 2.3 | NULL | NULL Wiesgrund Wohnen | Home & Living | 3 | 0.9 | 106 | 2.2 | NULL | NULL Brennquell Kaffee | Food & Coffee | 3 | 0.6 | 175 | 1.33 | NULL | NULL Fennel & Flint | Home & Living | 1 | 0.0 | 93 | 2.62 | 64080.39 | 169.08 (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 bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id),product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id),variant_counts AS ( SELECT p.tenant_id, COUNT(pv.id)::decimal / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product SELECT p.tenant_id AS tenant_id, CAST(COUNT(pv.id) AS decimal(18,6)) / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id),bundled_products AS ( SELECT DISTINCT p.tenant_id, p.id AS product_id SELECT DISTINCT p.tenant_id AS tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active'),bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id),order_stats 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 order_count 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)SELECT t.name AS merchant_name, t.industry, bc.active_bundle_count, ROUND(100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0), 1) AS pct_active_products_in_a_bundle, pc.active_product_count, ROUND(vc.avg_variants_per_product, 2) AS avg_variants_per_product, ROUND(os.revenue_eur, 2) AS revenue_eur_last_12mo, ROUND(os.revenue_eur / NULLIF(os.order_count, 0), 2) AS avg_order_value_eur_last_12mo t.industry AS industry, bc.active_bundle_count AS active_bundle_count, 100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0) AS pct_active_products_in_a_bundle, pc.active_product_count AS active_product_count, vc.avg_variants_per_product AS avg_variants_per_product, os.revenue_eur AS revenue_eur_last_12mo, os.revenue_eur / NULLIF(os.order_count, 0) AS avg_order_value_eur_last_12moFROM bundle_counts bcJOIN tenants t ON t.id = bc.tenant_idLEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_idLEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_idLEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_idLEFT JOIN order_stats os ON os.tenant_id = bc.tenant_idORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASCLIMIT 50ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(50)}}
WITH bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ), product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id ), variant_counts AS ( SELECT p.tenant_id AS tenant_id, CAST(COUNT(pv.id) AS decimal(18,6)) / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id ), bundled_products AS ( SELECT DISTINCT p.tenant_id AS tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active' ), bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id ), order_stats AS ( SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur, COUNT(*) AS order_count 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 ) SELECT t.name AS merchant_name, t.industry AS industry, bc.active_bundle_count AS active_bundle_count, 100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0) AS pct_active_products_in_a_bundle, pc.active_product_count AS active_product_count, vc.avg_variants_per_product AS avg_variants_per_product, os.revenue_eur AS revenue_eur_last_12mo, os.revenue_eur / NULLIF(os.order_count, 0) AS avg_order_value_eur_last_12mo FROM bundle_counts bc JOIN tenants t ON t.id = bc.tenant_id LEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_id LEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_id LEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_id LEFT JOIN order_stats os ON os.tenant_id = bc.tenant_id ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ), product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id ), variant_counts AS ( SELECT p.tenant_id AS tenant_id, CAST(COUNT(pv.id) AS decimal(18,6)) / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id ), bundled_products AS ( SELECT DISTINCT p.tenant_id AS tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active' ), bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id ), order_stats AS ( SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur, COUNT(*) AS order_count 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 ) SELECT t.name AS merchant_name, t.industry AS industry, bc.active_bundle_count AS active_bundle_count, 100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0) AS pct_active_products_in_a_bundle, pc.active_product_count AS active_product_count, vc.avg_variants_per_product AS avg_variants_per_product, os.revenue_eur AS revenue_eur_last_12mo, os.revenue_eur / NULLIF(os.order_count, 0) AS avg_order_value_eur_last_12mo FROM bundle_counts bc JOIN tenants t ON t.id = bc.tenant_id LEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_id LEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_id LEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_id LEFT JOIN order_stats os ON os.tenant_id = bc.tenant_id ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC 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: Merchants Using Bundles: Raw Profile Question: Among the handful of merchants that actually use product bundles, is there anything descriptively distinctive about them compared to the typical merchant? Grain: one row per merchant (tenant) that has at least one active product_bundle (is_active = true) Columns: - merchant_name - industry - active_bundle_count - pct_active_products_in_a_bundle - active_product_count - avg_variants_per_product - revenue_eur_last_12mo - avg_order_value_eur_last_12mo Details: Filter to tenants with at least one product_bundles row where is_active = true. active_bundle_count, pct_active_products_in_a_bundle, active_product_count, avg_variants_per_product, revenue_eur_last_12mo and avg_order_value_eur_last_12mo all use the same definitions as the earlier Collections and Bundles Adoption by Merchant and Merchant Catalog Profile reports (orders placed 2025-01-01 to 2025-12-31, status not cancelled, EUR conversion via exchange_rates as of placed_at date), so figures are directly comparable. No row cap expected to matter (likely well under 50 merchants) but apply the standard 50-row cap and note total rows returned. Order by pct_active_products_in_a_bundle descending, tie-break by revenue_eur_last_12mo descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ), product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id ), variant_counts AS ( SELECT p.tenant_id, COUNT(pv.id)::decimal / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id ), bundled_products AS ( SELECT DISTINCT p.tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active' ), bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id ), order_stats AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS order_count 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 ) SELECT t.name AS merchant_name, t.industry, bc.active_bundle_count, ROUND(100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0), 1) AS pct_active_products_in_a_bundle, pc.active_product_count, ROUND(vc.avg_variants_per_product, 2) AS avg_variants_per_product, ROUND(os.revenue_eur, 2) AS revenue_eur_last_12mo, ROUND(os.revenue_eur / NULLIF(os.order_count, 0), 2) AS avg_order_value_eur_last_12mo FROM bundle_counts bc JOIN tenants t ON t.id = bc.tenant_id LEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_id LEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_id LEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_id LEFT JOIN order_stats os ON os.tenant_id = bc.tenant_id ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, industry, active_bundle_count, pct_active_products_in_a_bundle, active_product_count, avg_variants_per_product, revenue_eur_last_12mo, avg_order_value_eur_last_12mo
submitWITH bundle_counts AS ( SELECT tenant_id, COUNT(*) AS active_bundle_count FROM product_bundles WHERE is_active = true GROUP BY tenant_id ), product_counts AS ( SELECT tenant_id, COUNT(*) AS active_product_count FROM products WHERE status = 'active' GROUP BY tenant_id ), variant_counts AS ( SELECT p.tenant_id, COUNT(pv.id)::decimal / NULLIF(COUNT(DISTINCT p.id), 0) AS avg_variants_per_product FROM products p JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = true WHERE p.status = 'active' GROUP BY p.tenant_id ), bundled_products AS ( SELECT DISTINCT p.tenant_id, p.id AS product_id FROM product_bundles pb JOIN bundle_items bi ON bi.bundle_id = pb.id JOIN product_variants pv ON pv.id = bi.variant_id JOIN products p ON p.id = pv.product_id WHERE pb.is_active = true AND p.status = 'active' ), bundled_product_counts AS ( SELECT tenant_id, COUNT(*) AS bundled_product_count FROM bundled_products GROUP BY tenant_id ), order_stats AS ( SELECT o.tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur, COUNT(*) AS order_count 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 ) SELECT t.name AS merchant_name, t.industry, bc.active_bundle_count, ROUND(100.0 * COALESCE(bpc.bundled_product_count, 0) / NULLIF(pc.active_product_count, 0), 1) AS pct_active_products_in_a_bundle, pc.active_product_count, ROUND(vc.avg_variants_per_product, 2) AS avg_variants_per_product, ROUND(os.revenue_eur, 2) AS revenue_eur_last_12mo, ROUND(os.revenue_eur / NULLIF(os.order_count, 0), 2) AS avg_order_value_eur_last_12mo FROM bundle_counts bc JOIN tenants t ON t.id = bc.tenant_id LEFT JOIN product_counts pc ON pc.tenant_id = bc.tenant_id LEFT JOIN variant_counts vc ON vc.tenant_id = bc.tenant_id LEFT JOIN bundled_product_counts bpc ON bpc.tenant_id = bc.tenant_id LEFT JOIN order_stats os ON os.tenant_id = bc.tenant_id ORDER BY pct_active_products_in_a_bundle DESC NULLS LAST, revenue_eur_last_12mo DESC NULLS LAST, t.id ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.