DB Olympics

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

Cragsley Outfitters: Sales Trajectory of Poor-Rated vs Other Products

“For Cragsley's already-identified poor-rated products, is demand starting to slow down to match the bad reviews, or is it still growing despite them?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 1, request 3 of 3

    Freya's reply raises four follow-up threads: Cragsley's quality concentration, the December rating dip's root cause (especially given COO's Albion Parcel carrier breakdown and CX's WISMO/late-delivery spike in Nov-Dec, which could well be the real explanation rather than a catalog issue), the zero-vs-one-image effect (is it causal or a proxy for something else like product age), and what distinguishes bundle-users descriptively. That's a lot for one week, so tonight I'm spending all 3 slots on Cragsley since it's the most urgent (our #2 merchant, flagged independently by Support Ops as having the worst response times too) and because Freya asked three distinct sub-questions about it that each deserve their own grain: which product categories concentrate the poor ratings, whether the problem is new or longstanding, and whether sales for the poor-rated products are already slowing or still growing despite the bad reviews. I'll tackle the December dip (cross-referencing carrier/shipping data), the image-effect confound, and bundle-user profiling on the remaining three nights.

    Cragsley Outfitters: Sales Trajectory of Poor-Rated vs Other Products
    Question
    For Cragsley's already-identified poor-rated products, is demand starting to slow down to match the bad reviews, or is it still growing despite them?
    One row per
    calendar month, 2023-01 through 2025-12, for Cragsley Outfitters only, split into two fixed product groups
    Columns
    • month
    • poor_tier_units_sold
    • poor_tier_revenue_eur
    • other_tier_units_sold
    • other_tier_revenue_eur

  3. Ines 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_cte AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
poor_tier AS (
  SELECT pr.product_id
  FROM product_reviews pr
  JOIN tenant_cte t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY pr.product_id
  HAVING count(*) >= 5 AND avg(pr.rating) < 3.0
),
other_tier AS (
  SELECT p.id AS product_id
  FROM products p
  JOIN tenant_cte t ON p.tenant_id = t.tenant_id
  WHERE p.status = 'active'
    AND p.id NOT IN (SELECT product_id FROM poor_tier)
),
months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'
),
line_items AS (
  SELECT
    date_trunc('month', o.placed_at)::date AS month,
    pv.product_id AS product_id,
    oi.quantity AS quantity,
    oi.line_total AS line_total,
    oi.currency_code AS currency_code,
    o.placed_at::date AS placed_date
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  JOIN product_variants pv ON oi.product_variant_id = pv.id
  JOIN tenant_cte t ON o.tenant_id = t.tenant_id
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
converted AS (
  SELECT
    li.month,
    li.product_id,
    li.quantity,
    CASE WHEN li.currency_code = 'EUR' THEN li.line_total
         ELSE li.line_total / er.rate END AS revenue_eur
  FROM line_items li
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = li.currency_code AND er.as_of = li.placed_date
),
tiered AS (
  SELECT c.month,
    CASE WHEN c.product_id IN (SELECT product_id FROM poor_tier) THEN 'poor'
         WHEN c.product_id IN (SELECT product_id FROM other_tier) THEN 'other'
         ELSE NULL END AS tier,
    c.quantity,
    c.revenue_eur
  FROM converted c
)
SELECT
  m.month,
  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.quantity END),0) AS poor_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0)) AS poor_tier_revenue_eur,
  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.quantity END),0) AS other_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0)) AS other_tier_revenue_eur
FROM months m
LEFT JOIN tiered t ON t.month = m.month
GROUP BY m.month
ORDER BY m.month

    “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. One note for the record: I rebuilt the poor-tier product list from the stated rule (≥5 approved reviews all-time, average rating <3.0) rather than pulling the prior report's saved list, since that list isn't stored in the warehouse — it should reproduce the same 23 products if the same logic was used originally. Revenue is based on order_items.line_total (post-discount, pre-tax), converted to EUR using the same-day exchange rate, rounded to whole euros.”

  4. The report comes back overnight

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

    month      | poor_tier_units_sold | poor_tier_revenue_eur | other_tier_units_sold | other_tier_revenue_eur
-----------+----------------------+-----------------------+-----------------------+-----------------------
2023-01-01 |                   11 |                 896.0 |                    41 |                 3006.0
2023-02-01 |                   12 |                 943.0 |                    31 |                 1933.0
2023-03-01 |                   26 |                1645.0 |                    50 |                 4224.0
2023-04-01 |                   24 |                2161.0 |                    39 |                 2725.0
2023-05-01 |                   31 |                2362.0 |                    40 |                 2651.0
2023-06-01 |                   27 |                1771.0 |                    47 |                 3332.0
2023-07-01 |                   18 |                1113.0 |                    64 |                 3400.0
2023-08-01 |                   14 |                 980.0 |                    37 |                 2221.0
2023-09-01 |                   27 |                1764.0 |                    56 |                 3424.0
2023-10-01 |                   42 |                3082.0 |                    83 |                 5921.0
2023-11-01 |                  100 |                8259.0 |                   260 |                15934.0
2023-12-01 |                   62 |                5198.0 |                   193 |                11909.0
2024-01-01 |                   54 |                3746.0 |                   151 |                 6976.0
2024-02-01 |                   35 |                2977.0 |                    85 |                 5775.0
2024-03-01 |                   40 |                3863.0 |                   131 |                 9016.0
2024-04-01 |                   37 |                2865.0 |                   152 |                 8955.0
2024-05-01 |                   49 |                3497.0 |                   161 |                 9846.0
2024-06-01 |                   50 |                3335.0 |                   122 |                 7504.0
2024-07-01 |                   42 |                1757.0 |                   202 |                12222.0
2024-08-01 |                   39 |                2250.0 |                   186 |                 9467.0
2024-09-01 |                   43 |                2262.0 |                   198 |                14023.0
2024-10-01 |                   49 |                2994.0 |                   257 |                14804.0
2024-11-01 |                   74 |                4676.0 |                   524 |                34254.0
2024-12-01 |                   92 |                6171.0 |                   653 |                43214.0
2025-01-01 |                   57 |                4234.0 |                   366 |                25020.0
2025-02-01 |                   33 |                2393.0 |                   245 |                16858.0
2025-03-01 |                   63 |                3577.0 |                   323 |                22304.0
2025-04-01 |                   55 |                3866.0 |                   359 |                24717.0
2025-05-01 |                   39 |                2675.0 |                   445 |                27068.0
2025-06-01 |                   38 |                3061.0 |                   386 |                22646.0
2025-07-01 |                   68 |                3687.0 |                   487 |                24439.0
2025-08-01 |                   19 |                1433.0 |                   347 |                22744.0
2025-09-01 |                   31 |                2399.0 |                   557 |                32374.0
2025-10-01 |                   53 |                4338.0 |                   669 |                44473.0
2025-11-01 |                   78 |                7397.0 |                  1187 |                81773.0
2025-12-01 |                   75 |                6177.0 |                  1388 |               100513.0
(36 rows)

  5. What came of it

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

    It builds on:

  6. It becomes benchmark query q349

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

    • spellingcast (2×)
    • 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
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (8×)
    • 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 tenant_cte AS (  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'),poor_tier AS (  SELECT pr.product_id  SELECT pr.product_id AS product_id  FROM product_reviews pr  JOIN tenant_cte t ON pr.tenant_id = t.tenant_id  WHERE pr.is_approved = true  GROUP BY pr.product_id  HAVING count(*) >= 5 AND avg(pr.rating) < 3.0  HAVING count(*) >= 5 AND avg(cast(pr.rating as numeric(18,6))) < 3.0),other_tier AS (  SELECT p.id AS product_id  FROM products p  JOIN tenant_cte t ON p.tenant_id = t.tenant_id  WHERE p.status = 'active'    AND p.id NOT IN (SELECT product_id FROM poor_tier)),months AS (  SELECT DISTINCT month_start AS month  FROM calendar_days  WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'),line_items AS (  SELECT    date_trunc('month', o.placed_at)::date AS month,    CAST(date_trunc('month', o.placed_at) AS date) AS month,    pv.product_id AS product_id,    oi.quantity AS quantity,    oi.line_total AS line_total,    oi.currency_code AS currency_code,    o.placed_at::date AS placed_date    CAST(o.placed_at AS date) AS placed_date  FROM order_items oi  JOIN orders o ON oi.order_id = o.id  JOIN product_variants pv ON oi.product_variant_id = pv.id  JOIN tenant_cte t ON o.tenant_id = t.tenant_id  WHERE o.status <> 'cancelled'    AND o.placed_at >= DATE '2023-01-01'    AND o.placed_at < DATE '2026-01-01'),converted AS (  SELECT    li.month,    li.product_id,    li.quantity,    li.month AS month,    li.product_id AS product_id,    li.quantity AS quantity,    CASE WHEN li.currency_code = 'EUR' THEN li.line_total         ELSE li.line_total / er.rate END AS revenue_eur         ELSE CAST(li.line_total / er.rate AS NUMERIC(18,6)) END AS revenue_eur  FROM line_items li  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = li.currency_code AND er.as_of = li.placed_date),tiered AS (  SELECT c.month,  SELECT c.month AS month,    CASE WHEN c.product_id IN (SELECT product_id FROM poor_tier) THEN 'poor'         WHEN c.product_id IN (SELECT product_id FROM other_tier) THEN 'other'         ELSE NULL END AS tier,    c.quantity,    c.revenue_eur    c.quantity AS quantity,    c.revenue_eur AS revenue_eur  FROM converted c)SELECT  m.month,  m.month AS month,  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.quantity END),0) AS poor_tier_units_sold,  ROUND(COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0)) AS poor_tier_revenue_eur,  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0) AS poor_tier_revenue_eur,  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.quantity END),0) AS other_tier_units_sold,  ROUND(COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0)) AS other_tier_revenue_eur  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0) AS other_tier_revenue_eurFROM months mLEFT JOIN tiered t ON t.month = m.monthGROUP BY m.monthORDER BY m.monthORDER BY m.month NULLS LAST

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

    WITH tenant_cte AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
poor_tier AS (
  SELECT pr.product_id AS product_id
  FROM product_reviews pr
  JOIN tenant_cte t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY pr.product_id
  HAVING count(*) >= 5 AND avg(cast(pr.rating as numeric(18,6))) < 3.0
),
other_tier AS (
  SELECT p.id AS product_id
  FROM products p
  JOIN tenant_cte t ON p.tenant_id = t.tenant_id
  WHERE p.status = 'active'
    AND p.id NOT IN (SELECT product_id FROM poor_tier)
),
months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'
),
line_items AS (
  SELECT
    CAST(date_trunc('month', o.placed_at) AS date) AS month,
    pv.product_id AS product_id,
    oi.quantity AS quantity,
    oi.line_total AS line_total,
    oi.currency_code AS currency_code,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  JOIN product_variants pv ON oi.product_variant_id = pv.id
  JOIN tenant_cte t ON o.tenant_id = t.tenant_id
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
converted AS (
  SELECT
    li.month AS month,
    li.product_id AS product_id,
    li.quantity AS quantity,
    CASE WHEN li.currency_code = 'EUR' THEN li.line_total
         ELSE CAST(li.line_total / er.rate AS NUMERIC(18,6)) END AS revenue_eur
  FROM line_items li
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = li.currency_code AND er.as_of = li.placed_date
),
tiered AS (
  SELECT c.month AS month,
    CASE WHEN c.product_id IN (SELECT product_id FROM poor_tier) THEN 'poor'
         WHEN c.product_id IN (SELECT product_id FROM other_tier) THEN 'other'
         ELSE NULL END AS tier,
    c.quantity AS quantity,
    c.revenue_eur AS revenue_eur
  FROM converted c
)
SELECT
  m.month AS month,
  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.quantity END),0) AS poor_tier_units_sold,
  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0) AS poor_tier_revenue_eur,
  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.quantity END),0) AS other_tier_units_sold,
  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0) AS other_tier_revenue_eur
FROM months m
LEFT JOIN tiered t ON t.month = m.month
GROUP BY m.month
ORDER BY m.month NULLS LAST
    100 ms1 s10 sHyper439 msDuckDB1.26 sGizmoSQL1.18 sclickhouse_brew450 msFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 46, Column 34: 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": "e242f3bb-e58b-4fe2-9fce-66db08d2b5e2", "query_lab
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression InSubquery(InSubquery { expr: Column(Column { relation: Some(Bare { table: "c" }), name: "product_id" }), subquery: <subquery>, negated: false })
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table20.line_total Decimal(14, 4) Decimal64(size = 0), __table26.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q349 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

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

Title: Cragsley Outfitters: Sales Trajectory of Poor-Rated vs Other Products
Question: For Cragsley's already-identified poor-rated products, is demand starting to slow down to match the bad reviews, or is it still growing despite them?
Grain: one row per calendar month, 2023-01 through 2025-12, for Cragsley Outfitters only, split into two fixed product groups
Columns:
- month
- poor_tier_units_sold
- poor_tier_revenue_eur
- other_tier_units_sold
- other_tier_revenue_eur
Details: Define the 'poor tier' as the fixed set of Cragsley products with at least 5 approved reviews (all-time) and an all-time average rating below 3.0 (the same set identified in the Poorly-Rated Product Concentration report, 23 products); 'other tier' is all of Cragsley's remaining active products. For each month, sum order_items.quantity and EUR-converted revenue (via exchange_rates as of placed_at date) from orders placed that month (status not cancelled), separately for each tier. Build off the full calendar so months with zero sales for a tier still show 0. Round revenue to whole EUR. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH tenant_cte AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
poor_tier AS (
  SELECT pr.product_id
  FROM product_reviews pr
  JOIN tenant_cte t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY pr.product_id
  HAVING count(*) >= 5 AND avg(pr.rating) < 3.0
),
other_tier AS (
  SELECT p.id AS product_id
  FROM products p
  JOIN tenant_cte t ON p.tenant_id = t.tenant_id
  WHERE p.status = 'active'
    AND p.id NOT IN (SELECT product_id FROM poor_tier)
),
months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'
),
line_items AS (
  SELECT
    date_trunc('month', o.placed_at)::date AS month,
    pv.product_id AS product_id,
    oi.quantity AS quantity,
    oi.line_total AS line_total,
    oi.currency_code AS currency_code,
    o.placed_at::date AS placed_date
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  JOIN product_variants pv ON oi.product_variant_id = pv.id
  JOIN tenant_cte t ON o.tenant_id = t.tenant_id
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
converted AS (
  SELECT
    li.month,
    li.product_id,
    li.quantity,
    CASE WHEN li.currency_code = 'EUR' THEN li.line_total
         ELSE li.line_total / er.rate END AS revenue_eur
  FROM line_items li
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = li.currency_code AND er.as_of = li.placed_date
),
tiered AS (
  SELECT c.month,
    CASE WHEN c.product_id IN (SELECT product_id FROM poor_tier) THEN 'poor'
         WHEN c.product_id IN (SELECT product_id FROM other_tier) THEN 'other'
         ELSE NULL END AS tier,
    c.quantity,
    c.revenue_eur
  FROM converted c
)
SELECT
  m.month,
  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.quantity END),0) AS poor_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0)) AS poor_tier_revenue_eur,
  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.quantity END),0) AS other_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0)) AS other_tier_revenue_eur
FROM months m
LEFT JOIN tiered t ON t.month = m.month
GROUP BY m.month
ORDER BY m.month
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, poor_tier_units_sold, poor_tier_revenue_eur, other_tier_units_sold, other_tier_revenue_eur
    • InesQuery compiles and returns the expected columns. Submitting.
    • Ines calls submit
      WITH tenant_cte AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
poor_tier AS (
  SELECT pr.product_id
  FROM product_reviews pr
  JOIN tenant_cte t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY pr.product_id
  HAVING count(*) >= 5 AND avg(pr.rating) < 3.0
),
other_tier AS (
  SELECT p.id AS product_id
  FROM products p
  JOIN tenant_cte t ON p.tenant_id = t.tenant_id
  WHERE p.status = 'active'
    AND p.id NOT IN (SELECT product_id FROM poor_tier)
),
months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'
),
line_items AS (
  SELECT
    date_trunc('month', o.placed_at)::date AS month,
    pv.product_id AS product_id,
    oi.quantity AS quantity,
    oi.line_total AS line_total,
    oi.currency_code AS currency_code,
    o.placed_at::date AS placed_date
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  JOIN product_variants pv ON oi.product_variant_id = pv.id
  JOIN tenant_cte t ON o.tenant_id = t.tenant_id
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
converted AS (
  SELECT
    li.month,
    li.product_id,
    li.quantity,
    CASE WHEN li.currency_code = 'EUR' THEN li.line_total
         ELSE li.line_total / er.rate END AS revenue_eur
  FROM line_items li
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = li.currency_code AND er.as_of = li.placed_date
),
tiered AS (
  SELECT c.month,
    CASE WHEN c.product_id IN (SELECT product_id FROM poor_tier) THEN 'poor'
         WHEN c.product_id IN (SELECT product_id FROM other_tier) THEN 'other'
         ELSE NULL END AS tier,
    c.quantity,
    c.revenue_eur
  FROM converted c
)
SELECT
  m.month,
  COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.quantity END),0) AS poor_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='poor' THEN t.revenue_eur END),0)) AS poor_tier_revenue_eur,
  COALESCE(SUM(CASE WHEN t.tier='other' THEN t.quantity END),0) AS other_tier_units_sold,
  ROUND(COALESCE(SUM(CASE WHEN t.tier='other' THEN t.revenue_eur END),0)) AS other_tier_revenue_eur
FROM months m
LEFT JOIN tiered t ON t.month = m.month
GROUP BY m.month
ORDER BY m.month
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. One note for the record: I rebuilt the poor-tier product list from the stated rule (≥5 approved reviews all-time, average rating <3.0) rather than pulling the prior report's saved list, since that list isn't stored in the warehouse — it should reproduce the same 23 products if the same logic was used originally. Revenue is based on `order_items.line_total` (post-discount, pre-tax), converted to EUR using the same-day exchange rate, rounded to whole euros.

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

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