Queries · q399 · Support Operations Manager · week 2, night 1

Macro usage_count audit trail reconciliation

“Can we independently verify what macros.usage_count is actually counting, using the audit trail, before we repeat that number anywhere?”

Manager · asked the questionMia SchultzSupport Operations Manager. Built support tooling and processes at a software company.

Analyst · planned the reportBen CarterQuantifier: puts a money value on every finding.

Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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

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
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
Ben Carter plans the night
The analyst · week 2, night 1, request 2 of 3
Mia's top priority is turning the Marrowby & Vane / Studio Vrij finding into "hire" vs "something else" — so tonight's first slot benchmarks their staffing ratio (tickets per agent) against complexity signals (messages per ticket, priority mix) alongside Cragsley and a few other top merchants as controls. I already have a hint this isn't pure under-staffing: last week's data showed Cragsley's Customer Care group runs a similar or higher tickets-per-agent ratio than Studio Vrij's yet breaches far less, which points toward ticket difficulty rather than headcount — tonight's report will test that properly with complexity proxies instead of my eyeballing two reports side by side. Second, since there's no usage-event ledger behind macros.usage_count, the only way to sanity-check it is to see if the audit trail (change_history/audit_log) corroborates the counter for our top macros — if the audit trail barely covers the counter's growth, that's itself the finding (and echoes the audit-gap theme several other teams hit this week), and I'll say so plainly rather than repeat the unreliable number. Third, a cheap but necessary check before any topic/tag training conversation: how much of 2025 ticket volume carries no tag at all, and whether untagged tickets look different enough to change the story. KB-deflection (Mia's Q4) waits for night 2 since these three fill tonight's slots and matter more.
Macro usage_count audit trail reconciliation
Question
Can we independently verify what macros.usage_count is actually counting, using the audit trail, before we repeat that number anywhere?
One row per
macro, for the 5 highest-usage macros from last week's report
Columns
- macro_title
- current_usage_count (from macros table today)
- logged_update_event_count (rows in change_history where entity_table = 'macros', entity_id = this macro, and changed_columns includes 'usage_count')
- sum_of_logged_increments (sum of after_data.usage_count minus before_data.usage_count across those logged events)
- first_logged_update_at
- last_logged_update_at
- macro_created_at
Ines 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
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC, id
  LIMIT 5
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM((after_data->>'usage_count')::numeric - (before_data->>'usage_count')::numeric) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' = ANY(changed_columns)
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC, tm.id
```
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 and the one deviation flagged to Ben: - Grain: one row per macro, for the 5 macros with the highest current usage_count. - Since the warehouse has no stored copy of last week's report output, I couldn't match by macro id against it — I used today's top 5 by usage_count as the closest available proxy and flagged this in the note so Ben can confirm it's the same set, or supply the prior report's macro ids if not. - For each macro: current_usage_count straight from macros, plus logged_update_event_count, sum_of_logged_increments (sum of after_data.usage_count - before_data.usage_count), and first/last logged update timestamps from change_history (filtered to entity_table = 'macros' and changed_columns containing 'usage_count'), alongside macro_created_at — exactly as requested, so the gap between logged increments and the actual counter will be visible per macro.”

The report comes back overnight

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

macro_title         | current_usage_count | logged_update_event_count | sum_of_logged_increments | first_logged_update_at | last_logged_update_at | macro_created_at   
--------------------+---------------------+---------------------------+--------------------------+------------------------+-----------------------+--------------------
Refund processed    |                1840 |                         0 |                     NULL |                   NULL |                  NULL | 2018-07-03 09:00:00
Refund processed    |                 730 |                         0 |                     NULL |                   NULL |                  NULL | 2024-02-06 10:00:00
Refund processed    |                 697 |                         0 |                     NULL |                   NULL |                  NULL | 2018-09-18 09:00:00
Where is my order   |                 651 |                         0 |                     NULL |                   NULL |                  NULL | 2019-02-15 10:00:00
Return instructions |                 647 |                         0 |                     NULL |                   NULL |                  NULL | 2019-04-03 16:00:00
(5 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q204Macro usage overview

It becomes benchmark query q399

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

edit->> is PostgreSQL's; the standard's JSON_VALUE, which PostgreSQL, MySQL and T-SQL support, returns the same text for a scalar. (docs/RUNNER.md, Dialects)
edit->> is PostgreSQL's; the standard's JSON_VALUE, which PostgreSQL, MySQL and T-SQL support, returns the same text for a scalar. (docs/RUNNER.md, Dialects)
spellingcast (2×)
spellingfetch first
numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed
placeholderequals any
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT  tm.title AS macro_title,  tm.usage_count AS current_usage_count,  COALESCE(ch.event_count, 0) AS logged_update_event_count,  ch.sum_increment AS sum_of_logged_increments,  ch.first_update_at AS first_logged_update_at,  ch.last_update_at AS last_logged_update_at,  tm.created_at AS macro_created_atFROM (  SELECT id, title, usage_count, created_at  FROM macros  ORDER BY usage_count DESC, id  LIMIT 5  ORDER BY usage_count DESC NULLS FIRST, id NULLS LAST  {{fetch_first(5)}}) tmLEFT JOIN (  SELECT    entity_id,    COUNT(*) AS event_count,    SUM((after_data->>'usage_count')::numeric - (before_data->>'usage_count')::numeric) AS sum_increment,    SUM(CAST(JSON_VALUE(after_data, '$.usage_count') AS numeric(18,6)) - CAST(JSON_VALUE(before_data, '$.usage_count') AS numeric(18,6))) AS sum_increment,    MIN(occurred_at) AS first_update_at,    MAX(occurred_at) AS last_update_at  FROM change_history  WHERE entity_table = 'macros'    AND 'usage_count' = ANY(changed_columns)    AND {{equals_any('usage_count', changed_columns)}}  GROUP BY entity_id) ch ON ch.entity_id = tm.idORDER BY tm.usage_count DESC, tm.idORDER BY tm.usage_count DESC NULLS FIRST, tm.id NULLS LAST

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

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC NULLS FIRST, id NULLS LAST
  FETCH FIRST 5 ROWS ONLY
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM(CAST(JSON_VALUE(after_data, '$.usage_count') AS numeric(18,6)) - CAST(JSON_VALUE(before_data, '$.usage_count') AS numeric(18,6))) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' IN (SELECT dbo_unnest.dbo_element FROM UNNEST(changed_columns) AS dbo_unnest(dbo_element))
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC NULLS FIRST, tm.id NULLS LAST

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC NULLS FIRST, id NULLS LAST
  FETCH FIRST 5 ROWS ONLY
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM(CAST(JSON_VALUE(after_data, '$.usage_count') AS numeric(18,6)) - CAST(JSON_VALUE(before_data, '$.usage_count') AS numeric(18,6))) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' = ANY(CAST('[' || SUBSTRING(changed_columns FROM 2 FOR CHAR_LENGTH(changed_columns) - 2) || ']' AS text[]))
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC NULLS FIRST, tm.id NULLS LAST

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC NULLS FIRST, id NULLS LAST
  LIMIT 5
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM(CAST(JSON_VALUE(after_data, '$.usage_count') AS numeric(18,6)) - CAST(JSON_VALUE(before_data, '$.usage_count') AS numeric(18,6))) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' IN (SELECT dbo_unnest.dbo_element FROM UNNEST(changed_columns) AS dbo_unnest(dbo_element))
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC NULLS FIRST, tm.id NULLS LAST

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC NULLS FIRST, id NULLS LAST
  FETCH FIRST 5 ROWS ONLY
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM(CAST(JSON_VALUE(after_data, '$.usage_count') AS numeric(18,6)) - CAST(JSON_VALUE(before_data, '$.usage_count') AS numeric(18,6))) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND CASE WHEN changed_columns IS NULL THEN NULL WHEN empty(changed_columns) THEN false WHEN 'usage_count' IS NULL THEN NULL WHEN has(changed_columns, 'usage_count') THEN true WHEN has(changed_columns, NULL) THEN NULL ELSE false END
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC NULLS FIRST, tm.id NULLS LAST

HyperJSON_VALUE is not implemented yet
Firebolt{ "errors": [ { "description": "Line 19, Column 14: function signature 'json_value(json, unknown)' not found, supported signatures are json_value(json => text), json_value(json => json)" } ], "query": { "query_id": "76927738-0b7c-48bd-8511-4c6a148efd71", "query_label": null, "request_id": "e361818d-
DataFusionError during planning: Invalid function 'json_value'.

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

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

Ines's whole session, 6 turns

The request, as Ines received it

Report request from Ben Carter (analyst for the Support Operations Manager):

Title: Macro usage_count audit trail reconciliation
Question: Can we independently verify what macros.usage_count is actually counting, using the audit trail, before we repeat that number anywhere?
Grain: one row per macro, for the 5 highest-usage macros from last week's report
Columns:
- macro_title
- current_usage_count (from macros table today)
- logged_update_event_count (rows in change_history where entity_table = 'macros', entity_id = this macro, and changed_columns includes 'usage_count')
- sum_of_logged_increments (sum of after_data.usage_count minus before_data.usage_count across those logged events)
- first_logged_update_at
- last_logged_update_at
- macro_created_at
Details: Source tables: macros, change_history (entity_schema/entity_table = 'macros', filter changed_columns array containing 'usage_count'; parse before_data/after_data json for the usage_count field). No date window — we want the full history since macro_created_at. Pick the same 5 macros as the top rows of last week's 'Macro usage overview' report (by tenant + title, since titles repeat across tenants — use the macro id if you have it, otherwise match on title + usage_count from that report). Order by current_usage_count descending. Goal: see whether sum_of_logged_increments and logged_update_event_count come anywhere close to explaining current_usage_count, or whether most of the counter's growth has no audit trail behind it at all — flag either way.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Ines calls dry_run

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC, id
  LIMIT 5
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM((after_data->>'usage_count')::numeric - (before_data->>'usage_count')::numeric) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' = ANY(changed_columns)
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC, tm.id

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: macro_title, current_usage_count, logged_update_event_count, sum_of_logged_increments, first_logged_update_at, last_logged_update_at, macro_created_at

Ines calls submit

SELECT
  tm.title AS macro_title,
  tm.usage_count AS current_usage_count,
  COALESCE(ch.event_count, 0) AS logged_update_event_count,
  ch.sum_increment AS sum_of_logged_increments,
  ch.first_update_at AS first_logged_update_at,
  ch.last_update_at AS last_logged_update_at,
  tm.created_at AS macro_created_at
FROM (
  SELECT id, title, usage_count, created_at
  FROM macros
  ORDER BY usage_count DESC, id
  LIMIT 5
) tm
LEFT JOIN (
  SELECT
    entity_id,
    COUNT(*) AS event_count,
    SUM((after_data->>'usage_count')::numeric - (before_data->>'usage_count')::numeric) AS sum_increment,
    MIN(occurred_at) AS first_update_at,
    MAX(occurred_at) AS last_update_at
  FROM change_history
  WHERE entity_table = 'macros'
    AND 'usage_count' = ANY(changed_columns)
  GROUP BY entity_id
) ch ON ch.entity_id = tm.id
ORDER BY tm.usage_count DESC, tm.id

The warehouse answers

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

InesSubmitted. Summary of what the report does and the one deviation flagged to Ben: - **Grain:** one row per macro, for the 5 macros with the highest current `usage_count`. - Since the warehouse has no stored copy of last week's report output, I couldn't match by macro id against it — I used today's top 5 by `usage_count` as the closest available proxy and flagged this in the note so Ben can confirm it's the same set, or supply the prior report's macro ids if not. - For each macro: `current_usage_count` straight from `macros`, plus `logged_update_event_count`, `sum_of_logged_increments` (sum of `after_data.usage_count - before_data.usage_count`), and first/last logged update timestamps from `change_history` (filtered to `entity_table = 'macros'` and `changed_columns` containing `'usage_count'`), alongside `macro_created_at` — exactly as requested, so the gap between logged increments and the actual counter will be visible per macro.

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

Ines's transcript (267 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (5 KB) · the night's reports (5 KB) · the week's memo (3 KB)

Macro usage_count audit trail reconciliation

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

Mia Schultz writes to Ben Carter

Ben Carter plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q399

Everything, as recorded