Queries · q147 · Head of Merchandising · week 1, night 4

Catalog Structure: Top Merchants vs Long Tail

“Do our top-revenue merchants' catalogs look structurally different from the long tail in size, categorization, and media richness?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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

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
Zara Ahmed plans the night
The analyst · week 1, night 4, request 3 of 3
Night 3 takeaways: (1) The Home & Living December dip report shows most of the 13 merchants have only 1-4 reviews that month, so most of the apparent 'decline' is single bad reviews swinging a tiny average, not a systemic issue — only Fennel & Flint (14 reviews, 3.14 avg, -0.97 vs baseline) and Wiesgrund Wohnen (12 reviews, 3.5 avg, -0.6) have enough volume to be a credible, actionable signal. (2) The platform-wide worst-rated-products list is dominated by just a handful of merchants repeating over and over: Cragsley Outfitters (our #2 merchant by revenue) accounts for roughly 20 of the 50 worst-rated products, Brennquell Kaffee and Marrowby & Vane also recur heavily, and some of these badly-rated items still sell well (e.g. Aeropress Go Pour-Over Kit at Brennquell: 128 units sold, 2.83 avg rating; Corduroy Linen Shirt at Cragsley: 110 units, 2.86 rating) — meaning poor feedback hasn't yet hurt demand but is a real risk. I want to quantify this concentration properly at the merchant level rather than relying on counting rows in a product list. (3) Collections are already near-universal (most merchants show 65-100% of active products in a collection, with a suspicious clustering around exactly 8 collections per merchant — possibly a template default), so they're not a differentiator worth pushing further. Bundles, by contrast, are almost entirely unused even by our biggest merchants (0% for Cragsley, OfficeHub, Castell, Deskline, Marrowby, Cinder Peak) — this is real white space Freya could push. For this final night before the memo, I'm prioritizing: (A) quantifying the poor-rating concentration per top merchant to turn the anecdote into a rankable list, (B) the still-unanswered 'high sales but few/no reviews' piece of Q3, and (C) the dedicated top-merchants-vs-long-tail structural comparison for Q5, which I haven't produced yet and is one of Freya's five named questions.
Catalog Structure: Top Merchants vs Long Tail
Question
Do our top-revenue merchants' catalogs look structurally different from the long tail in size, categorization, and media richness?
One row per
merchant revenue tier: Top 10, Next 20 (ranks 11-30), and Rest (ranks 31 and below), ranked by revenue_eur_last_12mo
Columns
- tier
- merchant_count
- avg_active_products_per_merchant
- avg_variants_per_product
- avg_categories_per_product
- pct_products_with_primary_image
- avg_media_count_per_product
- pct_active_products_in_a_collection
- revenue_share_pct

Omar 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 tenant_rev as (
  select
    o.tenant_id,
    sum(o.grand_total / er.rate) as revenue_eur
  from orders o
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = o.currency_code
   and er.as_of = o.placed_at::date
  where o.placed_at >= timestamp '2025-01-01'
    and o.placed_at < timestamp '2026-01-01'
    and o.status <> 'cancelled'
  group by o.tenant_id
),
ranked as (
  select
    t.id as tenant_id,
    coalesce(tr.revenue_eur, 0) as revenue_eur,
    row_number() over (order by coalesce(tr.revenue_eur,0) desc, t.id) as rnk
  from tenants t
  left join tenant_rev tr on tr.tenant_id = t.id
  where t.deleted_at is null
),
tiered as (
  select
    tenant_id,
    revenue_eur,
    case
      when rnk <= 10 then 'Top 10'
      when rnk <= 30 then 'Next 20'
      else 'Rest'
    end as tier,
    case
      when rnk <= 10 then 1
      when rnk <= 30 then 2
      else 3
    end as tier_order
  from ranked
),
tier_agg as (
  select tier, tier_order,
    count(distinct tenant_id) as merchant_count,
    sum(revenue_eur) as tier_revenue
  from tiered
  group by tier, tier_order
),
active_products as (
  select p.id as product_id, p.tenant_id
  from products p
  where p.status = 'active' and p.deleted_at is null
),
product_metrics as (
  select
    ap.product_id,
    ap.tenant_id,
    (select count(*) from product_variants pv
       where pv.product_id = ap.product_id and pv.is_active = true and pv.deleted_at is null) as variant_count,
    (select count(*) from product_categories pc
       where pc.product_id = ap.product_id) as category_count,
    exists (select 1 from product_media pm
       where pm.product_id = ap.product_id and pm.is_primary = true) as has_primary_image,
    (select count(*) from product_media pm2
       where pm2.product_id = ap.product_id) as media_count,
    exists (
       select 1 from collection_products cp
       join collections c on c.id = cp.collection_id and c.deleted_at is null
       where cp.product_id = ap.product_id
    ) as in_collection
  from active_products ap
),
product_agg as (
  select ti.tier,
    count(pm.product_id) as active_products,
    avg(pm.variant_count) as avg_variants,
    avg(pm.category_count) as avg_categories,
    avg(case when pm.has_primary_image then 1.0 else 0.0 end) as pct_primary_image,
    avg(pm.media_count) as avg_media,
    avg(case when pm.in_collection then 1.0 else 0.0 end) as pct_in_collection
  from tiered ti
  left join product_metrics pm on pm.tenant_id = ti.tenant_id
  group by ti.tier
)
select
  ta.tier,
  ta.merchant_count,
  round(pa.active_products::numeric / ta.merchant_count, 2) as avg_active_products_per_merchant,
  round(pa.avg_variants::numeric, 2) as avg_variants_per_product,
  round(pa.avg_categories::numeric, 2) as avg_categories_per_product,
  round((100.0*pa.pct_primary_image)::numeric, 1) as pct_products_with_primary_image,
  round(pa.avg_media::numeric, 2) as avg_media_count_per_product,
  round((100.0*pa.pct_in_collection)::numeric, 1) as pct_active_products_in_a_collection,
  round((100.0*ta.tier_revenue / sum(ta.tier_revenue) over ())::numeric, 1) as revenue_share_pct
from tier_agg ta
join product_agg pa on pa.tier = ta.tier
order by ta.tier_order

“Query compiles and returns the expected 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: - Tiering: ranked all non-deleted tenants by revenue_eur_last_12mo (orders placed in 2025, non-cancelled, converted to EUR via daily exchange_rates), then split into Top 10 / Next 20 / Rest by rank. - Catalog metrics: built a per-product metrics layer (variant count, category count, primary-image flag, media count, collection membership) scoped to active, non-deleted products, then pooled those at the tier level — so "per product" columns reflect all qualifying products in the tier, while avg_active_products_per_merchant divides the tier's total active products by its merchant count. - Revenue share: each tier's summed EUR revenue over the platform total, rounded to 1 decimal; other averages rounded to 2 decimals, percentages to 1 decimal, ordered Top 10 → Next 20 → Rest.”

The report comes back overnight

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

tier    | merchant_count | avg_active_products_per_merchant | avg_variants_per_product | avg_categories_per_product | pct_products_with_primary_image | avg_media_count_per_product | pct_active_products_in_a_collection | revenue_share_pct
--------+----------------+----------------------------------+--------------------------+----------------------------+---------------------------------+-----------------------------+-------------------------------------+------------------
Top 10  |             10 |                            110.3 |                     8.21 |                       1.96 |                            99.4 |                        3.49 |                                88.6 |              87.0
Next 20 |             20 |                             73.3 |                     4.89 |                       1.93 |                            98.8 |                        3.39 |                                87.9 |              10.7
Rest    |             81 |                            66.17 |                     4.79 |                       1.91 |                            99.0 |                        3.48 |                                91.1 |               2.2
(3 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q136Merchant Catalog Profile
- q144Collections and Bundles Adoption by Merchant

It becomes benchmark query q147

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

spellingcast (8×)
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
numericsdocs/NUMERICS.md: divisor guarded with NULLIF(..., 0); it is zero when only part of the data is loaded (an empty period), where engines raise an error or return inf/nan
numericsdocs/NUMERICS.md: divisor guarded with NULLIF(..., 0); it is zero when only part of the data is loaded (an empty period), where engines raise an error or return inf/nan
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
made explicita selected t.col gets AS col, its standard name made explicit (7×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

with tenant_rev as (  select    o.tenant_id,    sum(o.grand_total / er.rate) as revenue_eur    o.tenant_id AS tenant_id,    sum(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) as revenue_eur  from orders o  join exchange_rates er    on er.base_code = 'EUR'   and er.quote_code = o.currency_code   and er.as_of = o.placed_at::date   and er.as_of = CAST(o.placed_at AS date)  where o.placed_at >= timestamp '2025-01-01'    and o.placed_at < timestamp '2026-01-01'    and o.status <> 'cancelled'  group by o.tenant_id),ranked as (  select    t.id as tenant_id,    coalesce(tr.revenue_eur, 0) as revenue_eur,    row_number() over (order by coalesce(tr.revenue_eur,0) desc, t.id) as rnk    row_number() over (order by coalesce(tr.revenue_eur,0) desc NULLS FIRST, t.id NULLS LAST) as rnk  from tenants t  left join tenant_rev tr on tr.tenant_id = t.id  where t.deleted_at is null),tiered as (  select    tenant_id,    revenue_eur,    case      when rnk <= 10 then 'Top 10'      when rnk <= 30 then 'Next 20'      else 'Rest'    end as tier,    case      when rnk <= 10 then 1      when rnk <= 30 then 2      else 3    end as tier_order  from ranked),tier_agg as (  select tier, tier_order,    count(distinct tenant_id) as merchant_count,    sum(revenue_eur) as tier_revenue  from tiered  group by tier, tier_order),active_products as (  select p.id as product_id, p.tenant_id  select p.id as product_id, p.tenant_id AS tenant_id  from products p  where p.status = 'active' and p.deleted_at is null),product_metrics as (  select    ap.product_id,    ap.tenant_id,    ap.product_id AS product_id,    ap.tenant_id AS tenant_id,    (select count(*) from product_variants pv       where pv.product_id = ap.product_id and pv.is_active = true and pv.deleted_at is null) as variant_count,    (select count(*) from product_categories pc       where pc.product_id = ap.product_id) as category_count,    exists (select 1 from product_media pm       where pm.product_id = ap.product_id and pm.is_primary = true) as has_primary_image,    (select count(*) from product_media pm2       where pm2.product_id = ap.product_id) as media_count,    exists (       select 1 from collection_products cp       join collections c on c.id = cp.collection_id and c.deleted_at is null       where cp.product_id = ap.product_id    ) as in_collection  from active_products ap),product_agg as (  select ti.tier,  select ti.tier AS tier,    count(pm.product_id) as active_products,    avg(pm.variant_count) as avg_variants,    avg(pm.category_count) as avg_categories,    avg(cast(pm.variant_count as numeric(18,6))) as avg_variants,    avg(cast(pm.category_count as numeric(18,6))) as avg_categories,    avg(case when pm.has_primary_image then 1.0 else 0.0 end) as pct_primary_image,    avg(pm.media_count) as avg_media,    avg(cast(pm.media_count as numeric(18,6))) as avg_media,    avg(case when pm.in_collection then 1.0 else 0.0 end) as pct_in_collection  from tiered ti  left join product_metrics pm on pm.tenant_id = ti.tenant_id  group by ti.tier)select  ta.tier,  ta.merchant_count,  round(pa.active_products::numeric / ta.merchant_count, 2) as avg_active_products_per_merchant,  round(pa.avg_variants::numeric, 2) as avg_variants_per_product,  round(pa.avg_categories::numeric, 2) as avg_categories_per_product,  round((100.0*pa.pct_primary_image)::numeric, 1) as pct_products_with_primary_image,  round(pa.avg_media::numeric, 2) as avg_media_count_per_product,  round((100.0*pa.pct_in_collection)::numeric, 1) as pct_active_products_in_a_collection,  round((100.0*ta.tier_revenue / sum(ta.tier_revenue) over ())::numeric, 1) as revenue_share_pct  ta.tier AS tier,  ta.merchant_count AS merchant_count,  CAST(pa.active_products AS numeric(18,6)) / NULLIF(ta.merchant_count, 0) as avg_active_products_per_merchant,  pa.avg_variants as avg_variants_per_product,  pa.avg_categories as avg_categories_per_product,  (100.0*pa.pct_primary_image) as pct_products_with_primary_image,  pa.avg_media as avg_media_count_per_product,  (100.0*pa.pct_in_collection) as pct_active_products_in_a_collection,  (100.0*ta.tier_revenue / NULLIF(sum(ta.tier_revenue) over (), 0)) as revenue_share_pctfrom tier_agg tajoin product_agg pa on pa.tier = ta.tierorder by ta.tier_orderorder by ta.tier_order NULLS LAST

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

with tenant_rev as (
  select
    o.tenant_id AS tenant_id,
    sum(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) as revenue_eur
  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 >= timestamp '2025-01-01'
    and o.placed_at < timestamp '2026-01-01'
    and o.status <> 'cancelled'
  group by o.tenant_id
),
ranked as (
  select
    t.id as tenant_id,
    coalesce(tr.revenue_eur, 0) as revenue_eur,
    row_number() over (order by coalesce(tr.revenue_eur,0) desc NULLS FIRST, t.id NULLS LAST) as rnk
  from tenants t
  left join tenant_rev tr on tr.tenant_id = t.id
  where t.deleted_at is null
),
tiered as (
  select
    tenant_id,
    revenue_eur,
    case
      when rnk <= 10 then 'Top 10'
      when rnk <= 30 then 'Next 20'
      else 'Rest'
    end as tier,
    case
      when rnk <= 10 then 1
      when rnk <= 30 then 2
      else 3
    end as tier_order
  from ranked
),
tier_agg as (
  select tier, tier_order,
    count(distinct tenant_id) as merchant_count,
    sum(revenue_eur) as tier_revenue
  from tiered
  group by tier, tier_order
),
active_products as (
  select p.id as product_id, p.tenant_id AS tenant_id
  from products p
  where p.status = 'active' and p.deleted_at is null
),
product_metrics as (
  select
    ap.product_id AS product_id,
    ap.tenant_id AS tenant_id,
    (select count(*) from product_variants pv
       where pv.product_id = ap.product_id and pv.is_active = true and pv.deleted_at is null) as variant_count,
    (select count(*) from product_categories pc
       where pc.product_id = ap.product_id) as category_count,
    exists (select 1 from product_media pm
       where pm.product_id = ap.product_id and pm.is_primary = true) as has_primary_image,
    (select count(*) from product_media pm2
       where pm2.product_id = ap.product_id) as media_count,
    exists (
       select 1 from collection_products cp
       join collections c on c.id = cp.collection_id and c.deleted_at is null
       where cp.product_id = ap.product_id
    ) as in_collection
  from active_products ap
),
product_agg as (
  select ti.tier AS tier,
    count(pm.product_id) as active_products,
    avg(cast(pm.variant_count as numeric(18,6))) as avg_variants,
    avg(cast(pm.category_count as numeric(18,6))) as avg_categories,
    avg(case when pm.has_primary_image then 1.0 else 0.0 end) as pct_primary_image,
    avg(cast(pm.media_count as numeric(18,6))) as avg_media,
    avg(case when pm.in_collection then 1.0 else 0.0 end) as pct_in_collection
  from tiered ti
  left join product_metrics pm on pm.tenant_id = ti.tenant_id
  group by ti.tier
)
select
  ta.tier AS tier,
  ta.merchant_count AS merchant_count,
  CAST(pa.active_products AS numeric(18,6)) / NULLIF(ta.merchant_count, 0) as avg_active_products_per_merchant,
  pa.avg_variants as avg_variants_per_product,
  pa.avg_categories as avg_categories_per_product,
  (100.0*pa.pct_primary_image) as pct_products_with_primary_image,
  pa.avg_media as avg_media_count_per_product,
  (100.0*pa.pct_in_collection) as pct_active_products_in_a_collection,
  (100.0*ta.tier_revenue / NULLIF(sum(ta.tier_revenue) over (), 0)) as revenue_share_pct
from tier_agg ta
join product_agg pa on pa.tier = ta.tier
order by ta.tier_order NULLS LAST

Firebolt{ "errors": [ { "description": "Line 4, Column 28: 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": "52ec2fcf-58f4-4b06-b2c6-df31772b576f", "query_labe
DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table6.grand_total Decimal(14, 4) Decimal64(size = 0), __table7.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

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

Omar's whole session, 7 turns

The request, as Omar received it

Report request from Zara Ahmed (analyst for the Head of Merchandising):

Title: Catalog Structure: Top Merchants vs Long Tail
Question: Do our top-revenue merchants' catalogs look structurally different from the long tail in size, categorization, and media richness?
Grain: one row per merchant revenue tier: Top 10, Next 20 (ranks 11-30), and Rest (ranks 31 and below), ranked by revenue_eur_last_12mo
Columns:
- tier
- merchant_count
- avg_active_products_per_merchant
- avg_variants_per_product
- avg_categories_per_product
- pct_products_with_primary_image
- avg_media_count_per_product
- pct_active_products_in_a_collection
- revenue_share_pct
Details: Rank all non-deleted tenants by the same revenue_eur_last_12mo definition used in the Merchant Catalog Profile report (orders placed 2025-01-01 to 2025-12-31, status not cancelled, EUR conversion via exchange_rates as of placed_at date), then bucket into tiers: rank 1-10 = 'Top 10', rank 11-30 = 'Next 20', rank 31+ = 'Rest'. avg_active_products_per_merchant and avg_variants_per_product use active, non-deleted products/variants as in the Merchant Catalog Profile report. avg_categories_per_product = average count of product_categories rows per active product (categorization depth, not just the single primary_category_id). pct_products_with_primary_image = share of active products with a product_media row where is_primary = true. avg_media_count_per_product = average count of all product_media rows (any kind) per active product. pct_active_products_in_a_collection = share of active products appearing in at least one collection_products row pointing to a non-deleted collection, as in the Collections and Bundles Adoption report. revenue_share_pct = that tier's total revenue_eur_last_12mo divided by the platform total, rounded to 1 decimal. Round other averages to 2 decimals, percentages to 1 decimal. Order rows as Top 10, Next 20, Rest.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Omar calls dry_run

with tenant_rev as (
  select
    o.tenant_id,
    sum(o.grand_total / er.rate) as revenue_eur
  from orders o
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = o.currency_code
   and er.as_of = o.placed_at::date
  where o.placed_at >= timestamp '2025-01-01'
    and o.placed_at < timestamp '2026-01-01'
    and o.status <> 'cancelled'
  group by o.tenant_id
),
ranked as (
  select
    t.id as tenant_id,
    coalesce(tr.revenue_eur, 0) as revenue_eur,
    row_number() over (order by coalesce(tr.revenue_eur,0) desc, t.id) as rnk
  from tenants t
  left join tenant_rev tr on tr.tenant_id = t.id
  where t.deleted_at is null
),
tiered as (
  select
    tenant_id,
    revenue_eur,
    case
      when rnk <= 10 then 'Top 10'
      when rnk <= 30 then 'Next 20'
      else 'Rest'
    end as tier,
    case
      when rnk <= 10 then 1
      when rnk <= 30 then 2
      else 3
    end as tier_order
  from ranked
),
tier_agg as (
  select tier, tier_order,
    count(distinct tenant_id) as merchant_count,
    sum(revenue_eur) as tier_revenue
  from tiered
  group by tier, tier_order
),
active_products as (
  select p.id as product_id, p.tenant_id
  from products p
  where p.status = 'active' and p.deleted_at is null
),
product_metrics as (
  select
    ap.product_id,
    ap.tenant_id,
    (select count(*) from product_variants pv
       where pv.product_id = ap.product_id and pv.is_active = true and pv.deleted_at is null) as variant_count,
    (select count(*) from product_categories pc
       where pc.product_id = ap.product_id) as category_count,
    exists (select 1 from product_media pm
       where pm.product_id = ap.product_id and pm.is_primary = true) as has_primary_image,
    (select count(*) from product_media pm2
       where pm2.product_id = ap.product_id) as media_count,
    exists (
       select 1 from collection_products cp
       join collections c on c.id = cp.collection_id and c.deleted_at is null
       where cp.product_id = ap.product_id
    ) as in_collection
  from active_products ap
),
product_agg as (
  select ti.tier,
    count(pm.product_id) as active_products,
    avg(pm.variant_count) as avg_variants,
    avg(pm.category_count) as avg_categories,
    avg(case when pm.has_primary_image then 1.0 else 0.0 end) as pct_primary_image,
    avg(pm.media_count) as avg_media,
    avg(case when pm.in_collection then 1.0 else 0.0 end) as pct_in_collection
  from tiered ti
  left join product_metrics pm on pm.tenant_id = ti.tenant_id
  group by ti.tier
)
select
  ta.tier,
  ta.merchant_count,
  round(pa.active_products::numeric / ta.merchant_count, 2) as avg_active_products_per_merchant,
  round(pa.avg_variants::numeric, 2) as avg_variants_per_product,
  round(pa.avg_categories::numeric, 2) as avg_categories_per_product,
  round((100.0*pa.pct_primary_image)::numeric, 1) as pct_products_with_primary_image,
  round(pa.avg_media::numeric, 2) as avg_media_count_per_product,
  round((100.0*pa.pct_in_collection)::numeric, 1) as pct_active_products_in_a_collection,
  round((100.0*ta.tier_revenue / sum(ta.tier_revenue) over ())::numeric, 1) as revenue_share_pct
from tier_agg ta
join product_agg pa on pa.tier = ta.tier
order by ta.tier_order

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tier, merchant_count, avg_active_products_per_merchant, avg_variants_per_product, avg_categories_per_product, pct_products_with_primary_image, avg_media_count_per_product, pct_active_products_in_a_collection, revenue_share_pct

OmarQuery compiles and returns the expected columns. Submitting.

Omar calls submit

with tenant_rev as (
  select
    o.tenant_id,
    sum(o.grand_total / er.rate) as revenue_eur
  from orders o
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = o.currency_code
   and er.as_of = o.placed_at::date
  where o.placed_at >= timestamp '2025-01-01'
    and o.placed_at < timestamp '2026-01-01'
    and o.status <> 'cancelled'
  group by o.tenant_id
),
ranked as (
  select
    t.id as tenant_id,
    coalesce(tr.revenue_eur, 0) as revenue_eur,
    row_number() over (order by coalesce(tr.revenue_eur,0) desc, t.id) as rnk
  from tenants t
  left join tenant_rev tr on tr.tenant_id = t.id
  where t.deleted_at is null
),
tiered as (
  select
    tenant_id,
    revenue_eur,
    case
      when rnk <= 10 then 'Top 10'
      when rnk <= 30 then 'Next 20'
      else 'Rest'
    end as tier,
    case
      when rnk <= 10 then 1
      when rnk <= 30 then 2
      else 3
    end as tier_order
  from ranked
),
tier_agg as (
  select tier, tier_order,
    count(distinct tenant_id) as merchant_count,
    sum(revenue_eur) as tier_revenue
  from tiered
  group by tier, tier_order
),
active_products as (
  select p.id as product_id, p.tenant_id
  from products p
  where p.status = 'active' and p.deleted_at is null
),
product_metrics as (
  select
    ap.product_id,
    ap.tenant_id,
    (select count(*) from product_variants pv
       where pv.product_id = ap.product_id and pv.is_active = true and pv.deleted_at is null) as variant_count,
    (select count(*) from product_categories pc
       where pc.product_id = ap.product_id) as category_count,
    exists (select 1 from product_media pm
       where pm.product_id = ap.product_id and pm.is_primary = true) as has_primary_image,
    (select count(*) from product_media pm2
       where pm2.product_id = ap.product_id) as media_count,
    exists (
       select 1 from collection_products cp
       join collections c on c.id = cp.collection_id and c.deleted_at is null
       where cp.product_id = ap.product_id
    ) as in_collection
  from active_products ap
),
product_agg as (
  select ti.tier,
    count(pm.product_id) as active_products,
    avg(pm.variant_count) as avg_variants,
    avg(pm.category_count) as avg_categories,
    avg(case when pm.has_primary_image then 1.0 else 0.0 end) as pct_primary_image,
    avg(pm.media_count) as avg_media,
    avg(case when pm.in_collection then 1.0 else 0.0 end) as pct_in_collection
  from tiered ti
  left join product_metrics pm on pm.tenant_id = ti.tenant_id
  group by ti.tier
)
select
  ta.tier,
  ta.merchant_count,
  round(pa.active_products::numeric / ta.merchant_count, 2) as avg_active_products_per_merchant,
  round(pa.avg_variants::numeric, 2) as avg_variants_per_product,
  round(pa.avg_categories::numeric, 2) as avg_categories_per_product,
  round((100.0*pa.pct_primary_image)::numeric, 1) as pct_products_with_primary_image,
  round(pa.avg_media::numeric, 2) as avg_media_count_per_product,
  round((100.0*pa.pct_in_collection)::numeric, 1) as pct_active_products_in_a_collection,
  round((100.0*ta.tier_revenue / sum(ta.tier_revenue) over ())::numeric, 1) as revenue_share_pct
from tier_agg ta
join product_agg pa on pa.tier = ta.tier
order by ta.tier_order

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

OmarSubmitted. Summary of the approach: - **Tiering**: ranked all non-deleted tenants by `revenue_eur_last_12mo` (orders placed in 2025, non-cancelled, converted to EUR via daily `exchange_rates`), then split into Top 10 / Next 20 / Rest by rank. - **Catalog metrics**: built a per-product metrics layer (variant count, category count, primary-image flag, media count, collection membership) scoped to active, non-deleted products, then pooled those at the tier level — so "per product" columns reflect all qualifying products in the tier, while `avg_active_products_per_merchant` divides the tier's total active products by its merchant count. - **Revenue share**: each tier's summed EUR revenue over the platform total, rounded to 1 decimal; other averages rounded to 2 decimals, percentages to 1 decimal, ordered Top 10 → Next 20 → Rest.

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

Omar's transcript (287 KB) · Zara Ahmed's transcript (894 KB) · Freya Lindgren's transcript (260 KB) · the night's plan (6 KB) · the night's reports (19 KB) · the week's memo (3 KB)

Catalog Structure: Top Merchants vs Long Tail

Freya Lindgren writes to Zara Ahmed

Zara Ahmed plans the night

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q147

Everything, as recorded