Queries · q053 · Head of Growth Marketing · week 1, night 2
“Compared to full-price orders, do discounted orders in 2025 have a healthy order value, basket size, and return/cancellation rate, or do they look worse on the metrics that matter?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 1
Subject: What I need to get oriented this week
Hi Sofie,
Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.
What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.
Thanks for taking this on — let me know if you want me to cut this list down.
Chloé
The analyst · week 1, night 2, request 2 of 3
Two things from last night need explaining before I trust the headline numbers, plus one gap Chloé explicitly asked about that I haven't covered yet. First, 'direct/none' is 57% of 2025 sessions and growing 26% YoY — before I call that normal I want to see whether it's a tracking artifact concentrated in a few big merchants (consistent with the 'handful of large merchants' pattern) or spread evenly. Second, the discount-rate report showed two anomalous months (Jan 53%, Jul 59%) versus a ~35% baseline the rest of the year, with discount size per order flat — meaning more orders got a code, not bigger discounts. I want to know if that's a few tenants running sitewide promos. Third, Chloé specifically asked whether promoted orders 'look healthy, not just cheaper' — I haven't produced that comparison yet, so it goes in tonight regardless of the anomalies.
The engineer · 1 dry run on the empty schema, then the submission
SELECT order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pct FROM ( SELECT case when o.discount_total > 0 then 'discounted' else 'full price' end as order_group, count(*) as orders_count, round(avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end)::numeric, 0) as avg_order_value_eur, round(avg(coalesce(oi.item_qty,0))::numeric, 1) as avg_items_per_order, round(100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*), 1) as return_rate_pct, round(100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*), 1) as cancelled_rate_pct FROM orders o 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 LEFT JOIN (select order_id, sum(quantity) as item_qty from order_items group by order_id) oi on oi.order_id = o.id LEFT JOIN (select distinct order_id from order_returns) ret on ret.order_id = o.id WHERE o.placed_at >= date '2025-01-01' and o.placed_at < date '2026-01-01' GROUP BY case when o.discount_total > 0 then 'discounted' else 'full price' end ) t ORDER BY (case when order_group = 'discounted' then 0 else 1 end)
“Compiles cleanly and returns exactly the requested columns. Submitting.”
“Submitted. The report returns one row each for 'discounted' and 'full price' orders placed in 2025, with order count, EUR-converted average order value, average items per order, return rate %, and cancellation rate % — letting Sofie compare whether discounted orders are healthy or just cheaper.”
As Sofie Nilsen saw it, from the small dataset the agents worked on
order_group | orders_count | avg_order_value_eur | avg_items_per_order | return_rate_pct | cancelled_rate_pct ------------+--------------+---------------------+---------------------+-----------------+------------------- discounted | 8208 | 410 | 6.5 | 8.5 | 1.5 full price | 13985 | 192 | 2.6 | 18.6 | 4.3 (2 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pctFROM ( SELECT case when o.discount_total > 0 then 'discounted' else 'full price' end as order_group, count(*) as orders_count, round(avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end)::numeric, 0) as avg_order_value_eur, round(avg(coalesce(oi.item_qty,0))::numeric, 1) as avg_items_per_order, round(100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*), 1) as return_rate_pct, round(100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*), 1) as cancelled_rate_pct avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end) as avg_order_value_eur, avg(cast(coalesce(oi.item_qty,0) as numeric(18,6))) as avg_items_per_order, 100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*) as return_rate_pct, 100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*) as cancelled_rate_pct FROM orders o 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 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) LEFT JOIN (select order_id, sum(quantity) as item_qty from order_items group by order_id) oi on oi.order_id = o.id LEFT JOIN (select distinct order_id from order_returns) ret on ret.order_id = o.id WHERE o.placed_at >= date '2025-01-01' and o.placed_at < date '2026-01-01' GROUP BY case when o.discount_total > 0 then 'discounted' else 'full price' end) tORDER BY (case when order_group = 'discounted' then 0 else 1 end)ORDER BY (case when order_group = 'discounted' then 0 else 1 end) NULLS LAST
SELECT order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pct FROM ( SELECT case when o.discount_total > 0 then 'discounted' else 'full price' end as order_group, count(*) as orders_count, avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end) as avg_order_value_eur, avg(cast(coalesce(oi.item_qty,0) as numeric(18,6))) as avg_items_per_order, 100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*) as return_rate_pct, 100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*) as cancelled_rate_pct FROM orders o 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) LEFT JOIN (select order_id, sum(quantity) as item_qty from order_items group by order_id) oi on oi.order_id = o.id LEFT JOIN (select distinct order_id from order_returns) ret on ret.order_id = o.id WHERE o.placed_at >= date '2025-01-01' and o.placed_at < date '2026-01-01' GROUP BY case when o.discount_total > 0 then 'discounted' else 'full price' end ) t ORDER BY (case when order_group = 'discounted' then 0 else 1 end) NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Sofie Nilsen (analyst for the Head of Growth Marketing): Title: Do discounted orders look healthy, or just cheaper? Question: Compared to full-price orders, do discounted orders in 2025 have a healthy order value, basket size, and return/cancellation rate, or do they look worse on the metrics that matter? Grain: one row per order group: 'discounted' (discount_total > 0) vs 'full price' (discount_total = 0) Columns: - order_group - 'discounted' or 'full price' - orders_count - count of orders in that group, placed_at in 2025 - avg_order_value_eur - average orders.grand_total converted to EUR, rounded to 0 decimals - avg_items_per_order - average of sum(order_items.quantity) per order for that group, rounded to 1 decimal - return_rate_pct - share of orders in that group with at least one linked order_returns row, *100, rounded to 1 decimal - cancelled_rate_pct - share of orders in that group with status = 'cancelled', *100, rounded to 1 decimal Details: Source: orders (placed_at in 2025-01-01..2025-12-31, all tenants, no status filter so cancellations are visible) split into exactly two groups by discount_total > 0. Join order_items for item counts (sum quantity per order_id, then average across orders in the group). Join order_returns on order_id to flag any return. Convert grand_total to EUR via exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of=date of placed_at; EUR rate=1). Exactly 2 output rows, ordered 'discounted' then 'full price'. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pct FROM ( SELECT case when o.discount_total > 0 then 'discounted' else 'full price' end as order_group, count(*) as orders_count, round(avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end)::numeric, 0) as avg_order_value_eur, round(avg(coalesce(oi.item_qty,0))::numeric, 1) as avg_items_per_order, round(100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*), 1) as return_rate_pct, round(100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*), 1) as cancelled_rate_pct FROM orders o 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 LEFT JOIN (select order_id, sum(quantity) as item_qty from order_items group by order_id) oi on oi.order_id = o.id LEFT JOIN (select distinct order_id from order_returns) ret on ret.order_id = o.id WHERE o.placed_at >= date '2025-01-01' and o.placed_at < date '2026-01-01' GROUP BY case when o.discount_total > 0 then 'discounted' else 'full price' end ) t ORDER BY (case when order_group = 'discounted' then 0 else 1 end)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pct
submitSELECT order_group, orders_count, avg_order_value_eur, avg_items_per_order, return_rate_pct, cancelled_rate_pct FROM ( SELECT case when o.discount_total > 0 then 'discounted' else 'full price' end as order_group, count(*) as orders_count, round(avg(o.grand_total / case when o.currency_code = 'EUR' then 1 else er.rate end)::numeric, 0) as avg_order_value_eur, round(avg(coalesce(oi.item_qty,0))::numeric, 1) as avg_items_per_order, round(100.0 * sum(case when ret.order_id is not null then 1 else 0 end) / count(*), 1) as return_rate_pct, round(100.0 * sum(case when o.status = 'cancelled' then 1 else 0 end) / count(*), 1) as cancelled_rate_pct FROM orders o 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 LEFT JOIN (select order_id, sum(quantity) as item_qty from order_items group by order_id) oi on oi.order_id = o.id LEFT JOIN (select distinct order_id from order_returns) ret on ret.order_id = o.id WHERE o.placed_at >= date '2025-01-01' and o.placed_at < date '2026-01-01' GROUP BY case when o.discount_total > 0 then 'discounted' else 'full price' end ) t ORDER BY (case when order_group = 'discounted' then 0 else 1 end)
Submitted to tonight's batch. Your work on this request is done.