DB Olympics

Queries · q046 · VP Customer Experience · week 1, night 4

Shopper satisfaction (CSAT) by merchant, 2025

“Which merchants have the weakest shopper satisfaction, and how much does it vary across the platform?”

Manager · asked the questionDaniel AchterbergVP Customer Experience. Former head of support at a marketplace. Merchants run their helpdesk on Tidewick, so their shoppers' experience is partly Tidewick's reputation.
Analyst · planned the reportMateo RuizComparative: likes year-over-year and peer comparisons across merchants and countries.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  1. Daniel Achterberg writes to Mateo Ruiz

    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:

    1. How are we doing on response and resolution times, and is it getting better or worse? I keep hearing "we're slow" anecdotally but have no sense of the trend, or whether it varies a lot by merchant. I need a baseline I can put in front of the leadership team.
    2. Where is the support load actually coming from? What kinds of problems are shoppers contacting merchants about most, and has that mix been shifting lately? I want to know if there's a theme we could fix at the platform level instead of merchant-by-merchant.
    3. What does the returns and refunds experience look like from the shopper's side? How often shoppers end up wanting their money back, how long that takes to resolve, and whether that's a smooth process or a source of frustration. This feels close to our reputation.
    4. How satisfied are shoppers overall, and where is satisfaction weakest? I'd like a sense of satisfaction levels and whether certain merchants or situations stand out as problem spots.
    5. (If there's room this week) What are reviews and ratings telling us? Even a first look at how shoppers rate their purchases, and whether that lines up with what we see in support, would help me understand if there are blind spots in the ticket data.

    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

  2. Mateo Ruiz plans the night

    The analyst · week 1, night 4, request 1 of 3

    Night 3 confirmed the Studio Vrij story with real operational data: their placed-to-delivered time spiked to 9.3 days avg and 70.1% of shipments took over 7 days in May 2025 alone (vs 0-20% other months, platform average stayed 3-5%) - a genuine single-merchant fulfillment breakdown that explains the ticket spike, not a support-side or platform-wide issue. Useful side-finding: platform-wide % of shipments over 7 days creeps up in Nov/Dec 2025 (7-12% vs ~3-5% normally) alongside a real volume surge (1600→2600+ shipments/month) - that's consistent with capacity-constrained holiday fulfillment driving the recurring Nov/Dec ticket spikes seen across all three years, which is the platform-level theme Daniel asked about. The returns-by-merchant report is reassuring: resolve time (~7.5-8.5 days) and rejection rates are consistent across merchants regardless of return-rate level (which mostly tracks vertical - apparel/footwear naturally higher, food/beauty lower) - a clean contrast to how much support-speed varies by merchant. CSAT is noisy but its worst months (Dec-23, Dec-24/Jan-25, Jun-25, Dec-25) line up exactly with the ticket volume/slowness spikes already identified, suggesting speed drives satisfaction. This is the last night, so I'm using the 3 slots to: (1) get CSAT by merchant so Daniel can see which merchants are the weakest satisfaction spots (mirroring the support-speed and returns peer reports already in hand), (2) directly quantify the CSAT-vs-resolution-speed relationship platform-wide to turn "slow support hurts satisfaction" from a visual coincidence into a measured one - this is the single clearest "fix this and it moves the needle broadly" argument for the memo, and (3) a first look at product review ratings (priority #5) since there's room, to see if it echoes the CSAT dips or reveals a blind spot in ticket data.

    Shopper satisfaction (CSAT) by merchant, 2025
    Question
    Which merchants have the weakest shopper satisfaction, and how much does it vary across the platform?
    One row per
    tenant (merchant), among tenants with at least 10 CSAT responses in 2025
    Columns
    • merchant name
    • CSAT responses, 2025
    • avg score (1-5)
    • median score (1-5)
    • % satisfied (score >= 4)
    • % dissatisfied (score <= 2)

  3. Bea 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)
    SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t1.response_count,
  t2.avg_score,
  t3.median_score,
  t4.pct_satisfied,
  t5.pct_dissatisfied
FROM tenants t0
JOIN (
  SELECT tenant_id, COUNT(*) AS response_count
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
  HAVING COUNT(*) >= 10
) t1 ON t1.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(AVG(score)::numeric, 2) AS avg_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)::numeric, 1) AS median_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t3 ON t3.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_satisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t4 ON t4.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_dissatisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t5 ON t5.tenant_id = t0.id
ORDER BY t2.avg_score ASC, t0.name ASC
LIMIT 50

    “Query compiles. Submitting now.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. The report returns one row per merchant (tenant) with ≥10 CSAT responses in 2025 — response count, average score, median score, % satisfied (≥4), and % dissatisfied (≤2) — sorted worst average first, capped at 50 rows per house standard.”

  4. The report comes back overnight

    As Mateo Ruiz saw it, from the small dataset the agents worked on

    tenant_id | merchant_name                 | response_count | avg_score | median_score | pct_satisfied | pct_dissatisfied
----------+-------------------------------+----------------+-----------+--------------+---------------+-----------------
       79 | Kibblewick                    |             17 |      2.82 |          3.0 |          35.3 |             47.1
        2 | Marrowby & Vane               |             79 |      2.86 |          3.0 |          34.2 |             39.2
        7 | Cragsley Outfitters           |            130 |      3.05 |          3.0 |          39.2 |             37.7
       25 | Studio Vrij                   |             49 |      3.10 |          3.0 |          49.0 |             32.7
        9 | Honeycomb Larder              |             14 |      3.14 |          3.0 |          42.9 |             42.9
        1 | Grind Collective              |             42 |      3.17 |          3.0 |          45.2 |             28.6
       42 | Gratlinie Outdoor             |             10 |      3.20 |          3.0 |          30.0 |             10.0
       17 | Ampfeld Elektronik            |             12 |      3.33 |          3.0 |          41.7 |              8.3
       40 | Petalcourt Beauty             |             34 |      3.35 |          3.5 |          50.0 |             26.5
       11 | Ballycorran Industrial Supply |             14 |      3.43 |          3.0 |          35.7 |             14.3
       20 | Sourdough Club                |             15 |      3.60 |          4.0 |          53.3 |             20.0
       18 | Brennquell Kaffee             |             32 |      3.63 |          4.0 |          56.3 |             18.8
       12 | Spice Route Box               |             24 |      3.75 |          4.0 |          66.7 |             16.7
        5 | Fennel & Flint                |             16 |      3.88 |          4.0 |          75.0 |             18.8
       29 | Cinder Peak Sports Co.        |             13 |      4.08 |          4.0 |          69.2 |              0.0
       35 | Wiesgrund Wohnen              |             10 |      4.20 |          4.5 |          70.0 |              0.0
(16 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 q046

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

    • spellingcast (2×)
    • spellingfetch first
    • determinismtie-breaker: tenant names are not unique
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; 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
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit (5×)
    • placeholderpercentile cont
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT  t0.id AS tenant_id,  t0.name AS merchant_name,  t1.response_count,  t2.avg_score,  t3.median_score,  t4.pct_satisfied,  t5.pct_dissatisfied  t1.response_count AS response_count,  t2.avg_score AS avg_score,  t3.median_score AS median_score,  t4.pct_satisfied AS pct_satisfied,  t5.pct_dissatisfied AS pct_dissatisfiedFROM tenants t0JOIN (  SELECT tenant_id, COUNT(*) AS response_count  FROM csat_responses  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'  GROUP BY tenant_id  HAVING COUNT(*) >= 10) t1 ON t1.tenant_id = t0.idJOIN (  SELECT tenant_id, ROUND(AVG(score)::numeric, 2) AS avg_score  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS avg_score  FROM csat_responses  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'  GROUP BY tenant_id) t2 ON t2.tenant_id = t0.idJOIN (  SELECT tenant_id, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)::numeric, 1) AS median_score  SELECT tenant_id, {{percentile_cont(0.5, score)}} AS median_score  FROM csat_responses  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'  GROUP BY tenant_id) t3 ON t3.tenant_id = t0.idJOIN (  SELECT tenant_id,    ROUND(100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_satisfied    100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*) AS pct_satisfied  FROM csat_responses  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'  GROUP BY tenant_id) t4 ON t4.tenant_id = t0.idJOIN (  SELECT tenant_id,    ROUND(100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_dissatisfied    100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*) AS pct_dissatisfied  FROM csat_responses  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'  GROUP BY tenant_id) t5 ON t5.tenant_id = t0.idORDER BY t2.avg_score ASC, t0.name ASCLIMIT 50ORDER BY t2.avg_score ASC NULLS LAST, t0.name ASC NULLS LAST, t0.id ASC NULLS LAST{{fetch_first(50)}}

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

    SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t1.response_count AS response_count,
  t2.avg_score AS avg_score,
  t3.median_score AS median_score,
  t4.pct_satisfied AS pct_satisfied,
  t5.pct_dissatisfied AS pct_dissatisfied
FROM tenants t0
JOIN (
  SELECT tenant_id, COUNT(*) AS response_count
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
  HAVING COUNT(*) >= 10
) t1 ON t1.tenant_id = t0.id
JOIN (
  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS avg_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
JOIN (
  SELECT tenant_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) AS median_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t3 ON t3.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*) AS pct_satisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t4 ON t4.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*) AS pct_dissatisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t5 ON t5.tenant_id = t0.id
ORDER BY t2.avg_score ASC NULLS LAST, t0.name ASC NULLS LAST, t0.id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY
    10 ms100 ms1 s10 s30 sHyper15.1 msDuckDB11.0 msGizmoSQL14.9 msclickhouse_brew92.9 msFirebolt38.8 msDataFusiontimeout (30 s)ClickHouse36.8 ms
    • DataFusionno result within 30 s

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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Mateo Ruiz (analyst for the VP Customer Experience):

Title: Shopper satisfaction (CSAT) by merchant, 2025
Question: Which merchants have the weakest shopper satisfaction, and how much does it vary across the platform?
Grain: one row per tenant (merchant), among tenants with at least 10 CSAT responses in 2025
Columns:
- merchant name
- CSAT responses, 2025
- avg score (1-5)
- median score (1-5)
- % satisfied (score >= 4)
- % dissatisfied (score <= 2)
Details: Source: csat_responses joined to tenants (for name). Window: submitted_at in 2025-01-01 through 2025-12-31. Only include tenants with >=10 CSAT responses in 2025 to avoid noisy small samples. avg/median score over csat_responses.score. '% satisfied' = score>=4 count / total responses that tenant-year, percentage rounded to 1 decimal; '% dissatisfied' = score<=2 count / total, percentage rounded to 1 decimal. Round avg score to 2 decimals, median to 1 decimal. Order by avg score ascending (worst first, so the weakest spots surface at the top), tie-break by merchant name ascending. Cap at 50 printed rows per house standard; note total qualifying tenant count in the footer. I will cross-reference merchant names against the existing 'Response & resolution times by merchant, 2025' report rather than re-querying resolution time here, to keep this report single-purpose.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t1.response_count,
  t2.avg_score,
  t3.median_score,
  t4.pct_satisfied,
  t5.pct_dissatisfied
FROM tenants t0
JOIN (
  SELECT tenant_id, COUNT(*) AS response_count
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
  HAVING COUNT(*) >= 10
) t1 ON t1.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(AVG(score)::numeric, 2) AS avg_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)::numeric, 1) AS median_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t3 ON t3.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_satisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t4 ON t4.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_dissatisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t5 ON t5.tenant_id = t0.id
ORDER BY t2.avg_score ASC, t0.name ASC
LIMIT 50
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_id, merchant_name, response_count, avg_score, median_score, pct_satisfied, pct_dissatisfied
    • BeaQuery compiles. Submitting now.
    • Bea calls submit
      SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t1.response_count,
  t2.avg_score,
  t3.median_score,
  t4.pct_satisfied,
  t5.pct_dissatisfied
FROM tenants t0
JOIN (
  SELECT tenant_id, COUNT(*) AS response_count
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
  HAVING COUNT(*) >= 10
) t1 ON t1.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(AVG(score)::numeric, 2) AS avg_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
JOIN (
  SELECT tenant_id, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)::numeric, 1) AS median_score
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t3 ON t3.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_satisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t4 ON t4.tenant_id = t0.id
JOIN (
  SELECT tenant_id,
    ROUND(100.0 * SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_dissatisfied
  FROM csat_responses
  WHERE submitted_at >= DATE '2025-01-01' AND submitted_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t5 ON t5.tenant_id = t0.id
ORDER BY t2.avg_score ASC, t0.name ASC
LIMIT 50
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report returns one row per merchant (tenant) with ≥10 CSAT responses in 2025 — response count, average score, median score, % satisfied (≥4), and % dissatisfied (≤2) — sorted worst average first, capped at 50 rows per house standard.

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

    Bea's transcript (263 KB) · Mateo Ruiz's transcript (892 KB) · Daniel Achterberg's transcript (264 KB) · the night's plan (5 KB) · the night's reports (8 KB) · the week's memo (3 KB)