Queries · q040 · VP Customer Experience · week 1, night 2
“Are delivery/where-is-my-order tickets driving the recent growth in ticket volume and the slower/more volatile resolution times seen in the monthly trend?”
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 1 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 to_char(date_trunc('month', x.created_at), 'YYYY-MM') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_other FROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at)
SELECT to_char(date_trunc('month', x.created_at), 'YYYY-MM') AS month, strftime(date_trunc('month', x.created_at), '%Y-%m') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_otherFROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01') xGROUP BY date_trunc('month', x.created_at)ORDER BY date_trunc('month', x.created_at)
“This compiles and returns exactly the requested columns. Submitting now.”
“Submitted. The query classifies each ticket once (via an EXISTS subquery against ticket_tags_map/support_tags, case-insensitive match on 'delivery' or 'where-is-my-order'), then aggregates per calendar month into the 8 requested columns — tickets total, delivery count/share, and avg first-response/resolution hours split by delivery vs. other, covering Jul-2024 through Dec-2025 (18 rows expected).”
As Mateo Ruiz saw it, from the small dataset the agents worked on
month | tickets_total | tickets_delivery_related | delivery_share_pct | avg_first_response_hours_delivery | avg_first_response_hours_other | avg_resolution_hours_delivery | avg_resolution_hours_other --------+---------------+--------------------------+--------------------+-----------------------------------+--------------------------------+-------------------------------+--------------------------- 2024-07 | 131 | 57 | 43.5 | 16.2 | 14.9 | 25.4 | 26.5 2024-08 | 109 | 43 | 39.4 | 8.0 | 14.0 | 15.5 | 26.3 2024-09 | 121 | 55 | 45.5 | 9.7 | 12.3 | 21.2 | 21.4 2024-10 | 134 | 66 | 49.3 | 10.1 | 12.6 | 17.8 | 22.2 2024-11 | 177 | 81 | 45.8 | 13.3 | 21.1 | 22.4 | 36.2 2024-12 | 460 | 248 | 53.9 | 41.2 | 49.2 | 86.6 | 95.9 2025-01 | 150 | 42 | 28.0 | 12.5 | 52.1 | 32.3 | 98.5 2025-02 | 94 | 32 | 34.0 | 8.9 | 10.8 | 22.9 | 21.6 2025-03 | 116 | 50 | 43.1 | 8.8 | 13.1 | 16.3 | 20.8 2025-04 | 133 | 52 | 39.1 | 9.1 | 9.9 | 17.9 | 20.1 2025-05 | 206 | 114 | 55.3 | 37.6 | 12.7 | 78.8 | 20.5 2025-06 | 169 | 88 | 52.1 | 59.6 | 29.3 | 120.5 | 51.0 2025-07 | 153 | 67 | 43.8 | 13.4 | 12.1 | 26.7 | 25.0 2025-08 | 138 | 48 | 34.8 | 15.4 | 11.4 | 29.5 | 24.6 2025-09 | 168 | 94 | 56.0 | 12.0 | 13.5 | 20.7 | 22.2 2025-10 | 192 | 105 | 54.7 | 14.4 | 15.6 | 24.7 | 25.7 2025-11 | 316 | 206 | 65.2 | 23.1 | 23.3 | 49.6 | 43.0 2025-12 | 485 | 297 | 61.2 | 39.4 | 41.0 | 65.3 | 50.5 (18 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 strftime(date_trunc('month', x.created_at), '%Y-%m') AS month, {{year_month(date_trunc('month', x.created_at))}} AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_other 100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*) AS delivery_share_pct, avg({{seconds_between(x.created_at, x.first_response_at)}}/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_delivery, avg({{seconds_between(x.created_at, x.first_response_at)}}/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_other, avg({{seconds_between(x.created_at, x.resolved_at)}}/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_delivery, avg({{seconds_between(x.created_at, x.resolved_at)}}/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_otherFROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, t.id AS id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01') xGROUP BY date_trunc('month', x.created_at)ORDER BY date_trunc('month', x.created_at)ORDER BY date_trunc('month', x.created_at) NULLS LAST
SELECT strftime(date_trunc('month', x.created_at), '%Y-%m') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, 100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*) AS delivery_share_pct, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_other, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_other FROM ( SELECT t.id AS id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at) NULLS LAST
SELECT to_char(date_trunc('month', x.created_at), 'YYYY-MM') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, 100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*) AS delivery_share_pct, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_other, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_other FROM ( SELECT t.id AS id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at) NULLS LAST
SELECT to_char(CAST(date_trunc('month', x.created_at) AS timestamp), 'YYYY-MM') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, 100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*) AS delivery_share_pct, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.first_response_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_other, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_delivery, avg(CAST(EXTRACT(EPOCH FROM (x.resolved_at - x.created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_other FROM ( SELECT t.id AS id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at) NULLS LAST
SELECT formatDateTime(date_trunc('month', x.created_at), '%Y-%m') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, 100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*) AS delivery_share_pct, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', x.created_at, x.first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_delivery, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', x.created_at, x.first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL) AS avg_first_response_hours_other, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', x.created_at, x.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_delivery, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', x.created_at, x.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL) AS avg_resolution_hours_other FROM ( SELECT t.id AS id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at) 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: Delivery-related vs other tickets: volume and resolution speed Question: Are delivery/where-is-my-order tickets driving the recent growth in ticket volume and the slower/more volatile resolution times seen in the monthly trend? Grain: one calendar month, platform-wide, split into two ticket groups (delivery-related vs other) Columns: - month (YYYY-MM) - tickets total - tickets delivery-related - delivery share of tickets (%) - avg first response hours, delivery-related - avg first response hours, other - avg resolution hours, delivery-related - avg resolution hours, other Details: Source: tickets joined to ticket_tags_map and support_tags. Define a ticket as 'delivery-related' if it has at least one tag whose support_tags.name is 'delivery' or 'where-is-my-order' (case-insensitive match); all other tickets are 'other'. Classify at the ticket level (distinct ticket ids), not the tag-row level, so a ticket is counted once even if it carries both tags or additional tags. Window: tickets.created_at from 2024-07-01 through 2025-12-31 (18 months) to cover the Dec-2024 spike, the Jan-2025 carryover, the May/Jun-2025 anomaly, and the Nov/Dec-2025 spike. avg first response hours = extract(epoch from first_response_at - created_at)/3600 averaged over tickets in that group/month with first_response_at not null; avg resolution hours = extract(epoch from resolved_at - created_at)/3600 averaged over tickets in that group/month with resolved_at not null. Round hour figures to 1 decimal, share to 1 decimal. Order by month ascending. Expect 18 rows. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT to_char(date_trunc('month', x.created_at), 'YYYY-MM') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_other FROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at)
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT strftime(date_trunc('month', x.created_at), '%Y-%m') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_other FROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, tickets_total, tickets_delivery_related, delivery_share_pct, avg_first_response_hours_delivery, avg_first_response_hours_other, avg_resolution_hours_delivery, avg_resolution_hours_other
submitSELECT strftime(date_trunc('month', x.created_at), '%Y-%m') AS month, count(*) AS tickets_total, count(*) FILTER (WHERE x.is_delivery) AS tickets_delivery_related, round(100.0 * count(*) FILTER (WHERE x.is_delivery) / count(*), 1) AS delivery_share_pct, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_delivery, round(avg(extract(epoch from (x.first_response_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.first_response_at IS NOT NULL)::numeric, 1) AS avg_first_response_hours_other, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_delivery, round(avg(extract(epoch from (x.resolved_at - x.created_at))/3600) FILTER (WHERE NOT x.is_delivery AND x.resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours_other FROM ( SELECT t.id, t.tenant_id, t.created_at, t.first_response_at, t.resolved_at, EXISTS ( SELECT 1 FROM ticket_tags_map ttm JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id WHERE ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id AND lower(st.name) IN ('delivery', 'where-is-my-order') ) AS is_delivery FROM tickets t WHERE t.created_at >= timestamp '2024-07-01' AND t.created_at < timestamp '2026-01-01' ) x GROUP BY date_trunc('month', x.created_at) ORDER BY date_trunc('month', x.created_at)
Submitted to tonight's batch. Your work on this request is done.