Queries · q114 · Data Protection Officer · week 1, night 1

Compliance events: monthly volume and resolution time by severity

“How many compliance incidents are being flagged each month by severity, how quickly are they resolved, and is an unresolved backlog building up?”

Manager · asked the questionInes LaurentData Protection Officer. Lawyer by training, previously at a European marketplace. Also reports to the board's audit committee.

Analyst · planned the reportPriyanka DesaiAuditor's mindset: traces individual records end to end to check that the totals are real.

Engineer · wrote the SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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

Ines Laurent writes to Priyanka Desai
The manager, at the start of week 1
Subject: A few starting questions on privacy & data handling

Hi Priyanka,

Welcome aboard, and thank you for taking these on this week. As I get oriented, here's what I'd like to understand first. Given the slot constraints, please treat these as priorities in order — if only three fit this week, start from the top and we'll pick up the rest next week.
1. How are we doing on privacy requests from shoppers? I want to know how many access/erasure/portability requests we're getting, how long they typically take to close, and whether any are overdue. This is the thing the board audit committee will ask about first, and it's also a legal deadline risk, so I need a realistic picture, not just a snapshot.
2. What does our consent picture look like? I'd like a sense of how many of our users have actually opted in versus withdrawn or never given consent, and whether that's changed meaningfully over time. This tells me how exposed we are if we rely on consent for marketing or data use.
3. Are there open compliance issues sitting unresolved, and how serious are they? I want to understand the volume and severity of flagged incidents (things like suspicious data access or retention problems) and how quickly they tend to get closed out. I need to know if we have a backlog building up anywhere.
4. How long are we actually holding onto shopper data, and does it match what our retention rules say it should be? I'm less interested in the rulebook and more in whether reality matches it — are there data types we're sitting on well past when we said we would delete them?
5. Are there any legal holds in place right now, and what do they cover? I want to know how many are active, what they're blocking, and whether any look stale (i.e., should have been released a while ago). This matters for both litigation readiness and for explaining why certain deletions haven't happened.
For now, could you scope the first request for me? I'd like to see it broken down in a way that shows me trend over the last several months, not just a single total — I want to catch whether we're improving or falling behind.

Let me know if any of these need to be split further once you see what the data looks like.

Thanks, Ines
Priyanka Desai plans the night
The analyst · week 1, night 1, request 3 of 3
Ines asked me to scope item 1 first (DSAR volume/closure time/overdue, trended monthly) but also flagged items 2 and 3 as high priority and well-defined enough to start in parallel, since the week is tight (5 topics, 4 nights). None of these three depend on each other's results, so I'm running all as independent first-look reports tonight rather than waiting. I kept each to one grain and used a dimension-as-row-grain (purpose, severity) instead of guessing at unknown code values, per the carrier/month pattern in the handbook. Items 4 (retention vs. policy) and 5 (legal holds) are intentionally deferred to a later night since they need review of retention_policies/legal_holds structure first and aren't part of what Ines asked me to scope tonight.
Compliance events: monthly volume and resolution time by severity
Question
How many compliance incidents are being flagged each month by severity, how quickly are they resolved, and is an unresolved backlog building up?
One row per
calendar month of detection (compliance_events.occurred_at) and severity level
Columns
- month (YYYY-MM)
- severity
- incidents detected
- resolved count (resolved = true, as of 2026-01-05)
- unresolved count (resolved = false, as of 2026-01-05)
- median days to resolve (resolved_at - occurred_at, in days, for resolved=true rows only)
- unresolved and older than 30 days (resolved=false AND 2026-01-05 - occurred_at > 30 days)

Omar writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: REJECTED

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  round(
    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,
    1
  ) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

SELECT  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,  strftime(date_trunc('month', occurred_at), '%Y-%m') as month,  severity,  count(*) as incidents_detected,  count(*) filter (where resolved) as resolved_count,  count(*) filter (where not resolved) as unresolved_count,  round(    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,    1  ) as median_days_to_resolve,  count(*) filter (    where not resolved      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'  ) as unresolved_over_30dFROM compliance_eventsWHERE occurred_at >= timestamp '2023-01-01'  AND occurred_at < timestamp '2026-01-01'GROUP BY 1, 2ORDER BY 1, 2

“This compiles correctly with all requested columns. 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 groups compliance_events by detection month (2023-01 through 2025-12) and the raw severity values, with detected/resolved/unresolved counts, the median resolution time in days for resolved incidents, and a backlog count of unresolved incidents older than 30 days as of 2026-01-05.”

The report comes back overnight

As Priyanka Desai saw it, from the small dataset the agents worked on

month   | severity | incidents_detected | resolved_count | unresolved_count | median_days_to_resolve | unresolved_over_30d
--------+----------+--------------------+----------------+------------------+------------------------+--------------------
2023-06 | medium   |                  1 |              1 |                0 |                    3.7 |                   0
2023-10 | medium   |                  1 |              1 |                0 |                   18.1 |                   0
2024-03 | medium   |                  1 |              1 |                0 |                   20.7 |                   0
2025-02 | high     |                  3 |              3 |                0 |                    9.6 |                   0
2025-02 | medium   |                  1 |              1 |                0 |                   29.2 |                   0
2025-10 | high     |                  1 |              1 |                0 |                    0.1 |                   0
(6 rows)

It becomes benchmark query q114

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

editSubtracting two timestamps without an interval qualifier is not standard; the same condition as a timestamp minus a standard interval literal, which PostgreSQL and MySQL support. (docs/RUNNER.md, Dialects)
spellingcast
placeholderyear month
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
placeholderpercentile cont
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  strftime(date_trunc('month', occurred_at), '%Y-%m') as month,  {{year_month(date_trunc('month', occurred_at))}} as month,  severity,  count(*) as incidents_detected,  count(*) filter (where resolved) as resolved_count,  count(*) filter (where not resolved) as unresolved_count,  round(    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,    1  ) as median_days_to_resolve,  ({{percentile_cont_filter(0.5, {{seconds_between(occurred_at, resolved_at)}}/86400.0, resolved)}}) as median_days_to_resolve,  count(*) filter (    where not resolved      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY  ) as unresolved_over_30dFROM compliance_eventsWHERE occurred_at >= timestamp '2023-01-01'  AND occurred_at < timestamp '2026-01-01'GROUP BY 1, 2ORDER BY 1, 2ORDER BY 1 NULLS LAST, 2 NULLS LAST

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

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6))/86400.0) FILTER (WHERE resolved)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN resolved THEN CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6))/86400.0 END)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

SELECT
  to_char(CAST(date_trunc('month', occurred_at) AS timestamp), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6))/86400.0) FILTER (WHERE resolved)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6))/86400.0) FILTER (WHERE resolved)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6))/86400.0 AS DOUBLE PRECISION)) FILTER (WHERE resolved)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

SELECT
  formatDateTime(date_trunc('month', occurred_at), '%Y-%m') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  (quantileExactInclusiveIf(0.5)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', occurred_at, resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400.0), resolved)) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and occurred_at < TIMESTAMP '2026-01-05' - INTERVAL '30' DAY
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1 NULLS LAST, 2 NULLS LAST

DataFusionrow count 38 vs 2

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

Omar's whole session, 9 turns

The request, as Omar received it

Report request from Priyanka Desai (analyst for the Data Protection Officer):

Title: Compliance events: monthly volume and resolution time by severity
Question: How many compliance incidents are being flagged each month by severity, how quickly are they resolved, and is an unresolved backlog building up?
Grain: one row per calendar month of detection (compliance_events.occurred_at) and severity level
Columns:
- month (YYYY-MM)
- severity
- incidents detected
- resolved count (resolved = true, as of 2026-01-05)
- unresolved count (resolved = false, as of 2026-01-05)
- median days to resolve (resolved_at - occurred_at, in days, for resolved=true rows only)
- unresolved and older than 30 days (resolved=false AND 2026-01-05 - occurred_at > 30 days)
Details: Source: compliance_events. Group by date_trunc('month', occurred_at) (or join calendar_days) and by the severity column as-is (do not assume specific severity strings — use whatever distinct values are present). Use the literal date 2026-01-05 as 'today' for all age/backlog calculations (never CURRENT_DATE/now()). Span 2023-01 through 2025-12. Platform-wide, all tenants. Order by month ascending, then severity.

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

Omar calls dry_run

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  round(
    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,
    1
  ) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Omar calls dry_run

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  round(
    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,
    1
  ) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, severity, incidents_detected, resolved_count, unresolved_count, median_days_to_resolve, unresolved_over_30d

OmarThis compiles correctly with all requested columns. Submitting now.

Omar calls submit

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') as month,
  severity,
  count(*) as incidents_detected,
  count(*) filter (where resolved) as resolved_count,
  count(*) filter (where not resolved) as unresolved_count,
  round(
    (percentile_cont(0.5) within group (order by extract(epoch from (resolved_at - occurred_at))/86400.0) filter (where resolved))::numeric,
    1
  ) as median_days_to_resolve,
  count(*) filter (
    where not resolved
      and (timestamp '2026-01-05' - occurred_at) > interval '30 days'
  ) as unresolved_over_30d
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

The warehouse answers

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

OmarSubmitted. The report groups `compliance_events` by detection month (2023-01 through 2025-12) and the raw `severity` values, with detected/resolved/unresolved counts, the median resolution time in days for resolved incidents, and a backlog count of unresolved incidents older than 30 days as of 2026-01-05.

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

Omar's transcript (266 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (5 KB) · the night's reports (19 KB) · the week's memo (3 KB)

Compliance events: monthly volume and resolution time by severity

Ines Laurent writes to Priyanka Desai

Priyanka Desai plans the night

Omar writes the SQL

The report comes back overnight

It becomes benchmark query q114

Everything, as recorded