Queries · q042 · VP Customer Experience · week 1, night 2
“How often do shoppers end up requesting a return/refund, and how long does that process take to resolve, and is it changing over time?”
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: Where I'd like to start — support & shopper experience
Hi Mateo,
Good to have you on this with me. I'm still building my map of the business, so I want to keep this week broad — I'd rather get a solid first read on a few big questions than a deep dive on one. Here's what's on my mind, roughly in priority order:
What I need these for: I'm meeting merchant success and the exec team soon and want a credible, numbers-backed view of shopper experience — not just war stories. I also want to start spotting the two or three things that, if we fixed them, would move the needle most broadly across merchants, rather than chasing individual complaints.
Given the slot constraints, let's prioritize #1 through #3 this week if we can't fit all five — those feel most urgent to me. Happy to talk through any of this before you lock in requests.
Thanks, Daniel
The analyst · week 1, night 2, request 3 of 3
Reading report 1: year-end (Nov/Dec) spikes in volume, response and resolution time repeat every year (2023, 2024, 2025) — that looks like seasonal/holiday load and is somewhat expected, though worth noting for the memo. What's NOT seasonal is the 2025-05/2025-06 spike (resolution hours jump to 52.7/87.2, response to 26.5/45.1, breach rate to 36.9/45.6%) — nothing comparable appears in 2023 or 2024 mid-year, so I want to find the cause rather than assume it's noise. Report 3 shows delivery + where-is-my-order tags are already >65% of tagged tickets in the last 6 months and growing share while complaint/return/product-question shrink in share — that's the platform-level theme Daniel asked about in priority #2, and it may also explain the resolution-time trend in #1 if delivery tickets take longer to resolve and are concentrated in the spike months. Tonight I'm chasing both of these (merchant concentration of the May-Jun spike, and whether delivery-tagged tickets specifically explain slow/volatile resolution times), plus opening priority #3 (returns/refunds) with a foundational trend report so we have something in hand for that question this week. I don't need a new slot for a plain YoY same-month pivot — I can build that directly off report 1's numbers for the memo.
The engineer · 2 dry runs on the empty schema, then the submission
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month)
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pctFROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed (SELECT strftime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) omFULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, (SELECT strftime(date_trunc('month', requested_at), '%Y-%m') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rmON om.month = rm.monthORDER BY COALESCE(om.month, rm.month)
“Query compiles with the expected 8 columns. Submitting.”
“Submitted. The report gives one row per month (2023-01 through 2025-12) with orders placed, returns requested, the approximate return rate, refunded-return counts, average/median days from request to refund, and the rejection rate — built as independent monthly aggregates over orders and order_returns, joined on month.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
month | orders_placed | returns_requested | return_rate_pct | returns_refunded | avg_days_requested_to_refunded | median_days_requested_to_refunded | returns_rejected_pct --------+---------------+-------------------+-----------------+------------------+--------------------------------+-----------------------------------+--------------------- 2023-01 | 518 | 53 | 10.2 | 51 | 8.7 | 8.7 | 3.8 2023-02 | 546 | 90 | 16.5 | 88 | 8.6 | 8.8 | 2.2 2023-03 | 638 | 120 | 18.8 | 116 | 8.0 | 7.8 | 3.3 2023-04 | 677 | 133 | 19.6 | 131 | 7.8 | 7.7 | 1.5 2023-05 | 804 | 104 | 12.9 | 102 | 8.0 | 8.1 | 1.9 2023-06 | 746 | 124 | 16.6 | 120 | 8.0 | 7.9 | 3.2 2023-07 | 789 | 138 | 17.5 | 131 | 7.7 | 7.8 | 5.1 2023-08 | 842 | 123 | 14.6 | 118 | 7.7 | 7.6 | 4.1 2023-09 | 902 | 136 | 15.1 | 134 | 7.5 | 7.5 | 1.5 2023-10 | 1081 | 139 | 12.9 | 132 | 7.8 | 7.9 | 5.0 2023-11 | 1845 | 214 | 11.6 | 203 | 8.1 | 8.0 | 5.1 2023-12 | 1605 | 491 | 30.6 | 460 | 7.9 | 7.7 | 6.3 2024-01 | 1324 | 262 | 19.8 | 250 | 7.8 | 7.7 | 4.6 2024-02 | 1108 | 165 | 14.9 | 156 | 8.3 | 8.5 | 5.5 2024-03 | 1201 | 143 | 11.9 | 136 | 8.0 | 7.9 | 4.9 2024-04 | 1238 | 173 | 14.0 | 169 | 8.3 | 8.4 | 2.3 2024-05 | 1356 | 190 | 14.0 | 178 | 7.9 | 7.8 | 6.3 2024-06 | 1307 | 193 | 14.8 | 186 | 7.8 | 7.7 | 3.6 2024-07 | 1377 | 233 | 16.9 | 227 | 7.9 | 8.2 | 2.6 2024-08 | 1316 | 204 | 15.5 | 191 | 8.0 | 7.9 | 6.4 2024-09 | 1353 | 192 | 14.2 | 181 | 8.0 | 8.0 | 5.7 2024-10 | 1499 | 196 | 13.1 | 185 | 7.7 | 7.6 | 5.6 2024-11 | 2130 | 267 | 12.5 | 260 | 7.8 | 7.8 | 2.6 2024-12 | 2644 | 643 | 24.3 | 615 | 7.9 | 7.9 | 4.4 2025-01 | 1827 | 349 | 19.1 | 337 | 7.8 | 7.7 | 3.4 2025-02 | 1400 | 176 | 12.6 | 170 | 7.7 | 7.6 | 3.4 2025-03 | 1638 | 187 | 11.4 | 177 | 7.8 | 7.9 | 5.3 2025-04 | 1568 | 230 | 14.7 | 217 | 7.7 | 7.6 | 5.7 2025-05 | 1691 | 252 | 14.9 | 244 | 7.9 | 7.9 | 3.2 2025-06 | 1509 | 254 | 16.8 | 243 | 7.8 | 7.8 | 4.3 2025-07 | 1648 | 243 | 14.7 | 231 | 7.8 | 8.0 | 4.9 2025-08 | 1564 | 217 | 13.9 | 209 | 8.0 | 8.2 | 3.7 2025-09 | 1712 | 252 | 14.7 | 238 | 7.6 | 7.6 | 5.6 2025-10 | 1879 | 270 | 14.4 | 257 | 7.9 | 7.8 | 4.8 2025-11 | 2645 | 331 | 12.5 | 318 | 8.0 | 8.0 | 3.9 2025-12 | 3112 | 702 | 22.6 | 524 | 7.7 | 7.6 | 3.3 (36 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pct om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pctFROM (SELECT strftime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed (SELECT {{year_month(date_trunc('month', placed_at))}} AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) omFULL OUTER JOIN (SELECT strftime(date_trunc('month', requested_at), '%Y-%m') AS month, (SELECT {{year_month(date_trunc('month', requested_at))}} AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded AVG({{seconds_between(requested_at, refunded_at)}}/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, {{percentile_cont_filter(0.5, {{seconds_between(requested_at, refunded_at)}}/86400, status = 'refunded' AND refunded_at IS NOT NULL)}} AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rmON om.month = rm.monthORDER BY COALESCE(om.month, rm.month)ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT strftime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT strftime(date_trunc('month', requested_at), '%Y-%m') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN status = 'refunded' AND refunded_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400 END) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(CAST(date_trunc('month', placed_at) AS timestamp), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(CAST(date_trunc('month', requested_at) AS timestamp), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (refunded_at - requested_at)) AS NUMERIC(18,6))/86400 AS DOUBLE PRECISION)) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) NULLS LAST
SELECT COALESCE(om.month, rm.month) AS month, om.orders_placed AS orders_placed, rm.returns_requested AS returns_requested, CASE WHEN om.orders_placed > 0 THEN rm.returns_requested * 100.0 / om.orders_placed ELSE NULL END AS return_rate_pct, rm.returns_refunded AS returns_refunded, rm.avg_days_requested_to_refunded AS avg_days_requested_to_refunded, rm.median_days_requested_to_refunded AS median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN rm.returns_rejected * 100.0 / rm.returns_requested ELSE NULL END AS returns_rejected_pct FROM (SELECT formatDateTime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT formatDateTime(date_trunc('month', requested_at), '%Y-%m') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', requested_at, refunded_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS avg_days_requested_to_refunded, quantileExactInclusiveIf(0.5)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', requested_at, refunded_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400), status = 'refunded' AND refunded_at IS NOT NULL) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month) 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 Mateo Ruiz (analyst for the VP Customer Experience): Title: Returns and refunds: volume and time-to-resolve trend Question: How often do shoppers end up requesting a return/refund, and how long does that process take to resolve, and is it changing over time? Grain: one calendar month, platform-wide Columns: - month (YYYY-MM) - orders placed - returns requested - return rate (%) - returns refunded (count) - avg days requested-to-refunded - median days requested-to-refunded - returns rejected (%) Details: Source: order_returns (for request/approval/refund lifecycle) and orders (for volume denominator). Grain the month on order_returns.requested_at for return-side columns, and on orders.placed_at for 'orders placed'. Return rate = returns requested that month / orders placed that same month, as a percentage rounded to 1 decimal -- note in the report that this is an approximation since a return in month M may relate to an order placed in an earlier month, not necessarily the same month's orders. 'avg/median days requested-to-refunded' = extract(epoch from refunded_at - requested_at)/86400, averaged/median over returns in that month with status='refunded' and refunded_at not null. 'returns rejected (%)' = count of order_returns with status='rejected' that month / total returns requested that month, as a percentage rounded to 1 decimal. Window: 2023-01-01 through 2025-12-31 (36 months). Round day figures to 1 decimal. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pct FROM (SELECT to_char(date_trunc('month', placed_at), 'YYYY-MM') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT to_char(date_trunc('month', requested_at), 'YYYY-MM') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month)
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pct FROM (SELECT strftime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT strftime(date_trunc('month', requested_at), '%Y-%m') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, orders_placed, returns_requested, return_rate_pct, returns_refunded, avg_days_requested_to_refunded, median_days_requested_to_refunded, returns_rejected_pct
submitSELECT COALESCE(om.month, rm.month) AS month, om.orders_placed, rm.returns_requested, CASE WHEN om.orders_placed > 0 THEN ROUND(rm.returns_requested * 100.0 / om.orders_placed, 1) ELSE NULL END AS return_rate_pct, rm.returns_refunded, rm.avg_days_requested_to_refunded, rm.median_days_requested_to_refunded, CASE WHEN rm.returns_requested > 0 THEN ROUND(rm.returns_rejected * 100.0 / rm.returns_requested, 1) ELSE NULL END AS returns_rejected_pct FROM (SELECT strftime(date_trunc('month', placed_at), '%Y-%m') AS month, count(*) AS orders_placed FROM orders WHERE placed_at >= DATE '2023-01-01' AND placed_at < DATE '2026-01-01' GROUP BY 1) om FULL OUTER JOIN (SELECT strftime(date_trunc('month', requested_at), '%Y-%m') AS month, count(*) AS returns_requested, count(*) FILTER (WHERE status = 'refunded') AS returns_refunded, count(*) FILTER (WHERE status = 'rejected') AS returns_rejected, ROUND(AVG(extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL), 1) AS avg_days_requested_to_refunded, ROUND(CAST(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (refunded_at - requested_at))/86400) FILTER (WHERE status = 'refunded' AND refunded_at IS NOT NULL) AS numeric), 1) AS median_days_requested_to_refunded FROM order_returns WHERE requested_at >= DATE '2023-01-01' AND requested_at < DATE '2026-01-01' GROUP BY 1) rm ON om.month = rm.month ORDER BY COALESCE(om.month, rm.month)
Submitted to tonight's batch. Your work on this request is done.