Queries · q409 · Support Operations Manager · week 2, night 4

Ticket performance with known carrier-incident months excluded

“Once we strip out the known carrier-incident months, do Marrowby & Vane and Studio Vrij still look worse than Cragsley Outfitters, or does that fully explain the gap?”

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

1. A business event lands on the Support Operations Manager's desk

   Week 1; shared with Daniel Achterberg, VP Customer Experience

   Review how the helpdesk runs: queues and agent groups, use of macros and knowledge articles, SLA targets and breaches, and what makes tickets slow.

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Mia Schultz writes to Ben Carter

   The manager, at the start of week 2

   Subject: Re: Helpdesk Operating Review — great start, here's where to push next

   Ben,

   This is exactly the kind of baseline I wanted — and the speed-to-CSAT finding alone is worth putting in front of Daniel as-is. A few things stood out that I want sharpened before we present or act on them.

   What surprised me / needs explaining:

   • Marrowby & Vane and Studio Vrij are clearly our weak spot, but "under-resourced vs. harder cases" are two very different fixes (hire vs. retrain/reprioritize), and right now we can't tell which it is.
   • The macro usage number not reconciling with actual reply volume bothers me — I'd rather have no macro story this week than a wrong one, so let's not repeat that figure anywhere until it's explained.
   • The topic/tag breakdown (damaged, account, product-question) is useful but I don't want to act on it until we know how much of our ticket volume isn't tagged at all.

   Questions for next week, in priority order:

   1. For Marrowby & Vane and Studio Vrij specifically: is their breach rate because they're thinner on agents relative to their ticket load, or because their tickets take genuinely longer regardless of staffing (harder issues, bigger customers, more back-and-forth)? I want enough to recommend either "add headcount" or "something else is going on" with confidence.
   2. Before we use the macro usage figures for anything: what is that count actually tracking, and can we get a trustworthy sense of how much agents lean on canned replies versus writing from scratch?
   3. What share of tickets carry no topic tag at all, and does that change the story on which topics breach most? I don't want to walk into a training conversation with a skewed sample.
   4. If there's room: can we get even a rough signal on whether people who view a knowledge article before contacting us end up not filing a ticket at all, or filing a faster one? Even a partial read would tell us if KB investment is worth pushing on next quarter.

   Thanks — this is shaping up well. Let's make next week about turning the Marrowby & Vane / Studio Vrij question into something actionable.

   Mia

   Show the whole note

3. Ben Carter plans the night

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

   The picture is now very clear and it changes the recommendation completely. Report 1 shows Marrowby & Vane gets 51.6% of shipments from Albion Parcel — the exact carrier the COO team flagged as broken from November onward — while Report 2 shows Marrowby & Vane's delivery-tagged tickets were near-normal all year (240-1,196 min/ticket, Feb-Oct) until November (9,533 min, 61.8% breach) and December (19,274 min, 100% breach). That's a live, ongoing carrier failure, not a staffing gap. Studio Vrij tells a different story: its delivery-ticket resolution time exploded in May-July 2025 (8,528 / 29,549 / 8,763 minutes) and then fully recovered by September-December (83-680 minutes, 0% breach) — this matches almost exactly what the VP Customer Experience team posted last week about a merchant's 'local May 2025 delivery breakdown... not a platform-wide issue.' That was very likely Studio Vrij, and it's already over. Neither merchant looks like a 'hire more agents' case. Tonight I want to (1) pin the Albion Parcel collapse specifically to Marrowby & Vane's own shipments month-by-month to nail the mechanism and check if it's still ongoing, (2) find which of Studio Vrij's carriers caused the May-July spike and confirm it's genuinely resolved, and (3) run the clean controlled comparison Mia actually needs for a decision: strip the known incident months out of each tenant's numbers and see if what's left still looks worse than Cragsley's baseline, or whether the whole gap was the carrier event. On the knowledge-base question: the event_name inventory (Report 3) shows only 8 tracked events platform-wide — none related to viewing a help/KB article — so combined with last week's zero-match page_views test, I'm closing that question as 'not measurable with current instrumentation' rather than spending another slot guessing.

   Read the whole plan
   Ticket performance with known carrier-incident months excluded

   Question

   Once we strip out the known carrier-incident months, do Marrowby & Vane and Studio Vrij still look worse than Cragsley Outfitters, or does that fully explain the gap?

   One row per

   tenant: Marrowby & Vane (excluding Nov-Dec 2025), Studio Vrij (excluding May-Jul 2025), and Cragsley Outfitters (full year, as unadjusted baseline)

   Columns

   • tenant_name
   • excluded_period (text description, 'None' for Cragsley)
   • ticket_count_after_exclusion
   • avg_resolution_minutes_after_exclusion
   • resolution_breach_rate_after_exclusion

4. 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
     tenant_name,
     excluded_period,
     ticket_count_after_exclusion,
     avg_resolution_minutes_after_exclusion,
     resolution_breach_rate_after_exclusion
   FROM (
     SELECT
       ft.tenant_name,
       ft.excluded_period,
       count(*) AS ticket_count_after_exclusion,
       round(avg(extract(epoch from (ft.resolved_at - ft.created_at)) / 60.0)) AS avg_resolution_minutes_after_exclusion,
       round(
         100.0 * sum(
           case when exists (
             select 1 from sla_breaches sb
             where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'
           ) then 1 else 0 end
         ) / count(*),
         1
       ) AS resolution_breach_rate_after_exclusion
     FROM (
       SELECT
         tk.id as ticket_id,
         t.name as tenant_name,
         case
           when t.name = 'Marrowby & Vane' then 'Excludes Nov-Dec 2025'
           when t.name = 'Studio Vrij' then 'Excludes May-Jul 2025'
           else 'None'
         end as excluded_period,
         tk.created_at,
         tk.resolved_at
       FROM tickets tk
       JOIN tenants t ON t.id = tk.tenant_id
       WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters')
         AND tk.created_at >= timestamp '2025-01-01'
         AND tk.created_at < timestamp '2026-01-01'
         AND NOT (
           (t.name = 'Marrowby & Vane' AND tk.created_at >= timestamp '2025-11-01' AND tk.created_at < timestamp '2026-01-01')
           OR
           (t.name = 'Studio Vrij' AND tk.created_at >= timestamp '2025-05-01' AND tk.created_at < timestamp '2025-08-01')
         )
     ) ft
     GROUP BY ft.tenant_name, ft.excluded_period
   ) x
   ORDER BY resolution_breach_rate_after_exclusion DESC, tenant_name

   “Query compiles cleanly with the expected columns. Submitting.”

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

   “Submitted. The query pulls 2025 tickets for the three named tenants, drops Marrowby & Vane's Nov–Dec and Studio Vrij's May–Jul tickets, and reports ticket count, average resolution time, and resolution-breach rate per tenant so Ben can compare directly against last week's unadjusted figures.”

5. The report comes back overnight

   As Ben Carter saw it, from the small dataset the agents worked on

   tenant_name         | excluded_period       | ticket_count_after_exclusion | avg_resolution_minutes_after_exclusion | resolution_breach_rate_after_exclusion
   --------------------+-----------------------+------------------------------+----------------------------------------+---------------------------------------
   Cragsley Outfitters | None                  |                          400 |                                 2348.0 |                                   29.8
   Marrowby & Vane     | Excludes Nov-Dec 2025 |                          138 |                                 2909.0 |                                   22.5
   Studio Vrij         | Excludes May-Jul 2025 |                           71 |                                  985.0 |                                    7.0
   (3 rows)

6. What came of it

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

   It builds on:

   • q405Monthly delivery-topic ticket volume and speed, flagged merchants vs. Cragsley
   • q398Staffing load vs. ticket complexity, top merchants benchmark

7. It becomes benchmark query q409

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit (4×)
   • 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×)
   The benchmark text, against the submitted SQL

   SELECT  tenant_name,  excluded_period,  ticket_count_after_exclusion,  avg_resolution_minutes_after_exclusion,  resolution_breach_rate_after_exclusionFROM (  SELECT    ft.tenant_name,    ft.excluded_period,    ft.tenant_name AS tenant_name,    ft.excluded_period AS excluded_period,    count(*) AS ticket_count_after_exclusion,    round(avg(extract(epoch from (ft.resolved_at - ft.created_at)) / 60.0)) AS avg_resolution_minutes_after_exclusion,    round(      100.0 * sum(        case when exists (          select 1 from sla_breaches sb          where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'        ) then 1 else 0 end      ) / count(*),      1    ) AS resolution_breach_rate_after_exclusion    avg({{seconds_between(ft.created_at, ft.resolved_at)}} / 60.0) AS avg_resolution_minutes_after_exclusion,    100.0 * sum(      case when exists (        select 1 from sla_breaches sb        where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'      ) then 1 else 0 end    ) / count(*) AS resolution_breach_rate_after_exclusion  FROM (    SELECT      tk.id as ticket_id,      t.name as tenant_name,      case        when t.name = 'Marrowby & Vane' then 'Excludes Nov-Dec 2025'        when t.name = 'Studio Vrij' then 'Excludes May-Jul 2025'        else 'None'      end as excluded_period,      tk.created_at,      tk.resolved_at      tk.created_at AS created_at,      tk.resolved_at AS resolved_at    FROM tickets tk    JOIN tenants t ON t.id = tk.tenant_id    WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters')      AND tk.created_at >= timestamp '2025-01-01'      AND tk.created_at < timestamp '2026-01-01'      AND NOT (        (t.name = 'Marrowby & Vane' AND tk.created_at >= timestamp '2025-11-01' AND tk.created_at < timestamp '2026-01-01')        OR        (t.name = 'Studio Vrij' AND tk.created_at >= timestamp '2025-05-01' AND tk.created_at < timestamp '2025-08-01')      )  ) ft  GROUP BY ft.tenant_name, ft.excluded_period) xORDER BY resolution_breach_rate_after_exclusion DESC, tenant_nameORDER BY resolution_breach_rate_after_exclusion DESC NULLS FIRST, tenant_name NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewClickHouse · chDB

   SELECT
     tenant_name,
     excluded_period,
     ticket_count_after_exclusion,
     avg_resolution_minutes_after_exclusion,
     resolution_breach_rate_after_exclusion
   FROM (
     SELECT
       ft.tenant_name AS tenant_name,
       ft.excluded_period AS excluded_period,
       count(*) AS ticket_count_after_exclusion,
       avg(CAST(EXTRACT(EPOCH FROM (ft.resolved_at - ft.created_at)) AS NUMERIC(18,6)) / 60.0) AS avg_resolution_minutes_after_exclusion,
       100.0 * sum(
         case when exists (
           select 1 from sla_breaches sb
           where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'
         ) then 1 else 0 end
       ) / count(*) AS resolution_breach_rate_after_exclusion
     FROM (
       SELECT
         tk.id as ticket_id,
         t.name as tenant_name,
         case
           when t.name = 'Marrowby & Vane' then 'Excludes Nov-Dec 2025'
           when t.name = 'Studio Vrij' then 'Excludes May-Jul 2025'
           else 'None'
         end as excluded_period,
         tk.created_at AS created_at,
         tk.resolved_at AS resolved_at
       FROM tickets tk
       JOIN tenants t ON t.id = tk.tenant_id
       WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters')
         AND tk.created_at >= timestamp '2025-01-01'
         AND tk.created_at < timestamp '2026-01-01'
         AND NOT (
           (t.name = 'Marrowby & Vane' AND tk.created_at >= timestamp '2025-11-01' AND tk.created_at < timestamp '2026-01-01')
           OR
           (t.name = 'Studio Vrij' AND tk.created_at >= timestamp '2025-05-01' AND tk.created_at < timestamp '2025-08-01')
         )
     ) ft
     GROUP BY ft.tenant_name, ft.excluded_period
   ) x
   ORDER BY resolution_breach_rate_after_exclusion DESC NULLS FIRST, tenant_name NULLS LAST

   • DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
   • ClickHouseCode: 48. DB::Exception: Correlated subqueries in aggregate function argument are not supported. (NOT_IMPLEMENTED)

   Scale factor 1000, c8a.24xlarge, after the last load. q409 on the start page, with every configuration.

8. 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, 7 turns

   • The request, as Felix received it

     Report request from Ben Carter (analyst for the Support Operations Manager):
     
     Title: Ticket performance with known carrier-incident months excluded
     Question: Once we strip out the known carrier-incident months, do Marrowby & Vane and Studio Vrij still look worse than Cragsley Outfitters, or does that fully explain the gap?
     Grain: one row per tenant: Marrowby & Vane (excluding Nov-Dec 2025), Studio Vrij (excluding May-Jul 2025), and Cragsley Outfitters (full year, as unadjusted baseline)
     Columns:
     - tenant_name
     - excluded_period (text description, 'None' for Cragsley)
     - ticket_count_after_exclusion
     - avg_resolution_minutes_after_exclusion
     - resolution_breach_rate_after_exclusion
     Details: Window: tickets.created_at in 2025, restricted to the three named tenants, across ALL topics/tags (not just delivery), with Marrowby & Vane's November and December 2025 tickets excluded, Studio Vrij's May, June and July 2025 tickets excluded, and Cragsley Outfitters unmodified as the full-year baseline for comparison. Source tables: tickets, sla_breaches (breach_kind='resolution'). Order by resolution_breach_rate_after_exclusion descending. Round rate to 1 decimal percent, minutes to whole numbers. Goal: compare these adjusted rates directly against last week's full-year figures (Marrowby & Vane 39.4%, Studio Vrij 38.6%, Cragsley 29.8%) to see how much of the original gap survives once the known carrier incidents are removed.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Felix calls dry_run

     SELECT
       tenant_name,
       excluded_period,
       ticket_count_after_exclusion,
       avg_resolution_minutes_after_exclusion,
       resolution_breach_rate_after_exclusion
     FROM (
       SELECT
         ft.tenant_name,
         ft.excluded_period,
         count(*) AS ticket_count_after_exclusion,
         round(avg(extract(epoch from (ft.resolved_at - ft.created_at)) / 60.0)) AS avg_resolution_minutes_after_exclusion,
         round(
           100.0 * sum(
             case when exists (
               select 1 from sla_breaches sb
               where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'
             ) then 1 else 0 end
           ) / count(*),
           1
         ) AS resolution_breach_rate_after_exclusion
       FROM (
         SELECT
           tk.id as ticket_id,
           t.name as tenant_name,
           case
             when t.name = 'Marrowby & Vane' then 'Excludes Nov-Dec 2025'
             when t.name = 'Studio Vrij' then 'Excludes May-Jul 2025'
             else 'None'
           end as excluded_period,
           tk.created_at,
           tk.resolved_at
         FROM tickets tk
         JOIN tenants t ON t.id = tk.tenant_id
         WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters')
           AND tk.created_at >= timestamp '2025-01-01'
           AND tk.created_at < timestamp '2026-01-01'
           AND NOT (
             (t.name = 'Marrowby & Vane' AND tk.created_at >= timestamp '2025-11-01' AND tk.created_at < timestamp '2026-01-01')
             OR
             (t.name = 'Studio Vrij' AND tk.created_at >= timestamp '2025-05-01' AND tk.created_at < timestamp '2025-08-01')
           )
       ) ft
       GROUP BY ft.tenant_name, ft.excluded_period
     ) x
     ORDER BY resolution_breach_rate_after_exclusion DESC, tenant_name

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, excluded_period, ticket_count_after_exclusion, avg_resolution_minutes_after_exclusion, resolution_breach_rate_after_exclusion

   • FelixQuery compiles cleanly with the expected columns. Submitting.
   • Felix calls submit

     SELECT
       tenant_name,
       excluded_period,
       ticket_count_after_exclusion,
       avg_resolution_minutes_after_exclusion,
       resolution_breach_rate_after_exclusion
     FROM (
       SELECT
         ft.tenant_name,
         ft.excluded_period,
         count(*) AS ticket_count_after_exclusion,
         round(avg(extract(epoch from (ft.resolved_at - ft.created_at)) / 60.0)) AS avg_resolution_minutes_after_exclusion,
         round(
           100.0 * sum(
             case when exists (
               select 1 from sla_breaches sb
               where sb.ticket_id = ft.ticket_id and sb.breach_kind = 'resolution'
             ) then 1 else 0 end
           ) / count(*),
           1
         ) AS resolution_breach_rate_after_exclusion
       FROM (
         SELECT
           tk.id as ticket_id,
           t.name as tenant_name,
           case
             when t.name = 'Marrowby & Vane' then 'Excludes Nov-Dec 2025'
             when t.name = 'Studio Vrij' then 'Excludes May-Jul 2025'
             else 'None'
           end as excluded_period,
           tk.created_at,
           tk.resolved_at
         FROM tickets tk
         JOIN tenants t ON t.id = tk.tenant_id
         WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters')
           AND tk.created_at >= timestamp '2025-01-01'
           AND tk.created_at < timestamp '2026-01-01'
           AND NOT (
             (t.name = 'Marrowby & Vane' AND tk.created_at >= timestamp '2025-11-01' AND tk.created_at < timestamp '2026-01-01')
             OR
             (t.name = 'Studio Vrij' AND tk.created_at >= timestamp '2025-05-01' AND tk.created_at < timestamp '2025-08-01')
           )
       ) ft
       GROUP BY ft.tenant_name, ft.excluded_period
     ) x
     ORDER BY resolution_breach_rate_after_exclusion DESC, tenant_name

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • FelixSubmitted. The query pulls 2025 tickets for the three named tenants, drops Marrowby & Vane's Nov–Dec and Studio Vrij's May–Jul tickets, and reports ticket count, average resolution time, and resolution-breach rate per tenant so Ben can compare directly against last week's unadjusted figures.

   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 (268 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (5 KB) · the night's reports (6 KB) · the week's memo (3 KB)