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

Cragsley Outfitters: Rating Trend Over Time

“Has Cragsley Outfitters' rating problem been building over time, or has it always looked this way?”

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 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
Zara Ahmed plans the night
The analyst · week 2, night 1, request 2 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: Rating Trend Over Time
Question
Has Cragsley Outfitters' rating problem been building over time, or has it always looked this way?
One row per
calendar month, 2023-01 through 2025-12, for Cragsley Outfitters only
Columns
- month
- review_count
- avg_rating
- pct_1_2_star
- orders_count
- revenue_eur

Omar writes the SQL

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

Dry run 1 · STATUS: REJECTED

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT date_trunc('month', pr.created_at)::date AS month,
         count(*) AS review_count,
         avg(pr.rating) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT date_trunc('month', o.placed_at)::date AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / er.rate
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month,
  COALESCE(r.review_count, 0) AS review_count,
  ROUND(r.avg_rating::numeric, 2) AS avg_rating,
  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC

Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

WITH months AS (  SELECT DISTINCT month_start AS month  FROM calendar_days  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),tenant AS (  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'),reviews AS (  SELECT date_trunc('month', pr.created_at)::date AS month,         count(*) AS review_count,         avg(pr.rating) AS avg_rating,         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star  FROM product_reviews pr  JOIN tenant t ON pr.tenant_id = t.tenant_id  WHERE pr.is_approved = true  GROUP BY 1),orders_agg AS (  SELECT date_trunc('month', o.placed_at)::date AS month,         count(*) AS orders_count,         sum(           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total                ELSE o.grand_total / er.rate           END         ) AS revenue_eur  FROM orders o  JOIN tenant t ON o.tenant_id = t.tenant_id  LEFT 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.status <> 'cancelled'    AND o.placed_at IS NOT NULL  GROUP BY 1)SELECT  to_char(m.month, 'YYYY-MM') AS month,  strftime(m.month, '%Y-%m') AS month,  COALESCE(r.review_count, 0) AS review_count,  ROUND(r.avg_rating::numeric, 2) AS avg_rating,  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eurFROM months mLEFT JOIN reviews r ON r.month = m.monthLEFT JOIN orders_agg oa ON oa.month = m.monthORDER BY m.month ASC

“Query compiles and produces 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. The report builds off the full 2023-01–2025-12 calendar for Cragsley Outfitters, with zero-filled review/order counts in empty months and NULL rating/percentage where there were no approved reviews that month, so Zara can see clearly whether low ratings are a recent shift or a longstanding pattern.”

The report comes back overnight

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

month   | review_count | avg_rating | pct_1_2_star | orders_count | revenue_eur
--------+--------------+------------+--------------+--------------+------------
2023-01 |            5 |       3.00 |         40.0 |           94 |       25035
2023-02 |           20 |       4.05 |         10.0 |           79 |       18094
2023-03 |           16 |       4.13 |         25.0 |          103 |       24369
2023-04 |           26 |       4.00 |         15.4 |          117 |       26098
2023-05 |           27 |       4.44 |          3.7 |          146 |       30783
2023-06 |           32 |       3.56 |         31.3 |          122 |       21379
2023-07 |           19 |       3.68 |         26.3 |          137 |       27450
2023-08 |           24 |       4.13 |         16.7 |          120 |       20647
2023-09 |           31 |       3.97 |         16.1 |          140 |       27363
2023-10 |           29 |       4.00 |         20.7 |          190 |       41431
2023-11 |           48 |       3.77 |         16.7 |          417 |      101426
2023-12 |          100 |       3.62 |         25.0 |          344 |       76851
2024-01 |           65 |       4.14 |         13.8 |          250 |       53276
2024-02 |           48 |       4.08 |         16.7 |          153 |       40195
2024-03 |           43 |       4.26 |         14.0 |          200 |       50040
2024-04 |           47 |       4.19 |         10.6 |          194 |       44801
2024-05 |           35 |       4.40 |          5.7 |          208 |       45129
2024-06 |           40 |       4.00 |         17.5 |          200 |       38508
2024-07 |           44 |       4.07 |         13.6 |          228 |       39802
2024-08 |           43 |       3.72 |         23.3 |          208 |       39039
2024-09 |           43 |       4.44 |          9.3 |          206 |       43448
2024-10 |           50 |       4.24 |         12.0 |          253 |       52528
2024-11 |           49 |       3.92 |         16.3 |          426 |       91266
2024-12 |          122 |       3.80 |         22.1 |          571 |      111122
2025-01 |           75 |       3.92 |         18.7 |          263 |       54903
2025-02 |           35 |       4.11 |         14.3 |          166 |       33473
2025-03 |           35 |       4.14 |         11.4 |          217 |       44102
2025-04 |           32 |       3.94 |         21.9 |          236 |       47458
2025-05 |           45 |       4.13 |         13.3 |          257 |       47074
2025-06 |           56 |       3.98 |         14.3 |          233 |       39323
2025-07 |           27 |       4.04 |         14.8 |          274 |       41600
2025-08 |           40 |       4.13 |         12.5 |          198 |       37026
2025-09 |           29 |       4.00 |         17.2 |          257 |       45447
2025-10 |           48 |       4.15 |         14.6 |          298 |       63257
2025-11 |           49 |       4.10 |         12.2 |          500 |      112926
2025-12 |          109 |       3.70 |         25.7 |          587 |      129125
(36 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q354Fennel & Flint and Wiesgrund Wohnen: Historical December Pattern, 2023-2025
It builds on:
- q145Poorly-Rated Product Concentration by Merchant
- q138Monthly Review Volume and Rating Trend

It becomes benchmark query q348

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

editAn output alias reused the name of an input column the same block still uses; DataFusion rejects the query, which the standard resolves (docs/RUNNER.md, Dialects: a rewrite that costs nothing on any system goes into the canonical query).
spellingcast (6×)
placeholderyear month
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
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
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 months AS (  SELECT DISTINCT month_start AS month  FROM calendar_days  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),tenant AS (  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'),reviews AS (  SELECT date_trunc('month', pr.created_at)::date AS month,  SELECT CAST(date_trunc('month', pr.created_at) AS date) AS month,         count(*) AS review_count,         avg(pr.rating) AS avg_rating,         avg(cast(pr.rating as numeric(18,6))) AS avg_rating,         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star  FROM product_reviews pr  JOIN tenant t ON pr.tenant_id = t.tenant_id  WHERE pr.is_approved = true  GROUP BY 1),orders_agg AS (  SELECT date_trunc('month', o.placed_at)::date AS month,  SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month,         count(*) AS orders_count,         sum(           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total                ELSE o.grand_total / er.rate                ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6))           END         ) AS revenue_eur  FROM orders o  JOIN tenant t ON o.tenant_id = t.tenant_id  LEFT 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.status <> 'cancelled'    AND o.placed_at IS NOT NULL  GROUP BY 1)SELECT  strftime(m.month, '%Y-%m') AS month,  {{year_month(m.month)}} AS month_label,  COALESCE(r.review_count, 0) AS review_count,  ROUND(r.avg_rating::numeric, 2) AS avg_rating,  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,  r.avg_rating AS avg_rating,  r.pct_1_2_star AS pct_1_2_star,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eur  COALESCE(oa.revenue_eur, 0) AS revenue_eurFROM months mLEFT JOIN reviews r ON r.month = m.monthLEFT JOIN orders_agg oa ON oa.month = m.monthORDER BY m.month ASCORDER BY m.month ASC NULLS LAST

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

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT CAST(date_trunc('month', pr.created_at) AS date) AS month,
         count(*) AS review_count,
         avg(cast(pr.rating as numeric(18,6))) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6))
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month_label,
  COALESCE(r.review_count, 0) AS review_count,
  r.avg_rating AS avg_rating,
  r.pct_1_2_star AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.revenue_eur, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT CAST(date_trunc('month', pr.created_at) AS date) AS month,
         count(*) AS review_count,
         avg(cast(pr.rating as numeric(18,6))) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6))
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month_label,
  COALESCE(r.review_count, 0) AS review_count,
  r.avg_rating AS avg_rating,
  r.pct_1_2_star AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.revenue_eur, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT CAST(date_trunc('month', pr.created_at) AS date) AS month,
         count(*) AS review_count,
         avg(cast(pr.rating as numeric(18,6))) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6))
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  to_char(CAST(m.month AS timestamp), 'YYYY-MM') AS month_label,
  COALESCE(r.review_count, 0) AS review_count,
  r.avg_rating AS avg_rating,
  r.pct_1_2_star AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.revenue_eur, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT CAST(date_trunc('month', pr.created_at) AS date) AS month,
         count(*) AS review_count,
         avg(cast(pr.rating as numeric(18,6))) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6))
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  formatDateTime(m.month, '%Y-%m') AS month_label,
  COALESCE(r.review_count, 0) AS review_count,
  r.avg_rating AS avg_rating,
  r.pct_1_2_star AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.revenue_eur, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC NULLS LAST

Firebolt{ "errors": [ { "description": "Line 24, Column 41: 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": "3d518da0-2273-4005-83e4-a4d087f68f4d", "query_lab
DataFusionrow 1: col 1: "2023-01" vs "YYYY-MM"
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table8.grand_total Decimal(14, 4) Decimal64(size = 0), __table11.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. q348 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, 9 turns

The request, as Omar received it

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

Title: Cragsley Outfitters: Rating Trend Over Time
Question: Has Cragsley Outfitters' rating problem been building over time, or has it always looked this way?
Grain: one row per calendar month, 2023-01 through 2025-12, for Cragsley Outfitters only
Columns:
- month
- review_count
- avg_rating
- pct_1_2_star
- orders_count
- revenue_eur
Details: Filter to tenant = Cragsley Outfitters. review_count/avg_rating/pct_1_2_star computed over approved product_reviews with created_at in that month (same definitions used in the platform-wide Monthly Review Volume and Rating Trend report, so the two can be compared). orders_count = count of Cragsley orders with placed_at in that month and status not cancelled. revenue_eur = sum of grand_total for those orders converted to EUR via exchange_rates as of placed_at date. Build off the full calendar so every month appears even with zero reviews. Round avg_rating to 2 decimals, pct to 1 decimal, revenue to whole EUR. Order by month ascending.

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

Omar calls dry_run

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT date_trunc('month', pr.created_at)::date AS month,
         count(*) AS review_count,
         avg(pr.rating) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT date_trunc('month', o.placed_at)::date AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / er.rate
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month,
  COALESCE(r.review_count, 0) AS review_count,
  ROUND(r.avg_rating::numeric, 2) AS avg_rating,
  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Omar calls dry_run

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT date_trunc('month', pr.created_at)::date AS month,
         count(*) AS review_count,
         avg(pr.rating) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT date_trunc('month', o.placed_at)::date AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / er.rate
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month,
  COALESCE(r.review_count, 0) AS review_count,
  ROUND(r.avg_rating::numeric, 2) AS avg_rating,
  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, review_count, avg_rating, pct_1_2_star, orders_count, revenue_eur

OmarQuery compiles and produces the expected columns. Submitting.

Omar calls submit

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
tenant AS (
  SELECT id AS tenant_id FROM tenants WHERE name = 'Cragsley Outfitters'
),
reviews AS (
  SELECT date_trunc('month', pr.created_at)::date AS month,
         count(*) AS review_count,
         avg(pr.rating) AS avg_rating,
         100.0 * count(*) FILTER (WHERE pr.rating <= 2) / NULLIF(count(*), 0) AS pct_1_2_star
  FROM product_reviews pr
  JOIN tenant t ON pr.tenant_id = t.tenant_id
  WHERE pr.is_approved = true
  GROUP BY 1
),
orders_agg AS (
  SELECT date_trunc('month', o.placed_at)::date AS month,
         count(*) AS orders_count,
         sum(
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / er.rate
           END
         ) AS revenue_eur
  FROM orders o
  JOIN tenant t ON o.tenant_id = t.tenant_id
  LEFT 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.status <> 'cancelled'
    AND o.placed_at IS NOT NULL
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month,
  COALESCE(r.review_count, 0) AS review_count,
  ROUND(r.avg_rating::numeric, 2) AS avg_rating,
  ROUND(r.pct_1_2_star::numeric, 1) AS pct_1_2_star,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.revenue_eur, 0)::numeric, 0) AS revenue_eur
FROM months m
LEFT JOIN reviews r ON r.month = m.month
LEFT JOIN orders_agg oa ON oa.month = m.month
ORDER BY m.month ASC

The warehouse answers

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

OmarSubmitted. The report builds off the full 2023-01–2025-12 calendar for Cragsley Outfitters, with zero-filled review/order counts in empty months and NULL rating/percentage where there were no approved reviews that month, so Zara can see clearly whether low ratings are a recent shift or a longstanding pattern.

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 (273 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)

Cragsley Outfitters: Rating Trend Over Time

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 q348

Everything, as recorded