DB Olympics

Queries · q139 · Head of Merchandising · week 1, night 2

Image-Richness vs Sales Within Apparel & Footwear

“Within a single vertical, removing the cross-vertical confound seen in the platform-wide bucket report, do products with more images actually sell better?”

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 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.

    1. How healthy is our catalog overall, and where is it thin? I'd like a sense of how many products and variants each merchant is actually running, which ones feel well-built out (good imagery, descriptions, variants) versus sparse, and whether that varies a lot across merchants or verticals. I need this to know where to focus my attention first.
    2. Does catalog quality actually move sales? I'm curious whether products with richer content — more images, more reviews, better ratings — sell noticeably better than ones without. I want to build the case (to myself and to merchants) for why investing in catalog presentation is worth it.
    3. What do customers think of the products, and where are the trouble spots? I'd like to see how reviews and ratings are trending, which products or categories are getting poor feedback, and whether there are products with surprisingly few or no reviews despite decent sales. This tells me where to nudge merchants on quality or listing improvements.
    4. Are we using collections and bundles well? I want to know how much merchants are leaning on curated collections and bundles versus just flat product listings, and whether those grouped presentations correlate with better performance. This will shape whether I push merchandising tools harder in my conversations with merchants.
    5. How are our top merchants' catalogs structured compared to the long tail? Since a handful of merchants drive most of the volume, I'd like to understand if their catalogs look meaningfully different (size, categorization, media richness) from smaller merchants — it'll help me figure out what "good" looks like and what advice to bring to the smaller ones.

    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

  2. Zara Ahmed plans the night

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

    Night 1 findings to react to: (1) In the merchant profile, image-presence (97-100%) and variant richness are almost entirely explained by vertical (apparel ~12-14 variants/product vs B2B ~1-2), not merchant skill — so 'catalog health' comparisons need to control for vertical. A couple of outliers stood out for later: Bytefield's avg_rating of 1.50 and Circuit Harbor's thin 18 orders against 93 products, both worth a Q3 drill-down later. (2) The image-count-bucket report is NOT monotonic — bucket '1 image' (265 EUR/product) beats '2-3' and '4-6' (183, 192), only catching back up at '7+' (521). That's suspicious and likely confounded by vertical mix (B2B products tend to have 1 plain image but high per-order value; apparel has many images but lower per-unit revenue). I want to re-run the richness-vs-sales comparison inside a single vertical to remove that confound before telling Freya 'more images = more sales'. (3) The monthly review trend shows review counts climbing through the series with sharp December spikes each year, but December 2025 stands out with the lowest avg_rating (3.71) and highest pct_1_2_star (25.8%) in the whole 36-month series — a real anomaly worth root-causing. Also, since order volume is growing over the same period, I can't yet tell if rising review counts reflect genuine engagement growth or just mirror order growth — need review-per-order to normalize that. Tonight's three requests: control for vertical on richness-vs-sales, normalize review volume by order volume, and localize the December 2025 rating dip by vertical.

    Image-Richness vs Sales Within Apparel & Footwear
    Question
    Within a single vertical, removing the cross-vertical confound seen in the platform-wide bucket report, do products with more images actually sell better?
    One row per
    product image-count bucket (0, 1, 2-3, 4-6, 7+), restricted to products belonging to tenants in the Apparel & Footwear industry
    Columns
    • image_count_bucket
    • product_count
    • pct_with_description
    • avg_variants_per_product
    • avg_reviews_per_product
    • avg_rating
    • avg_units_sold_per_product_last_12mo
    • avg_revenue_per_product_eur_last_12mo

  3. Grace writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH apparel_tenants AS (
    SELECT id AS tenant_id
    FROM tenants
    WHERE industry = 'Apparel & Footwear'
),
base_products AS (
    SELECT p.id AS product_id,
           p.tenant_id,
           p.description
    FROM products p
    JOIN apparel_tenants t ON t.tenant_id = p.tenant_id
    WHERE p.status = 'active'
      AND p.deleted_at IS NULL
),
image_counts AS (
    SELECT bp.product_id,
           COUNT(pm.id) AS image_count
    FROM base_products bp
    LEFT JOIN product_media pm
           ON pm.product_id = bp.product_id
          AND pm.kind = 'image'
    GROUP BY bp.product_id
),
variant_counts AS (
    SELECT bp.product_id,
           COUNT(pv.id) AS variant_count
    FROM base_products bp
    LEFT JOIN product_variants pv
           ON pv.product_id = bp.product_id
          AND pv.deleted_at IS NULL
    GROUP BY bp.product_id
),
review_stats AS (
    SELECT bp.product_id,
           COUNT(pr.id) AS review_count,
           AVG(pr.rating::numeric) AS avg_rating_for_product
    FROM base_products bp
    LEFT JOIN product_reviews pr
           ON pr.product_id = bp.product_id
          AND pr.is_approved = true
    GROUP BY bp.product_id
),
order_items_in_window AS (
    SELECT oi.product_variant_id,
           oi.quantity,
           oi.line_total,
           oi.currency_code,
           o.placed_at
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.placed_at >= TIMESTAMP '2025-01-01'
      AND o.placed_at < TIMESTAMP '2026-01-01'
      AND o.status <> 'cancelled'
),
order_items_eur AS (
    SELECT oiw.product_variant_id,
           oiw.quantity,
           oiw.line_total / er.rate AS line_total_eur
    FROM order_items_in_window oiw
    JOIN exchange_rates er
           ON er.base_code = 'EUR'
          AND er.quote_code = oiw.currency_code
          AND er.as_of = CAST(oiw.placed_at AS date)
),
variant_product_map AS (
    SELECT pv.id AS variant_id,
           pv.product_id
    FROM product_variants pv
    JOIN base_products bp ON bp.product_id = pv.product_id
),
product_sales AS (
    SELECT vpm.product_id,
           SUM(oie.quantity) AS units_sold,
           SUM(oie.line_total_eur) AS revenue_eur
    FROM variant_product_map vpm
    JOIN order_items_eur oie ON oie.product_variant_id = vpm.variant_id
    GROUP BY vpm.product_id
),
product_metrics AS (
    SELECT
        bp.product_id,
        CASE
            WHEN ic.image_count = 0 THEN '0'
            WHEN ic.image_count = 1 THEN '1'
            WHEN ic.image_count BETWEEN 2 AND 3 THEN '2-3'
            WHEN ic.image_count BETWEEN 4 AND 6 THEN '4-6'
            ELSE '7+'
        END AS image_count_bucket,
        CASE WHEN bp.description IS NOT NULL AND bp.description <> '' THEN 1 ELSE 0 END AS has_description,
        COALESCE(vc.variant_count, 0) AS variant_count,
        COALESCE(rs.review_count, 0) AS review_count,
        rs.avg_rating_for_product,
        COALESCE(ps.units_sold, 0) AS units_sold,
        COALESCE(ps.revenue_eur, 0) AS revenue_eur
    FROM base_products bp
    JOIN image_counts ic ON ic.product_id = bp.product_id
    LEFT JOIN variant_counts vc ON vc.product_id = bp.product_id
    LEFT JOIN review_stats rs ON rs.product_id = bp.product_id
    LEFT JOIN product_sales ps ON ps.product_id = bp.product_id
)
SELECT
    image_count_bucket,
    COUNT(*) AS product_count,
    ROUND(100.0 * SUM(has_description) / COUNT(*), 2) AS pct_with_description,
    ROUND(AVG(variant_count), 2) AS avg_variants_per_product,
    ROUND(AVG(review_count), 2) AS avg_reviews_per_product,
    ROUND(AVG(avg_rating_for_product) FILTER (WHERE review_count > 0), 2) AS avg_rating,
    ROUND(AVG(units_sold), 2) AS avg_units_sold_per_product_last_12mo,
    ROUND(AVG(revenue_eur), 0) AS avg_revenue_per_product_eur_last_12mo
FROM product_metrics
GROUP BY image_count_bucket
ORDER BY
    CASE image_count_bucket
        WHEN '0' THEN 1
        WHEN '1' THEN 2
        WHEN '2-3' THEN 3
        WHEN '4-6' THEN 4
        WHEN '7+' THEN 5
    END

    “Query compiles and returns exactly the requested columns. 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 the approach: - Scope: active, non-deleted products belonging to tenants where tenants.industry = 'Apparel & Footwear'. - Bucketing: products grouped by count of product_media rows with kind='image' into 0 / 1 / 2-3 / 4-6 / 7+. - Enrichment per product: description presence, variant count (product_variants, excluding soft-deleted), approved review count and per-product average rating (product_reviews where is_approved), and trailing-12-month (2025) units/revenue from order_items→orders (status ≠ cancelled), with revenue converted to EUR via the daily exchange_rates (EUR→order currency) as of each order's placed_at date. - Aggregation: one row per bucket with product counts and the requested averages/rates, rounded per spec, ordered naturally (0, 1, 2-3, 4-6, 7+). This mirrors the night-1 platform-wide report's methodology, just scoped to a single vertical to remove the cross-vertical confound, as requested.”

  4. The report comes back overnight

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

    image_count_bucket | product_count | pct_with_description | avg_variants_per_product | avg_reviews_per_product | avg_rating | avg_units_sold_per_product_last_12mo | avg_revenue_per_product_eur_last_12mo
-------------------+---------------+----------------------+--------------------------+-------------------------+------------+--------------------------------------+--------------------------------------
0                  |            29 |                 93.1 |                     4.24 |                     0.0 |       NULL |                                  2.0 |                                 109.0
1                  |           194 |                 73.2 |                    13.81 |                    0.53 |       3.85 |                                 4.84 |                                 355.0
2-3                |          1004 |                69.92 |                    12.92 |                    0.66 |       3.62 |                                 5.74 |                                 382.0
4-6                |           768 |                69.79 |                    13.05 |                    0.65 |       3.75 |                                 5.49 |                                 326.0
7+                 |            64 |                78.13 |                    13.45 |                    0.59 |       3.63 |                                 6.22 |                                 399.0
(5 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 q139

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

    • spellingcast
    • numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (16×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
    The benchmark text, against the submitted SQL
    WITH apparel_tenants AS (    SELECT id AS tenant_id    FROM tenants    WHERE industry = 'Apparel & Footwear'),base_products AS (    SELECT p.id AS product_id,           p.tenant_id,           p.description           p.tenant_id AS tenant_id,           p.description AS description    FROM products p    JOIN apparel_tenants t ON t.tenant_id = p.tenant_id    WHERE p.status = 'active'      AND p.deleted_at IS NULL),image_counts AS (    SELECT bp.product_id,    SELECT bp.product_id AS product_id,           COUNT(pm.id) AS image_count    FROM base_products bp    LEFT JOIN product_media pm           ON pm.product_id = bp.product_id          AND pm.kind = 'image'    GROUP BY bp.product_id),variant_counts AS (    SELECT bp.product_id,    SELECT bp.product_id AS product_id,           COUNT(pv.id) AS variant_count    FROM base_products bp    LEFT JOIN product_variants pv           ON pv.product_id = bp.product_id          AND pv.deleted_at IS NULL    GROUP BY bp.product_id),review_stats AS (    SELECT bp.product_id,    SELECT bp.product_id AS product_id,           COUNT(pr.id) AS review_count,           AVG(pr.rating::numeric) AS avg_rating_for_product           AVG(CAST(pr.rating AS numeric(18,6))) AS avg_rating_for_product    FROM base_products bp    LEFT JOIN product_reviews pr           ON pr.product_id = bp.product_id          AND pr.is_approved = true    GROUP BY bp.product_id),order_items_in_window AS (    SELECT oi.product_variant_id,           oi.quantity,           oi.line_total,           oi.currency_code,           o.placed_at    SELECT oi.product_variant_id AS product_variant_id,           oi.quantity AS quantity,           oi.line_total AS line_total,           oi.currency_code AS currency_code,           o.placed_at AS placed_at    FROM order_items oi    JOIN orders o ON o.id = oi.order_id    WHERE o.placed_at >= TIMESTAMP '2025-01-01'      AND o.placed_at < TIMESTAMP '2026-01-01'      AND o.status <> 'cancelled'),order_items_eur AS (    SELECT oiw.product_variant_id,           oiw.quantity,           oiw.line_total / er.rate AS line_total_eur    SELECT oiw.product_variant_id AS product_variant_id,           oiw.quantity AS quantity,           CAST(oiw.line_total / er.rate AS NUMERIC(18,6)) AS line_total_eur    FROM order_items_in_window oiw    JOIN exchange_rates er           ON er.base_code = 'EUR'          AND er.quote_code = oiw.currency_code          AND er.as_of = CAST(oiw.placed_at AS date)),variant_product_map AS (    SELECT pv.id AS variant_id,           pv.product_id           pv.product_id AS product_id    FROM product_variants pv    JOIN base_products bp ON bp.product_id = pv.product_id),product_sales AS (    SELECT vpm.product_id,    SELECT vpm.product_id AS product_id,           SUM(oie.quantity) AS units_sold,           SUM(oie.line_total_eur) AS revenue_eur    FROM variant_product_map vpm    JOIN order_items_eur oie ON oie.product_variant_id = vpm.variant_id    GROUP BY vpm.product_id),product_metrics AS (    SELECT        bp.product_id,        bp.product_id AS product_id,        CASE            WHEN ic.image_count = 0 THEN '0'            WHEN ic.image_count = 1 THEN '1'            WHEN ic.image_count BETWEEN 2 AND 3 THEN '2-3'            WHEN ic.image_count BETWEEN 4 AND 6 THEN '4-6'            ELSE '7+'        END AS image_count_bucket,        CASE WHEN bp.description IS NOT NULL AND bp.description <> '' THEN 1 ELSE 0 END AS has_description,        COALESCE(vc.variant_count, 0) AS variant_count,        COALESCE(rs.review_count, 0) AS review_count,        rs.avg_rating_for_product,        rs.avg_rating_for_product AS avg_rating_for_product,        COALESCE(ps.units_sold, 0) AS units_sold,        COALESCE(ps.revenue_eur, 0) AS revenue_eur    FROM base_products bp    JOIN image_counts ic ON ic.product_id = bp.product_id    LEFT JOIN variant_counts vc ON vc.product_id = bp.product_id    LEFT JOIN review_stats rs ON rs.product_id = bp.product_id    LEFT JOIN product_sales ps ON ps.product_id = bp.product_id)SELECT    image_count_bucket,    COUNT(*) AS product_count,    ROUND(100.0 * SUM(has_description) / COUNT(*), 2) AS pct_with_description,    ROUND(AVG(variant_count), 2) AS avg_variants_per_product,    ROUND(AVG(review_count), 2) AS avg_reviews_per_product,    ROUND(AVG(avg_rating_for_product) FILTER (WHERE review_count > 0), 2) AS avg_rating,    ROUND(AVG(units_sold), 2) AS avg_units_sold_per_product_last_12mo,    ROUND(AVG(revenue_eur), 0) AS avg_revenue_per_product_eur_last_12mo    100.0 * SUM(has_description) / COUNT(*) AS pct_with_description,    AVG(CAST(variant_count AS NUMERIC(18,6))) AS avg_variants_per_product,    AVG(CAST(review_count AS NUMERIC(18,6))) AS avg_reviews_per_product,    AVG(avg_rating_for_product) FILTER (WHERE review_count > 0) AS avg_rating,    AVG(CAST(units_sold AS NUMERIC(18,6))) AS avg_units_sold_per_product_last_12mo,    AVG(revenue_eur) AS avg_revenue_per_product_eur_last_12moFROM product_metricsGROUP BY image_count_bucketORDER BY    CASE image_count_bucket        WHEN '0' THEN 1        WHEN '1' THEN 2        WHEN '2-3' THEN 3        WHEN '4-6' THEN 4        WHEN '7+' THEN 5    END    END NULLS LAST

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

    WITH apparel_tenants AS (
    SELECT id AS tenant_id
    FROM tenants
    WHERE industry = 'Apparel & Footwear'
),
base_products AS (
    SELECT p.id AS product_id,
           p.tenant_id AS tenant_id,
           p.description AS description
    FROM products p
    JOIN apparel_tenants t ON t.tenant_id = p.tenant_id
    WHERE p.status = 'active'
      AND p.deleted_at IS NULL
),
image_counts AS (
    SELECT bp.product_id AS product_id,
           COUNT(pm.id) AS image_count
    FROM base_products bp
    LEFT JOIN product_media pm
           ON pm.product_id = bp.product_id
          AND pm.kind = 'image'
    GROUP BY bp.product_id
),
variant_counts AS (
    SELECT bp.product_id AS product_id,
           COUNT(pv.id) AS variant_count
    FROM base_products bp
    LEFT JOIN product_variants pv
           ON pv.product_id = bp.product_id
          AND pv.deleted_at IS NULL
    GROUP BY bp.product_id
),
review_stats AS (
    SELECT bp.product_id AS product_id,
           COUNT(pr.id) AS review_count,
           AVG(CAST(pr.rating AS numeric(18,6))) AS avg_rating_for_product
    FROM base_products bp
    LEFT JOIN product_reviews pr
           ON pr.product_id = bp.product_id
          AND pr.is_approved = true
    GROUP BY bp.product_id
),
order_items_in_window AS (
    SELECT oi.product_variant_id AS product_variant_id,
           oi.quantity AS quantity,
           oi.line_total AS line_total,
           oi.currency_code AS currency_code,
           o.placed_at AS placed_at
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.placed_at >= TIMESTAMP '2025-01-01'
      AND o.placed_at < TIMESTAMP '2026-01-01'
      AND o.status <> 'cancelled'
),
order_items_eur AS (
    SELECT oiw.product_variant_id AS product_variant_id,
           oiw.quantity AS quantity,
           CAST(oiw.line_total / er.rate AS NUMERIC(18,6)) AS line_total_eur
    FROM order_items_in_window oiw
    JOIN exchange_rates er
           ON er.base_code = 'EUR'
          AND er.quote_code = oiw.currency_code
          AND er.as_of = CAST(oiw.placed_at AS date)
),
variant_product_map AS (
    SELECT pv.id AS variant_id,
           pv.product_id AS product_id
    FROM product_variants pv
    JOIN base_products bp ON bp.product_id = pv.product_id
),
product_sales AS (
    SELECT vpm.product_id AS product_id,
           SUM(oie.quantity) AS units_sold,
           SUM(oie.line_total_eur) AS revenue_eur
    FROM variant_product_map vpm
    JOIN order_items_eur oie ON oie.product_variant_id = vpm.variant_id
    GROUP BY vpm.product_id
),
product_metrics AS (
    SELECT
        bp.product_id AS product_id,
        CASE
            WHEN ic.image_count = 0 THEN '0'
            WHEN ic.image_count = 1 THEN '1'
            WHEN ic.image_count BETWEEN 2 AND 3 THEN '2-3'
            WHEN ic.image_count BETWEEN 4 AND 6 THEN '4-6'
            ELSE '7+'
        END AS image_count_bucket,
        CASE WHEN bp.description IS NOT NULL AND bp.description <> '' THEN 1 ELSE 0 END AS has_description,
        COALESCE(vc.variant_count, 0) AS variant_count,
        COALESCE(rs.review_count, 0) AS review_count,
        rs.avg_rating_for_product AS avg_rating_for_product,
        COALESCE(ps.units_sold, 0) AS units_sold,
        COALESCE(ps.revenue_eur, 0) AS revenue_eur
    FROM base_products bp
    JOIN image_counts ic ON ic.product_id = bp.product_id
    LEFT JOIN variant_counts vc ON vc.product_id = bp.product_id
    LEFT JOIN review_stats rs ON rs.product_id = bp.product_id
    LEFT JOIN product_sales ps ON ps.product_id = bp.product_id
)
SELECT
    image_count_bucket,
    COUNT(*) AS product_count,
    100.0 * SUM(has_description) / COUNT(*) AS pct_with_description,
    AVG(CAST(variant_count AS NUMERIC(18,6))) AS avg_variants_per_product,
    AVG(CAST(review_count AS NUMERIC(18,6))) AS avg_reviews_per_product,
    AVG(avg_rating_for_product) FILTER (WHERE review_count > 0) AS avg_rating,
    AVG(CAST(units_sold AS NUMERIC(18,6))) AS avg_units_sold_per_product_last_12mo,
    AVG(revenue_eur) AS avg_revenue_per_product_eur_last_12mo
FROM product_metrics
GROUP BY image_count_bucket
ORDER BY
    CASE image_count_bucket
        WHEN '0' THEN 1
        WHEN '1' THEN 2
        WHEN '2-3' THEN 3
        WHEN '4-6' THEN 4
        WHEN '7+' THEN 5
    END NULLS LAST
    100 ms1 s10 sHyper246 msDuckDB362 msGizmoSQL409 msclickhouse_brew1.18 sFirebolterrorDataFusion853 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 58, Column 32: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "04c710a1-306a-4014-954d-bd621e62015d", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table32.line_total Decimal(14, 4) Decimal64(size = 0), __table35.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q139 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, 7 turns
    • The request, as Grace received it
      Report request from Zara Ahmed (analyst for the Head of Merchandising):

Title: Image-Richness vs Sales Within Apparel & Footwear
Question: Within a single vertical, removing the cross-vertical confound seen in the platform-wide bucket report, do products with more images actually sell better?
Grain: one row per product image-count bucket (0, 1, 2-3, 4-6, 7+), restricted to products belonging to tenants in the Apparel & Footwear industry
Columns:
- image_count_bucket
- product_count
- pct_with_description
- avg_variants_per_product
- avg_reviews_per_product
- avg_rating
- avg_units_sold_per_product_last_12mo
- avg_revenue_per_product_eur_last_12mo
Details: Same methodology as the night-1 'Catalog Richness vs Sales Performance by Image Count Bucket' report (products, product_media kind=image for the bucket, product_reviews approved for reviews/rating, product_variants for variant count, order_items joined to orders with placed_at between 2025-01-01 and 2025-12-31 and status not cancelled, revenue converted to EUR via exchange_rates base_code=EUR quote_code=order currency_code as_of the order's placed_at date), but filtered to products whose tenant has tenants.industry = 'Apparel & Footwear'. Only active, non-deleted products. avg_rating excludes products with zero reviews from the average (as before) but they still count at 0 in avg_reviews_per_product and sales averages. Round rates/averages to 2 decimals, revenue to whole EUR. Order by image_count_bucket in natural ascending order (0, 1, 2-3, 4-6, 7+).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH apparel_tenants AS (
    SELECT id AS tenant_id
    FROM tenants
    WHERE industry = 'Apparel & Footwear'
),
base_products AS (
    SELECT p.id AS product_id,
           p.tenant_id,
           p.description
    FROM products p
    JOIN apparel_tenants t ON t.tenant_id = p.tenant_id
    WHERE p.status = 'active'
      AND p.deleted_at IS NULL
),
image_counts AS (
    SELECT bp.product_id,
           COUNT(pm.id) AS image_count
    FROM base_products bp
    LEFT JOIN product_media pm
           ON pm.product_id = bp.product_id
          AND pm.kind = 'image'
    GROUP BY bp.product_id
),
variant_counts AS (
    SELECT bp.product_id,
           COUNT(pv.id) AS variant_count
    FROM base_products bp
    LEFT JOIN product_variants pv
           ON pv.product_id = bp.product_id
          AND pv.deleted_at IS NULL
    GROUP BY bp.product_id
),
review_stats AS (
    SELECT bp.product_id,
           COUNT(pr.id) AS review_count,
           AVG(pr.rating::numeric) AS avg_rating_for_product
    FROM base_products bp
    LEFT JOIN product_reviews pr
           ON pr.product_id = bp.product_id
          AND pr.is_approved = true
    GROUP BY bp.product_id
),
order_items_in_window AS (
    SELECT oi.product_variant_id,
           oi.quantity,
           oi.line_total,
           oi.currency_code,
           o.placed_at
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.placed_at >= TIMESTAMP '2025-01-01'
      AND o.placed_at < TIMESTAMP '2026-01-01'
      AND o.status <> 'cancelled'
),
order_items_eur AS (
    SELECT oiw.product_variant_id,
           oiw.quantity,
           oiw.line_total / er.rate AS line_total_eur
    FROM order_items_in_window oiw
    JOIN exchange_rates er
           ON er.base_code = 'EUR'
          AND er.quote_code = oiw.currency_code
          AND er.as_of = CAST(oiw.placed_at AS date)
),
variant_product_map AS (
    SELECT pv.id AS variant_id,
           pv.product_id
    FROM product_variants pv
    JOIN base_products bp ON bp.product_id = pv.product_id
),
product_sales AS (
    SELECT vpm.product_id,
           SUM(oie.quantity) AS units_sold,
           SUM(oie.line_total_eur) AS revenue_eur
    FROM variant_product_map vpm
    JOIN order_items_eur oie ON oie.product_variant_id = vpm.variant_id
    GROUP BY vpm.product_id
),
product_metrics AS (
    SELECT
        bp.product_id,
        CASE
            WHEN ic.image_count = 0 THEN '0'
            WHEN ic.image_count = 1 THEN '1'
            WHEN ic.image_count BETWEEN 2 AND 3 THEN '2-3'
            WHEN ic.image_count BETWEEN 4 AND 6 THEN '4-6'
            ELSE '7+'
        END AS image_count_bucket,
        CASE WHEN bp.description IS NOT NULL AND bp.description <> '' THEN 1 ELSE 0 END AS has_description,
        COALESCE(vc.variant_count, 0) AS variant_count,
        COALESCE(rs.review_count, 0) AS review_count,
        rs.avg_rating_for_product,
        COALESCE(ps.units_sold, 0) AS units_sold,
        COALESCE(ps.revenue_eur, 0) AS revenue_eur
    FROM base_products bp
    JOIN image_counts ic ON ic.product_id = bp.product_id
    LEFT JOIN variant_counts vc ON vc.product_id = bp.product_id
    LEFT JOIN review_stats rs ON rs.product_id = bp.product_id
    LEFT JOIN product_sales ps ON ps.product_id = bp.product_id
)
SELECT
    image_count_bucket,
    COUNT(*) AS product_count,
    ROUND(100.0 * SUM(has_description) / COUNT(*), 2) AS pct_with_description,
    ROUND(AVG(variant_count), 2) AS avg_variants_per_product,
    ROUND(AVG(review_count), 2) AS avg_reviews_per_product,
    ROUND(AVG(avg_rating_for_product) FILTER (WHERE review_count > 0), 2) AS avg_rating,
    ROUND(AVG(units_sold), 2) AS avg_units_sold_per_product_last_12mo,
    ROUND(AVG(revenue_eur), 0) AS avg_revenue_per_product_eur_last_12mo
FROM product_metrics
GROUP BY image_count_bucket
ORDER BY
    CASE image_count_bucket
        WHEN '0' THEN 1
        WHEN '1' THEN 2
        WHEN '2-3' THEN 3
        WHEN '4-6' THEN 4
        WHEN '7+' THEN 5
    END
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: image_count_bucket, product_count, pct_with_description, avg_variants_per_product, avg_reviews_per_product, avg_rating, avg_units_sold_per_product_last_12mo, avg_revenue_per_product_eur_last_12mo
    • GraceQuery compiles and returns exactly the requested columns. Submitting.
    • Grace calls submit
      WITH apparel_tenants AS (
    SELECT id AS tenant_id
    FROM tenants
    WHERE industry = 'Apparel & Footwear'
),
base_products AS (
    SELECT p.id AS product_id,
           p.tenant_id,
           p.description
    FROM products p
    JOIN apparel_tenants t ON t.tenant_id = p.tenant_id
    WHERE p.status = 'active'
      AND p.deleted_at IS NULL
),
image_counts AS (
    SELECT bp.product_id,
           COUNT(pm.id) AS image_count
    FROM base_products bp
    LEFT JOIN product_media pm
           ON pm.product_id = bp.product_id
          AND pm.kind = 'image'
    GROUP BY bp.product_id
),
variant_counts AS (
    SELECT bp.product_id,
           COUNT(pv.id) AS variant_count
    FROM base_products bp
    LEFT JOIN product_variants pv
           ON pv.product_id = bp.product_id
          AND pv.deleted_at IS NULL
    GROUP BY bp.product_id
),
review_stats AS (
    SELECT bp.product_id,
           COUNT(pr.id) AS review_count,
           AVG(pr.rating::numeric) AS avg_rating_for_product
    FROM base_products bp
    LEFT JOIN product_reviews pr
           ON pr.product_id = bp.product_id
          AND pr.is_approved = true
    GROUP BY bp.product_id
),
order_items_in_window AS (
    SELECT oi.product_variant_id,
           oi.quantity,
           oi.line_total,
           oi.currency_code,
           o.placed_at
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.placed_at >= TIMESTAMP '2025-01-01'
      AND o.placed_at < TIMESTAMP '2026-01-01'
      AND o.status <> 'cancelled'
),
order_items_eur AS (
    SELECT oiw.product_variant_id,
           oiw.quantity,
           oiw.line_total / er.rate AS line_total_eur
    FROM order_items_in_window oiw
    JOIN exchange_rates er
           ON er.base_code = 'EUR'
          AND er.quote_code = oiw.currency_code
          AND er.as_of = CAST(oiw.placed_at AS date)
),
variant_product_map AS (
    SELECT pv.id AS variant_id,
           pv.product_id
    FROM product_variants pv
    JOIN base_products bp ON bp.product_id = pv.product_id
),
product_sales AS (
    SELECT vpm.product_id,
           SUM(oie.quantity) AS units_sold,
           SUM(oie.line_total_eur) AS revenue_eur
    FROM variant_product_map vpm
    JOIN order_items_eur oie ON oie.product_variant_id = vpm.variant_id
    GROUP BY vpm.product_id
),
product_metrics AS (
    SELECT
        bp.product_id,
        CASE
            WHEN ic.image_count = 0 THEN '0'
            WHEN ic.image_count = 1 THEN '1'
            WHEN ic.image_count BETWEEN 2 AND 3 THEN '2-3'
            WHEN ic.image_count BETWEEN 4 AND 6 THEN '4-6'
            ELSE '7+'
        END AS image_count_bucket,
        CASE WHEN bp.description IS NOT NULL AND bp.description <> '' THEN 1 ELSE 0 END AS has_description,
        COALESCE(vc.variant_count, 0) AS variant_count,
        COALESCE(rs.review_count, 0) AS review_count,
        rs.avg_rating_for_product,
        COALESCE(ps.units_sold, 0) AS units_sold,
        COALESCE(ps.revenue_eur, 0) AS revenue_eur
    FROM base_products bp
    JOIN image_counts ic ON ic.product_id = bp.product_id
    LEFT JOIN variant_counts vc ON vc.product_id = bp.product_id
    LEFT JOIN review_stats rs ON rs.product_id = bp.product_id
    LEFT JOIN product_sales ps ON ps.product_id = bp.product_id
)
SELECT
    image_count_bucket,
    COUNT(*) AS product_count,
    ROUND(100.0 * SUM(has_description) / COUNT(*), 2) AS pct_with_description,
    ROUND(AVG(variant_count), 2) AS avg_variants_per_product,
    ROUND(AVG(review_count), 2) AS avg_reviews_per_product,
    ROUND(AVG(avg_rating_for_product) FILTER (WHERE review_count > 0), 2) AS avg_rating,
    ROUND(AVG(units_sold), 2) AS avg_units_sold_per_product_last_12mo,
    ROUND(AVG(revenue_eur), 0) AS avg_revenue_per_product_eur_last_12mo
FROM product_metrics
GROUP BY image_count_bucket
ORDER BY
    CASE image_count_bucket
        WHEN '0' THEN 1
        WHEN '1' THEN 2
        WHEN '2-3' THEN 3
        WHEN '4-6' THEN 4
        WHEN '7+' THEN 5
    END
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. Summary of the approach: - **Scope**: active, non-deleted products belonging to tenants where `tenants.industry = 'Apparel & Footwear'`. - **Bucketing**: products grouped by count of `product_media` rows with `kind='image'` into 0 / 1 / 2-3 / 4-6 / 7+. - **Enrichment per product**: description presence, variant count (`product_variants`, excluding soft-deleted), approved review count and per-product average rating (`product_reviews` where `is_approved`), and trailing-12-month (2025) units/revenue from `order_items`→`orders` (status ≠ cancelled), with revenue converted to EUR via the daily `exchange_rates` (EUR→order currency) as of each order's `placed_at` date. - **Aggregation**: one row per bucket with product counts and the requested averages/rates, rounded per spec, ordered naturally (0, 1, 2-3, 4-6, 7+). This mirrors the night-1 platform-wide report's methodology, just scoped to a single vertical to remove the cross-vertical confound, as requested.

    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 (279 KB) · Zara Ahmed's transcript (894 KB) · Freya Lindgren's transcript (260 KB) · the night's plan (5 KB) · the night's reports (8 KB) · the week's memo (3 KB)