DB Olympics

Queries · q039 · VP Customer Experience · week 1, night 1

Ticket topic mix and recent shift

“What kinds of problems are shoppers contacting merchants about most, and has that mix shifted in the last 6 months versus the prior 6?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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

    Daniel's top priority is #1 (response/resolution trend, and variance by merchant), so I'm spending 2 of tonight's 3 slots there: a platform-wide monthly trend (to see direction of travel) and a per-merchant snapshot for the latest full year (to see spread across merchants, since he likes peer comparisons). The third slot starts #2 (where load comes from) with a tag-based mix comparing the last two half-years to catch any shift. I'm deliberately holding off on YoY columns inside the trend report itself since the 36-month series already shows year-over-year pattern, and saving width for the core time metrics. Returns/refunds (#3), satisfaction (#4) and reviews (#5) are next on my list for later nights.

    Ticket topic mix and recent shift
    Question
    What kinds of problems are shoppers contacting merchants about most, and has that mix shifted in the last 6 months versus the prior 6?
    One row per
    support tag (platform-wide, tag name)
    Columns
    • tag name
    • tickets, last 6 months (Jul-Dec 2025)
    • tickets, prior 6 months (Jan-Jun 2025)
    • share of tickets, last 6 months (%)
    • share of tickets, prior 6 months (%)
    • share change (percentage points)
    • avg resolution hours, last 6 months

  3. Felix 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
  stn.name AS tag_name,
  COALESCE(a.cnt,0) AS tickets_last_6mo,
  COALESCE(b.cnt,0) AS tickets_prior_6mo,
  ROUND(COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0), 1) AS share_last_6mo_pct,
  ROUND(COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0), 1) AS share_prior_6mo_pct,
  ROUND(
    COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)
    - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0)
  , 1) AS share_change_pp,
  ROUND(a.avg_res_hours, 1) AS avg_resolution_hours_last_6mo
FROM (SELECT DISTINCT name FROM support_tags) stn
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt,
         AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0) AS avg_res_hours
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01'
  GROUP BY st.name
) a ON a.name = stn.name
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01'
  GROUP BY st.name
) b ON b.name = stn.name
CROSS JOIN (
  SELECT
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01') AS total_a,
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01') AS total_b
) totals
WHERE COALESCE(a.cnt,0) > 0 OR COALESCE(b.cnt,0) > 0
ORDER BY tickets_last_6mo DESC, tag_name ASC
LIMIT 50
    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of what the report does: - Grain: one row per support tag name (merged across tenants, since tag names are only unique per-tenant). - Metrics: ticket-tag counts for Jul–Dec 2025 and Jan–Jun 2025, each period's share of that period's tagged ticket-tag rows, the share change in points, and average resolution hours (period A only, over resolved tickets). - Filter: only tags with activity in at least one of the two periods are shown, ordered by recent volume descending. - Caveat flagged in the note: multi-tag tickets inflate counts (a ticket with 2 tags counts once per tag), so the tag totals won't sum to total ticket volume — consistent with what the request asked for.”

  4. The report comes back overnight

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

    tag_name          | tickets_last_6mo | tickets_prior_6mo | share_last_6mo_pct | share_prior_6mo_pct | share_change_pp | avg_resolution_hours_last_6mo
------------------+------------------+-------------------+--------------------+---------------------+-----------------+------------------------------
delivery          |              817 |               378 |               34.7 |                28.6 |             6.1 |                          45.5
where-is-my-order |              726 |               331 |               30.8 |                25.1 |             5.8 |                          47.8
complaint         |              265 |               199 |               11.2 |                15.1 |            -3.8 |                          27.7
return            |              237 |               173 |               10.1 |                13.1 |            -3.0 |                          32.4
product-question  |              138 |               109 |                5.9 |                 8.3 |            -2.4 |                          49.9
damaged           |              126 |                84 |                5.3 |                 6.4 |            -1.0 |                          35.8
refund            |               36 |                29 |                1.5 |                 2.2 |            -0.7 |                          24.0
payment           |               11 |                 9 |                0.5 |                 0.7 |            -0.2 |                          29.6
account           |                0 |                 9 |                0.0 |                 0.7 |            -0.7 |                          NULL
(9 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q039

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

    • spellingfetch first
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • placeholderseconds between
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT  stn.name AS tag_name,  COALESCE(a.cnt,0) AS tickets_last_6mo,  COALESCE(b.cnt,0) AS tickets_prior_6mo,  ROUND(COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0), 1) AS share_last_6mo_pct,  ROUND(COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0), 1) AS share_prior_6mo_pct,  ROUND(    COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)    - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0)  , 1) AS share_change_pp,  ROUND(a.avg_res_hours, 1) AS avg_resolution_hours_last_6mo  COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0) AS share_last_6mo_pct,  COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0) AS share_prior_6mo_pct,  COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)  - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0) AS share_change_pp,  a.avg_res_hours AS avg_resolution_hours_last_6moFROM (SELECT DISTINCT name FROM support_tags) stnLEFT JOIN (  SELECT st.name AS name, COUNT(*) AS cnt,         AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0) AS avg_res_hours         AVG({{seconds_between(t.created_at, t.resolved_at)}} / 3600.0) AS avg_res_hours  FROM ticket_tags_map ttm  JOIN tickets t ON t.id = ttm.ticket_id  JOIN support_tags st ON st.id = ttm.support_tag_id  WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01'  GROUP BY st.name) a ON a.name = stn.nameLEFT JOIN (  SELECT st.name AS name, COUNT(*) AS cnt  FROM ticket_tags_map ttm  JOIN tickets t ON t.id = ttm.ticket_id  JOIN support_tags st ON st.id = ttm.support_tag_id  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01'  GROUP BY st.name) b ON b.name = stn.nameCROSS JOIN (  SELECT    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id       WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01') AS total_a,    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01') AS total_b) totalsWHERE COALESCE(a.cnt,0) > 0 OR COALESCE(b.cnt,0) > 0ORDER BY tickets_last_6mo DESC, tag_name ASCLIMIT 50ORDER BY tickets_last_6mo DESC NULLS FIRST, tag_name ASC NULLS LAST{{fetch_first(50)}}

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

    SELECT
  stn.name AS tag_name,
  COALESCE(a.cnt,0) AS tickets_last_6mo,
  COALESCE(b.cnt,0) AS tickets_prior_6mo,
  COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0) AS share_last_6mo_pct,
  COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0) AS share_prior_6mo_pct,
  COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)
  - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0) AS share_change_pp,
  a.avg_res_hours AS avg_resolution_hours_last_6mo
FROM (SELECT DISTINCT name FROM support_tags) stn
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt,
         AVG(CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 3600.0) AS avg_res_hours
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01'
  GROUP BY st.name
) a ON a.name = stn.name
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01'
  GROUP BY st.name
) b ON b.name = stn.name
CROSS JOIN (
  SELECT
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01') AS total_a,
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01') AS total_b
) totals
WHERE COALESCE(a.cnt,0) > 0 OR COALESCE(b.cnt,0) > 0
ORDER BY tickets_last_6mo DESC NULLS FIRST, tag_name ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY
    10 ms100 ms1 sHyper59.1 msDuckDB30.2 msGizmoSQL38.5 msclickhouse_brew197 msFirebolt153 msDataFusion198 msClickHouse234 ms

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

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

Title: Ticket topic mix and recent shift
Question: What kinds of problems are shoppers contacting merchants about most, and has that mix shifted in the last 6 months versus the prior 6?
Grain: one row per support tag (platform-wide, tag name)
Columns:
- tag name
- tickets, last 6 months (Jul-Dec 2025)
- tickets, prior 6 months (Jan-Jun 2025)
- share of tickets, last 6 months (%)
- share of tickets, prior 6 months (%)
- share change (percentage points)
- avg resolution hours, last 6 months
Details: Source: tickets joined to ticket_tags_map and support_tags (group by support_tags.name across all tenants, since we want the platform-level theme). A ticket can carry multiple tags; count each ticket once per tag it carries (so a ticket with 2 tags contributes to both tag rows - note this in the report so totals don't have to sum to ticket volume). Period A = tickets.created_at between 2025-07-01 and 2025-12-31 inclusive; period B = 2025-01-01 to 2025-06-30 inclusive. Share of tickets = tag's ticket count in that period / total tagged-ticket-tag-rows in that period, as a percentage rounded to 1 decimal. Share change = share in period A minus share in period B, in percentage points, rounded to 1 decimal. avg resolution hours (period A only) = extract(epoch from resolved_at - created_at)/3600 averaged over that tag's resolved tickets in period A, rounded to 1 decimal. Order by tickets in the last 6 months descending, tie-break by tag name ascending. Report total distinct tag count returned (expect well under 50 rows).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT
  stn.name AS tag_name,
  COALESCE(a.cnt,0) AS tickets_last_6mo,
  COALESCE(b.cnt,0) AS tickets_prior_6mo,
  ROUND(COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0), 1) AS share_last_6mo_pct,
  ROUND(COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0), 1) AS share_prior_6mo_pct,
  ROUND(
    COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)
    - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0)
  , 1) AS share_change_pp,
  ROUND(a.avg_res_hours, 1) AS avg_resolution_hours_last_6mo
FROM (SELECT DISTINCT name FROM support_tags) stn
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt,
         AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0) AS avg_res_hours
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01'
  GROUP BY st.name
) a ON a.name = stn.name
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01'
  GROUP BY st.name
) b ON b.name = stn.name
CROSS JOIN (
  SELECT
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01') AS total_a,
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01') AS total_b
) totals
WHERE COALESCE(a.cnt,0) > 0 OR COALESCE(b.cnt,0) > 0
ORDER BY tickets_last_6mo DESC, tag_name ASC
LIMIT 50
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tag_name, tickets_last_6mo, tickets_prior_6mo, share_last_6mo_pct, share_prior_6mo_pct, share_change_pp, avg_resolution_hours_last_6mo
    • Felix calls submit
      SELECT
  stn.name AS tag_name,
  COALESCE(a.cnt,0) AS tickets_last_6mo,
  COALESCE(b.cnt,0) AS tickets_prior_6mo,
  ROUND(COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0), 1) AS share_last_6mo_pct,
  ROUND(COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0), 1) AS share_prior_6mo_pct,
  ROUND(
    COALESCE(a.cnt,0) * 100.0 / NULLIF(totals.total_a,0)
    - COALESCE(b.cnt,0) * 100.0 / NULLIF(totals.total_b,0)
  , 1) AS share_change_pp,
  ROUND(a.avg_res_hours, 1) AS avg_resolution_hours_last_6mo
FROM (SELECT DISTINCT name FROM support_tags) stn
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt,
         AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0) AS avg_res_hours
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01'
  GROUP BY st.name
) a ON a.name = stn.name
LEFT JOIN (
  SELECT st.name AS name, COUNT(*) AS cnt
  FROM ticket_tags_map ttm
  JOIN tickets t ON t.id = ttm.ticket_id
  JOIN support_tags st ON st.id = ttm.support_tag_id
  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01'
  GROUP BY st.name
) b ON b.name = stn.name
CROSS JOIN (
  SELECT
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-07-01' AND t.created_at < DATE '2026-01-01') AS total_a,
    (SELECT COUNT(*) FROM ticket_tags_map ttm JOIN tickets t ON t.id = ttm.ticket_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2025-07-01') AS total_b
) totals
WHERE COALESCE(a.cnt,0) > 0 OR COALESCE(b.cnt,0) > 0
ORDER BY tickets_last_6mo DESC, tag_name ASC
LIMIT 50
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. Summary of what the report does: - **Grain:** one row per support tag name (merged across tenants, since tag names are only unique per-tenant). - **Metrics:** ticket-tag counts for Jul–Dec 2025 and Jan–Jun 2025, each period's share of that period's tagged ticket-tag rows, the share change in points, and average resolution hours (period A only, over resolved tickets). - **Filter:** only tags with activity in at least one of the two periods are shown, ordered by recent volume descending. - **Caveat flagged in the note:** multi-tag tickets inflate counts (a ticket with 2 tags counts once per tag), so the tag totals won't sum to total ticket volume — consistent with what the request asked for.

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

    Felix's transcript (272 KB) · Mateo Ruiz's transcript (892 KB) · Daniel Achterberg's transcript (264 KB) · the night's plan (5 KB) · the night's reports (15 KB) · the week's memo (3 KB)