DB Olympics

Queries · q355 · Head of Merchandising · week 2, night 3

Bundle Adoption Rate by Vertical

“Is bundle adoption really concentrated in specific verticals, and how strong is that pattern across the whole merchant base, not just the list of bundle-users?”

Manager · asked the questionFreya LindgrenHead of Merchandising. Came from a fashion marketplace's catalog team. Responsible for helping merchants present their products well.
Analyst · planned the reportZara AhmedExploratory: looks at the raw detail of an unfamiliar area before aggregating it.
Engineer · wrote the SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. Freya Lindgren writes to Zara Ahmed

    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

  2. Zara Ahmed plans the night

    The analyst · week 2, night 3, request 3 of 3

    Night 2 findings: (1) The shipping-delay hypothesis for the Dec dip holds for Fennel & Flint with a plausible lag — their on-time delivery crashed to 53.7% in November (from ~85-88% normally) and their bad reviews peaked the following month, December (14 reviews, 3.14 avg, 35.7% 1-2 star). But it does NOT explain Wiesgrund Wohnen at all: their delivery performance stayed strong all through Nov-Dec (90.9%, 93.8%) and they used 0% Albion Parcel every single month — yet they had an equally bad December (12 reviews, 3.5 avg, 41.7% 1-2 star). So there are two different causes behind what looked like one 'Home & Living dip,' and I need to find Wiesgrund Wohnen's actual driver. (2) The image-bucket report, run without the active-only filter this time, overturned my 'maybe they're just new/unfinished listings' theory: the 85 zero-image products are actually the OLDEST on average (1,136 days vs ~1,040 for others) and have fewer variants (3.4 vs ~5.5) — these are neglected legacy active listings, not pending-content new ones, and notably zero B2B merchants have any zero-image products (B2B is always at least minimally photographed). So the zero-to-one-image effect looks like a real, if small and old, content gap worth cleaning up, not a proxy for recency. (3) The bundle-user list makes the pattern obvious just from eyeballing the 50 rows: every single bundle-using merchant is in Beauty, Food & Coffee, Books & Stationery, Jewelry, Consumer Electronics, or Home & Living — none are Apparel & Footwear, Sports & Outdoor, or B2B wholesale, and they skew toward low-variant, giftable, small-revenue merchants. I want one clean aggregate to confirm this is a true vertical pattern and not an artifact of which merchants happened to print. Tonight: drill into Wiesgrund Wohnen's actual December driver at the product level, check whether both merchants' December dips are a recurring yearly pattern (like Cragsley's) or unique to 2025, and confirm the bundle-vertical clustering with hard numbers.

    Bundle Adoption Rate by Vertical
    Question
    Is bundle adoption really concentrated in specific verticals, and how strong is that pattern across the whole merchant base, not just the list of bundle-users?
    One row per
    tenant industry (vertical)
    Columns
    • industry
    • merchant_count
    • merchants_using_bundles
    • pct_merchants_using_bundles
    • avg_pct_products_bundled_among_users

  3. Grace writes the SQL

    The engineer · 6 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: REJECTED
    WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
),
active_products AS (
  SELECT tenant_id, id AS product_id
  FROM products
  WHERE status = 'active' AND deleted_at IS NULL
),
bundled_product_ids AS (
  SELECT DISTINCT pv.tenant_id, pv.product_id
  FROM bundle_items bi
  JOIN product_bundles pb
    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id
  JOIN product_variants pv
    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id
  WHERE pb.is_active = true
),
tenant_bundle_pct AS (
  SELECT
    ap.tenant_id,
    COUNT(DISTINCT ap.product_id) AS active_product_count,
    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count
  FROM active_products ap
  LEFT JOIN bundled_product_ids bp
    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id
  GROUP BY ap.tenant_id
),
tenant_pct AS (
  SELECT
    tenant_id,
    CASE WHEN active_product_count > 0
      THEN bundled_active_product_count::decimal / active_product_count * 100
      ELSE NULL END AS pct_active_products_in_a_bundle
  FROM tenant_bundle_pct
),
tenant_summary AS (
  SELECT
    at.tenant_id,
    COALESCE(at.industry, 'Unknown') AS industry,
    CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,
    tp.pct_active_products_in_a_bundle
  FROM active_tenants at
  LEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_id
  LEFT JOIN tenant_pct tp ON tp.tenant_id = at.tenant_id
)
SELECT
  industry,
  COUNT(*) AS merchant_count,
  SUM(uses_bundles) AS merchants_using_bundles,
  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,
  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_users
FROM tenant_summary
GROUP BY industry
ORDER BY pct_merchants_using_bundles DESC, industry ASC
    Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true),active_products AS (  SELECT tenant_id, id AS product_id  FROM products  WHERE status = 'active' AND deleted_at IS NULL),bundled_product_ids AS (  SELECT DISTINCT pv.tenant_id, pv.product_id  FROM bundle_items bi  JOIN product_bundles pb    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id  JOIN product_variants pv    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id  WHERE pb.is_active = true),tenant_bundle_pct AS (  SELECT    ap.tenant_id,    COUNT(DISTINCT ap.product_id) AS active_product_count,    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count  FROM active_products ap  LEFT JOIN bundled_product_ids bp    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id  GROUP BY ap.tenant_id),tenant_pct AS (  SELECT    tenant_id,    CASE WHEN active_product_count > 0      THEN bundled_active_product_count::decimal / active_product_count * 100      ELSE NULL END AS pct_active_products_in_a_bundle  FROM tenant_bundle_pct),tenant_summary AS (  SELECT    at.tenant_id,    COALESCE(at.industry, 'Unknown') AS industry,    CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,    tp.pct_active_products_in_a_bundle  FROM active_tenants at  LEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_id  LEFT JOIN tenant_pct tp ON tp.tenant_id = at.tenant_id)SELECT  industry,  COUNT(*) AS merchant_count,  SUM(uses_bundles) AS merchants_using_bundles,  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_usersFROM tenant_summaryGROUP BY industryORDER BY pct_merchants_using_bundles DESC, industry ASCSELECT bundled_active_product_count::decimal / active_product_count * 100 AS pctFROM (SELECT 1 AS bundled_active_product_count, 2 AS active_product_count) t
    Dry run 3 · 1 change from the dry run before · STATUS: REJECTED
    SELECT bundled_active_product_count::decimal / active_product_count * 100 AS pctFROM (SELECT 1 AS bundled_active_product_count, 2 AS active_product_count) tWITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true)SELECT  at.tenant_id,  CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundlesFROM active_tenants atLEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_id
    Dry run 4 · 2 changes from the dry run before · STATUS: REJECTED
    WITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true)SELECT  at.tenant_id,  CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles  CASE WHEN bt.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundlesFROM active_tenants atLEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_idLEFT JOIN bundle_using_tenants bt ON bt.tenant_id = at.tenant_id
    Dry run 5 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true)SELECT  at.tenant_id,  CASE WHEN bt.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundlesFROM active_tenants atLEFT JOIN bundle_using_tenants bt ON bt.tenant_id = at.tenant_id  a.tenant_id,  CASE WHEN b.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundlesFROM active_tenants aLEFT JOIN bundle_using_tenants b ON b.tenant_id = a.tenant_id

    “Good — at was being parsed as a reserved keyword. Let me rebuild the full query with safe aliases.”

    Dry run 6 · 2 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true),active_products AS (  SELECT tenant_id, id AS product_id  FROM products  WHERE status = 'active' AND deleted_at IS NULL),bundled_product_ids AS (  SELECT DISTINCT pv.tenant_id, pv.product_id  FROM bundle_items bi  JOIN product_bundles pb    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id  JOIN product_variants pv    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id  WHERE pb.is_active = true),tenant_bundle_counts AS (  SELECT    ap.tenant_id,    COUNT(DISTINCT ap.product_id) AS active_product_count,    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count  FROM active_products ap  LEFT JOIN bundled_product_ids bp    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id  GROUP BY ap.tenant_id),tenant_pct AS (  SELECT    tenant_id,    CASE WHEN active_product_count > 0      THEN bundled_active_product_count::decimal / active_product_count * 100      ELSE NULL END AS pct_active_products_in_a_bundle  FROM tenant_bundle_counts),tenant_summary AS (  SELECT    ten.tenant_id,    COALESCE(ten.industry, 'Unknown') AS industry,    CASE WHEN bu.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,    tp.pct_active_products_in_a_bundle  FROM active_tenants ten  LEFT JOIN bundle_using_tenants bu ON bu.tenant_id = ten.tenant_id  LEFT JOIN tenant_pct tp ON tp.tenant_id = ten.tenant_id)SELECT  a.tenant_id,  CASE WHEN b.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundlesFROM active_tenants aLEFT JOIN bundle_using_tenants b ON b.tenant_id = a.tenant_id  industry,  COUNT(*) AS merchant_count,  SUM(uses_bundles) AS merchants_using_bundles,  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_usersFROM tenant_summaryGROUP BY industryORDER BY pct_merchants_using_bundles DESC, industry ASC

    “The query compiles with exactly the requested columns and grain. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of what the query does: - Grain: one row per tenant industry (vertical), with industries missing a value grouped as Unknown. - merchant_count: non-deleted tenants in that industry. - merchants_using_bundles: tenants with ≥1 product_bundles row where is_active = true. - pct_merchants_using_bundles: the ratio of the two above, ×100, rounded to 1 decimal. - avg_pct_products_bundled_among_users: for each bundle-using tenant, the share of their active, non-deleted products that appear as a component variant in an active bundle, averaged only across that industry's bundle-using tenants (null when there are none). One assumption flagged in the note: since I didn't have the text of the earlier "Collections and Bundles Adoption" / "Merchants Using Bundles" reports, I defined pct_active_products_in_a_bundle as the share of a tenant's active catalog that shows up as a bundle component — the most natural reading for a merchandising "adoption depth" metric. If the prior reports used a different definition (e.g., counting the bundle's own header product), the analyst should flag it and we can re-run.”

  4. The report comes back overnight

    As Zara Ahmed saw it, from the small dataset the agents worked on

    industry                  | merchant_count | merchants_using_bundles | pct_merchants_using_bundles | avg_pct_products_bundled_among_users
--------------------------+----------------+-------------------------+-----------------------------+-------------------------------------
Beauty                    |             12 |                      12 |                       100.0 |                                  4.1
Books & Stationery        |              5 |                       5 |                       100.0 |                                  3.3
Consumer Electronics      |              8 |                       8 |                       100.0 |                                  4.7
Food & Coffee             |             11 |                      11 |                       100.0 |                                  5.3
Home & Living             |             16 |                      16 |                       100.0 |                                  2.1
Jewelry & Accessories     |              5 |                       5 |                       100.0 |                                  3.3
Apparel & Footwear        |             22 |                       0 |                         0.0 |                                 NULL
Industrial Supplies (B2B) |              5 |                       0 |                         0.0 |                                 NULL
Office Supplies (B2B)     |              4 |                       0 |                         0.0 |                                 NULL
Pet Supplies              |             10 |                       0 |                         0.0 |                                 NULL
Sports & Outdoor          |             13 |                       0 |                         0.0 |                                 NULL
(11 rows)

  5. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  6. It becomes benchmark query q355

    Changed only where the benchmark needs the same result on every system

    • spellingcast (2×)
    • numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • made explicita selected t.col gets AS col, its standard name made explicit (5×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    WITH active_tenants AS (  SELECT id AS tenant_id, industry  FROM tenants  WHERE deleted_at IS NULL),bundle_using_tenants AS (  SELECT DISTINCT tenant_id  FROM product_bundles  WHERE is_active = true),active_products AS (  SELECT tenant_id, id AS product_id  FROM products  WHERE status = 'active' AND deleted_at IS NULL),bundled_product_ids AS (  SELECT DISTINCT pv.tenant_id, pv.product_id  SELECT DISTINCT pv.tenant_id AS tenant_id, pv.product_id AS product_id  FROM bundle_items bi  JOIN product_bundles pb    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id  JOIN product_variants pv    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id  WHERE pb.is_active = true),tenant_bundle_counts AS (  SELECT    ap.tenant_id,    ap.tenant_id AS tenant_id,    COUNT(DISTINCT ap.product_id) AS active_product_count,    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count  FROM active_products ap  LEFT JOIN bundled_product_ids bp    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id  GROUP BY ap.tenant_id),tenant_pct AS (  SELECT    tenant_id,    CASE WHEN active_product_count > 0      THEN bundled_active_product_count::decimal / active_product_count * 100      THEN CAST(bundled_active_product_count AS decimal(18,6)) / active_product_count * 100      ELSE NULL END AS pct_active_products_in_a_bundle  FROM tenant_bundle_counts),tenant_summary AS (  SELECT    ten.tenant_id,    ten.tenant_id AS tenant_id,    COALESCE(ten.industry, 'Unknown') AS industry,    CASE WHEN bu.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,    tp.pct_active_products_in_a_bundle    tp.pct_active_products_in_a_bundle AS pct_active_products_in_a_bundle  FROM active_tenants ten  LEFT JOIN bundle_using_tenants bu ON bu.tenant_id = ten.tenant_id  LEFT JOIN tenant_pct tp ON tp.tenant_id = ten.tenant_id)SELECT  industry,  COUNT(*) AS merchant_count,  SUM(uses_bundles) AS merchants_using_bundles,  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_users  CAST(SUM(uses_bundles) AS decimal(18,6)) / COUNT(*) * 100 AS pct_merchants_using_bundles,  AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END) AS avg_pct_products_bundled_among_usersFROM tenant_summaryGROUP BY industryORDER BY pct_merchants_using_bundles DESC, industry ASCORDER BY pct_merchants_using_bundles DESC NULLS FIRST, industry ASC NULLS LAST

    As each system received it; highlighted, the parts spelled for that system

    WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
),
active_products AS (
  SELECT tenant_id, id AS product_id
  FROM products
  WHERE status = 'active' AND deleted_at IS NULL
),
bundled_product_ids AS (
  SELECT DISTINCT pv.tenant_id AS tenant_id, pv.product_id AS product_id
  FROM bundle_items bi
  JOIN product_bundles pb
    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id
  JOIN product_variants pv
    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id
  WHERE pb.is_active = true
),
tenant_bundle_counts AS (
  SELECT
    ap.tenant_id AS tenant_id,
    COUNT(DISTINCT ap.product_id) AS active_product_count,
    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count
  FROM active_products ap
  LEFT JOIN bundled_product_ids bp
    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id
  GROUP BY ap.tenant_id
),
tenant_pct AS (
  SELECT
    tenant_id,
    CASE WHEN active_product_count > 0
      THEN CAST(bundled_active_product_count AS decimal(18,6)) / active_product_count * 100
      ELSE NULL END AS pct_active_products_in_a_bundle
  FROM tenant_bundle_counts
),
tenant_summary AS (
  SELECT
    ten.tenant_id AS tenant_id,
    COALESCE(ten.industry, 'Unknown') AS industry,
    CASE WHEN bu.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,
    tp.pct_active_products_in_a_bundle AS pct_active_products_in_a_bundle
  FROM active_tenants ten
  LEFT JOIN bundle_using_tenants bu ON bu.tenant_id = ten.tenant_id
  LEFT JOIN tenant_pct tp ON tp.tenant_id = ten.tenant_id
)
SELECT
  industry,
  COUNT(*) AS merchant_count,
  SUM(uses_bundles) AS merchants_using_bundles,
  CAST(SUM(uses_bundles) AS decimal(18,6)) / COUNT(*) * 100 AS pct_merchants_using_bundles,
  AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END) AS avg_pct_products_bundled_among_users
FROM tenant_summary
GROUP BY industry
ORDER BY pct_merchants_using_bundles DESC NULLS FIRST, industry ASC NULLS LAST
    10 ms100 ms1 sHyper11.0 msDuckDB63.8 msGizmoSQL74.4 msclickhouse_brew107 msFirebolt73.7 msDataFusion79.9 msClickHouse33.7 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q355 on the start page, with every configuration.

  7. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Grace's whole session, 18 turns
    • The request, as Grace received it
      Report request from Zara Ahmed (analyst for the Head of Merchandising):

Title: Bundle Adoption Rate by Vertical
Question: Is bundle adoption really concentrated in specific verticals, and how strong is that pattern across the whole merchant base, not just the list of bundle-users?
Grain: one row per tenant industry (vertical)
Columns:
- industry
- merchant_count
- merchants_using_bundles
- pct_merchants_using_bundles
- avg_pct_products_bundled_among_users
Details: merchant_count = count of non-deleted tenants in that industry. merchants_using_bundles = count of those tenants with at least one product_bundles row where is_active = true. pct_merchants_using_bundles = merchants_using_bundles / merchant_count * 100, rounded to 1 decimal. avg_pct_products_bundled_among_users = average of pct_active_products_in_a_bundle (as defined in the Collections and Bundles Adoption and Merchants Using Bundles reports) across only the merchants_using_bundles in that industry; null/blank if zero users. Order by pct_merchants_using_bundles descending, tie-break by industry name.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
),
active_products AS (
  SELECT tenant_id, id AS product_id
  FROM products
  WHERE status = 'active' AND deleted_at IS NULL
),
bundled_product_ids AS (
  SELECT DISTINCT pv.tenant_id, pv.product_id
  FROM bundle_items bi
  JOIN product_bundles pb
    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id
  JOIN product_variants pv
    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id
  WHERE pb.is_active = true
),
tenant_bundle_pct AS (
  SELECT
    ap.tenant_id,
    COUNT(DISTINCT ap.product_id) AS active_product_count,
    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count
  FROM active_products ap
  LEFT JOIN bundled_product_ids bp
    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id
  GROUP BY ap.tenant_id
),
tenant_pct AS (
  SELECT
    tenant_id,
    CASE WHEN active_product_count > 0
      THEN bundled_active_product_count::decimal / active_product_count * 100
      ELSE NULL END AS pct_active_products_in_a_bundle
  FROM tenant_bundle_pct
),
tenant_summary AS (
  SELECT
    at.tenant_id,
    COALESCE(at.industry, 'Unknown') AS industry,
    CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,
    tp.pct_active_products_in_a_bundle
  FROM active_tenants at
  LEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_id
  LEFT JOIN tenant_pct tp ON tp.tenant_id = at.tenant_id
)
SELECT
  industry,
  COUNT(*) AS merchant_count,
  SUM(uses_bundles) AS merchants_using_bundles,
  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,
  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_users
FROM tenant_summary
GROUP BY industry
ORDER BY pct_merchants_using_bundles DESC, industry ASC
    • The warehouse answers
      STATUS: REJECTED
  - not a single SELECT statement: syntax error at or near "."
    • Grace calls dry_run
      SELECT bundled_active_product_count::decimal / active_product_count * 100 AS pct
FROM (SELECT 1 AS bundled_active_product_count, 2 AS active_product_count) t
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: pct
    • Grace calls dry_run
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
)
SELECT
  at.tenant_id,
  CASE WHEN but.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles
FROM active_tenants at
LEFT JOIN bundle_using_tenants but ON but.tenant_id = at.tenant_id
    • The warehouse answers
      STATUS: REJECTED
  - not a single SELECT statement: syntax error at or near "."
    • Grace calls dry_run
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
)
SELECT
  at.tenant_id,
  CASE WHEN bt.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles
FROM active_tenants at
LEFT JOIN bundle_using_tenants bt ON bt.tenant_id = at.tenant_id
    • The warehouse answers
      STATUS: REJECTED
  - not a single SELECT statement: syntax error at or near "."
    • Grace calls dry_run
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
)
SELECT
  a.tenant_id,
  CASE WHEN b.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles
FROM active_tenants a
LEFT JOIN bundle_using_tenants b ON b.tenant_id = a.tenant_id
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_id, uses_bundles
    • GraceGood — `at` was being parsed as a reserved keyword. Let me rebuild the full query with safe aliases.
    • Grace calls dry_run
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
),
active_products AS (
  SELECT tenant_id, id AS product_id
  FROM products
  WHERE status = 'active' AND deleted_at IS NULL
),
bundled_product_ids AS (
  SELECT DISTINCT pv.tenant_id, pv.product_id
  FROM bundle_items bi
  JOIN product_bundles pb
    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id
  JOIN product_variants pv
    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id
  WHERE pb.is_active = true
),
tenant_bundle_counts AS (
  SELECT
    ap.tenant_id,
    COUNT(DISTINCT ap.product_id) AS active_product_count,
    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count
  FROM active_products ap
  LEFT JOIN bundled_product_ids bp
    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id
  GROUP BY ap.tenant_id
),
tenant_pct AS (
  SELECT
    tenant_id,
    CASE WHEN active_product_count > 0
      THEN bundled_active_product_count::decimal / active_product_count * 100
      ELSE NULL END AS pct_active_products_in_a_bundle
  FROM tenant_bundle_counts
),
tenant_summary AS (
  SELECT
    ten.tenant_id,
    COALESCE(ten.industry, 'Unknown') AS industry,
    CASE WHEN bu.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,
    tp.pct_active_products_in_a_bundle
  FROM active_tenants ten
  LEFT JOIN bundle_using_tenants bu ON bu.tenant_id = ten.tenant_id
  LEFT JOIN tenant_pct tp ON tp.tenant_id = ten.tenant_id
)
SELECT
  industry,
  COUNT(*) AS merchant_count,
  SUM(uses_bundles) AS merchants_using_bundles,
  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,
  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_users
FROM tenant_summary
GROUP BY industry
ORDER BY pct_merchants_using_bundles DESC, industry ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: industry, merchant_count, merchants_using_bundles, pct_merchants_using_bundles, avg_pct_products_bundled_among_users
    • GraceThe query compiles with exactly the requested columns and grain. Submitting.
    • Grace calls submit
      WITH active_tenants AS (
  SELECT id AS tenant_id, industry
  FROM tenants
  WHERE deleted_at IS NULL
),
bundle_using_tenants AS (
  SELECT DISTINCT tenant_id
  FROM product_bundles
  WHERE is_active = true
),
active_products AS (
  SELECT tenant_id, id AS product_id
  FROM products
  WHERE status = 'active' AND deleted_at IS NULL
),
bundled_product_ids AS (
  SELECT DISTINCT pv.tenant_id, pv.product_id
  FROM bundle_items bi
  JOIN product_bundles pb
    ON pb.id = bi.bundle_id AND pb.tenant_id = bi.tenant_id
  JOIN product_variants pv
    ON pv.id = bi.variant_id AND pv.tenant_id = bi.tenant_id
  WHERE pb.is_active = true
),
tenant_bundle_counts AS (
  SELECT
    ap.tenant_id,
    COUNT(DISTINCT ap.product_id) AS active_product_count,
    COUNT(DISTINCT bp.product_id) AS bundled_active_product_count
  FROM active_products ap
  LEFT JOIN bundled_product_ids bp
    ON bp.tenant_id = ap.tenant_id AND bp.product_id = ap.product_id
  GROUP BY ap.tenant_id
),
tenant_pct AS (
  SELECT
    tenant_id,
    CASE WHEN active_product_count > 0
      THEN bundled_active_product_count::decimal / active_product_count * 100
      ELSE NULL END AS pct_active_products_in_a_bundle
  FROM tenant_bundle_counts
),
tenant_summary AS (
  SELECT
    ten.tenant_id,
    COALESCE(ten.industry, 'Unknown') AS industry,
    CASE WHEN bu.tenant_id IS NOT NULL THEN 1 ELSE 0 END AS uses_bundles,
    tp.pct_active_products_in_a_bundle
  FROM active_tenants ten
  LEFT JOIN bundle_using_tenants bu ON bu.tenant_id = ten.tenant_id
  LEFT JOIN tenant_pct tp ON tp.tenant_id = ten.tenant_id
)
SELECT
  industry,
  COUNT(*) AS merchant_count,
  SUM(uses_bundles) AS merchants_using_bundles,
  ROUND(SUM(uses_bundles)::decimal / COUNT(*) * 100, 1) AS pct_merchants_using_bundles,
  ROUND(AVG(CASE WHEN uses_bundles = 1 THEN pct_active_products_in_a_bundle END), 1) AS avg_pct_products_bundled_among_users
FROM tenant_summary
GROUP BY industry
ORDER BY pct_merchants_using_bundles DESC, industry ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. Summary of what the query does: - **Grain:** one row per tenant industry (vertical), with industries missing a value grouped as `Unknown`. - **merchant_count:** non-deleted tenants in that industry. - **merchants_using_bundles:** tenants with ≥1 `product_bundles` row where `is_active = true`. - **pct_merchants_using_bundles:** the ratio of the two above, ×100, rounded to 1 decimal. - **avg_pct_products_bundled_among_users:** for each bundle-using tenant, the share of their active, non-deleted products that appear as a component variant in an active bundle, averaged only across that industry's bundle-using tenants (null when there are none). One assumption flagged in the note: since I didn't have the text of the earlier "Collections and Bundles Adoption" / "Merchants Using Bundles" reports, I defined `pct_active_products_in_a_bundle` as the share of a tenant's active catalog that shows up as a bundle component — the most natural reading for a merchandising "adoption depth" metric. If the prior reports used a different definition (e.g., counting the bundle's own header product), the analyst should flag it and we can re-run.

    Not shown here: 239 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

    Grace's transcript (305 KB) · Zara Ahmed's transcript (894 KB) · Freya Lindgren's transcript (260 KB) · the night's plan (5 KB) · the night's reports (5 KB) · the week's memo (4 KB)