DB Olympics

Queries · q280 · Head of Risk and Payments · week 2, night 2

Flagged compliance/security incidents: volume, severity and resolution time

“How many compliance/security incidents have been flagged, how severe are they, how long do they typically take to resolve, and is anything still sitting open a long time?”

Manager · asked the questionAylin DemirHead of Risk and Payments. Previously in fraud operations at a bank. Responsible for payment performance and for keeping bad actors out.
Analyst · planned the reportClaire DuboisTime-series minded: weekly and daily trends, before and after, seasonality.
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

  1. A business event lands on the Head of Risk and Payments's desk

    Week 1; shared with Rosa Van den Berg, Head of Data

    An enterprise prospect sent a security and compliance questionnaire covering account security, access control, privacy requests, data retention and audit trails. Sales wants answers backed by evidence from our own systems, not by policy documents.

    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. Aylin Demir writes to Claire Dubois

    The manager, at the start of week 2

    Subject: Re: Questionnaire memo — good work, a few threads to pull

    Claire,

    This is a strong memo, and the gaps you flagged are exactly the kind of thing I'd rather surface now than have a prospect's security team find later. A few things stand out and I want to understand them better before we finalize anything.

    The February 2025 login spike is the one that worries me most. A 10x jump in failures with no corresponding rise in flagged suspicious activity is a real detection gap, not just a reporting curiosity — if that was a credential-stuffing attempt, I need to know whether it actually got anywhere.

    Questions for this week:

    1. On the February 2025 spike — did it cluster around a handful of accounts, merchants, or sources, or was it spread evenly? And did any of the accounts that failed repeatedly go on to log in successfully shortly after? That tells us whether this was noise or an actual near-miss.
    2. On the three unresolved rejection dates — rather than retrying the same technical path, can we establish, using whatever reliable timestamp we do have, roughly when each decision was made relative to the request date? I don't need the exact figure if it's not retrievable — I need to know whether we were plausibly within the deadline or not, so we can decide how to word that answer.
    3. On flagged incidents (the one we deferred) — now that we're another week in, I want this one: how many incidents have been flagged, how severe, how long they typically take to close, and whether anything has sat open a long time. This is likely to come up in the same breath as the login spike.
    4. On the tables with no retention policy — before I escalate this as a gap, I want to know how exposed we actually are: roughly how far back does that data go today? If it's a few months, this is a minor cleanup; if it's three years, it's a bigger conversation with Rosa.

    Keep the role/permission logging gap and the broken query as noted — no need to keep spending slots chasing those; I'll raise the first with engineering myself and we can note the second as a known limitation in the memo.

    Thanks, Aylin

  3. Claire Dubois plans the night

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

    Night 1 results need a careful read before I pass anything to Aylin:

    Report 1 (concentration by merchant) is clean and striking: 3 merchants — Cragsley Outfitters (4,407 failed, 88.1% fail rate), Marrowby & Vane (1,978 failed, 86.1%), Studio Vrij (783 failed, 84.2%) — account for ~7,168 of the ~7,429 failed attempts we found last week (96.5%), while the next-highest merchant had just 17 failed attempts. These are the exact same three merchants Support Ops called out last week as having both the heaviest ticket volume and worst response times. But the pattern doesn't look like a credential-stuffing attack: distinct_emails_with_any_failed_attempt is nearly as high as total failed attempts (e.g. Cragsley: 2,786 distinct emails for 4,407 fails, max 8 fails on any single email, max 9 from any single IP, spread across 1,903 distinct IPs). That's wide, shallow failure across many accounts and many sources at once — much more consistent with a broken login flow/client bug affecting those 3 merchants specifically than with an external attack.

    Report 2 (near-miss outcomes) is internally broken and I will not pass these numbers on: percentages are computed against a fixed denominator of 102, but the bucket counts (42+12+89+452=595) don't come close to summing to 102, and the engineer's note confirms email was matched globally rather than scoped per tenant (login_attempts.email is only unique per tenant) — so the "cohort" silently grew across tenants mid-query while the percentage base didn't update. This is exactly the kind of actor/identity mismatch Rosa flagged in week 1, now showing up in a different table. I'm retrying tonight with tenant-scoped (tenant_id, email) pairs and mutually-exclusive buckets that must sum to the true cohort size.

    Report 3 (DSAR via updated_at) looks solid and resolves Aylin's question #2: all 3 rejected DSARs show updated_at well inside their 30-day due_at window (8.7, 3.8, and 23.7 days after receipt respectively) — plausibly within deadline, with the usual caveat that updated_at isn't a verified "decision" timestamp.

    Tonight (3 slots): (1) retry the near-miss report correctly; (2) incidents/compliance_events volume, severity, and resolution time (question #3, deferred from last week); (3) how far back the five retention-policy-less tables actually go, to size the exposure for question #4.

    Flagged compliance/security incidents: volume, severity and resolution time
    Question
    How many compliance/security incidents have been flagged, how severe are they, how long do they typically take to resolve, and is anything still sitting open a long time?
    One row per
    compliance_events.severity value, across all tenants, all occurred_at dates from 2023-01-01 through 2025-12-31
    Columns
    • severity
    • total_events
    • resolved_count
    • unresolved_count
    • pct_resolved
    • avg_days_to_resolve (resolved_at - occurred_at, resolved events only)
    • median_days_to_resolve
    • oldest_unresolved_age_days (2025-12-31 minus occurred_at, for the oldest unresolved event in this severity)
    • distinct_event_types

  4. Omar 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
  severity,
  count(*) AS total_events,
  count(*) FILTER (WHERE resolved) AS resolved_count,
  count(*) FILTER (WHERE NOT resolved) AS unresolved_count,
  round(100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0), 1) AS pct_resolved,
  round(avg(extract(epoch FROM (resolved_at - occurred_at)) / 86400.0) FILTER (WHERE resolved), 1) AS avg_days_to_resolve,
  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,
  round(
    max(CASE WHEN NOT resolved THEN extract(epoch FROM (timestamp '2025-12-31' - occurred_at)) / 86400.0 END)::numeric,
    0
  ) AS oldest_unresolved_age_days,
  count(DISTINCT event_type) AS distinct_event_types
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY severity
ORDER BY total_events DESC, severity

    “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 report returns one row per compliance_events.severity with total/resolved/unresolved counts, resolution-rate and resolution-time stats (mean and median days), the oldest unresolved incident's age as of 2025-12-31, and the count of distinct event types — ordered by total volume descending.”

  5. The report comes back overnight

    As Claire Dubois saw it, from the small dataset the agents worked on

    severity | total_events | resolved_count | unresolved_count | pct_resolved | avg_days_to_resolve | median_days_to_resolve | oldest_unresolved_age_days | distinct_event_types
---------+--------------+----------------+------------------+--------------+---------------------+------------------------+----------------------------+---------------------
high     |            4 |              4 |                0 |        100.0 |                 7.2 |                    9.6 |                       NULL |                    2
medium   |            4 |              4 |                0 |        100.0 |                17.9 |                   19.4 |                       NULL |                    1
(2 rows)

  6. It becomes benchmark query q280

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

    • spellingcast (2×)
    • 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) (3×)
    • placeholderpercentile cont
    • placeholderseconds between (3×)
    • 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  severity,  count(*) AS total_events,  count(*) FILTER (WHERE resolved) AS resolved_count,  count(*) FILTER (WHERE NOT resolved) AS unresolved_count,  round(100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0), 1) AS pct_resolved,  round(avg(extract(epoch FROM (resolved_at - occurred_at)) / 86400.0) FILTER (WHERE resolved), 1) AS avg_days_to_resolve,  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,  round(    max(CASE WHEN NOT resolved THEN extract(epoch FROM (timestamp '2025-12-31' - occurred_at)) / 86400.0 END)::numeric,    0  ) AS oldest_unresolved_age_days,  100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0) AS pct_resolved,  avg({{seconds_between(occurred_at, resolved_at)}} / 86400.0) FILTER (WHERE resolved) AS avg_days_to_resolve,  ({{percentile_cont_filter(0.5, {{seconds_between(occurred_at, resolved_at)}} / 86400.0, resolved)}}) AS median_days_to_resolve,  max(CASE WHEN NOT resolved THEN {{seconds_between(occurred_at, timestamp '2025-12-31')}} / 86400.0 END) AS oldest_unresolved_age_days,  count(DISTINCT event_type) AS distinct_event_typesFROM compliance_eventsWHERE occurred_at >= timestamp '2023-01-01'  AND occurred_at < timestamp '2026-01-01'GROUP BY severityORDER BY total_events DESC, severityORDER BY total_events DESC NULLS FIRST, severity NULLS LAST

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

    SELECT
  severity,
  count(*) AS total_events,
  count(*) FILTER (WHERE resolved) AS resolved_count,
  count(*) FILTER (WHERE NOT resolved) AS unresolved_count,
  100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0) AS pct_resolved,
  avg(CAST(EXTRACT(EPOCH FROM (resolved_at - occurred_at)) AS NUMERIC(18,6)) / 86400.0) FILTER (WHERE resolved) AS avg_days_to_resolve,
  (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,
  max(CASE WHEN NOT resolved THEN CAST(EXTRACT(EPOCH FROM (timestamp '2025-12-31' - occurred_at)) AS NUMERIC(18,6)) / 86400.0 END) AS oldest_unresolved_age_days,
  count(DISTINCT event_type) AS distinct_event_types
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY severity
ORDER BY total_events DESC NULLS FIRST, severity NULLS LAST
    1 ms10 ms100 msHyper8.1 msDuckDB5.9 msGizmoSQL9.7 msclickhouse_brew37.3 msFirebolt12.0 msDataFusion29.0 msClickHouse23.2 ms

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

    Omar's whole session, 7 turns
    • The request, as Omar received it
      Report request from Claire Dubois (analyst for the Head of Risk and Payments):

Title: Flagged compliance/security incidents: volume, severity and resolution time
Question: How many compliance/security incidents have been flagged, how severe are they, how long do they typically take to resolve, and is anything still sitting open a long time?
Grain: one row per compliance_events.severity value, across all tenants, all occurred_at dates from 2023-01-01 through 2025-12-31
Columns:
- severity
- total_events
- resolved_count
- unresolved_count
- pct_resolved
- avg_days_to_resolve (resolved_at - occurred_at, resolved events only)
- median_days_to_resolve
- oldest_unresolved_age_days (2025-12-31 minus occurred_at, for the oldest unresolved event in this severity)
- distinct_event_types
Details: Source: compliance_events. Use 2025-12-31 as the fixed reference date for age calculations (not CURRENT_DATE). This is the deferred 'incidents' question from last week — want a clear read on total volume, how severity breaks down, typical time-to-close, and whether any severity tier has old unresolved events that would look bad to a security reviewer. Order by total_events descending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      SELECT
  severity,
  count(*) AS total_events,
  count(*) FILTER (WHERE resolved) AS resolved_count,
  count(*) FILTER (WHERE NOT resolved) AS unresolved_count,
  round(100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0), 1) AS pct_resolved,
  round(avg(extract(epoch FROM (resolved_at - occurred_at)) / 86400.0) FILTER (WHERE resolved), 1) AS avg_days_to_resolve,
  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,
  round(
    max(CASE WHEN NOT resolved THEN extract(epoch FROM (timestamp '2025-12-31' - occurred_at)) / 86400.0 END)::numeric,
    0
  ) AS oldest_unresolved_age_days,
  count(DISTINCT event_type) AS distinct_event_types
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY severity
ORDER BY total_events DESC, severity
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: severity, total_events, resolved_count, unresolved_count, pct_resolved, avg_days_to_resolve, median_days_to_resolve, oldest_unresolved_age_days, distinct_event_types
    • OmarQuery compiles cleanly with the expected columns. Submitting.
    • Omar calls submit
      SELECT
  severity,
  count(*) AS total_events,
  count(*) FILTER (WHERE resolved) AS resolved_count,
  count(*) FILTER (WHERE NOT resolved) AS unresolved_count,
  round(100.0 * count(*) FILTER (WHERE resolved) / NULLIF(count(*), 0), 1) AS pct_resolved,
  round(avg(extract(epoch FROM (resolved_at - occurred_at)) / 86400.0) FILTER (WHERE resolved), 1) AS avg_days_to_resolve,
  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,
  round(
    max(CASE WHEN NOT resolved THEN extract(epoch FROM (timestamp '2025-12-31' - occurred_at)) / 86400.0 END)::numeric,
    0
  ) AS oldest_unresolved_age_days,
  count(DISTINCT event_type) AS distinct_event_types
FROM compliance_events
WHERE occurred_at >= timestamp '2023-01-01'
  AND occurred_at < timestamp '2026-01-01'
GROUP BY severity
ORDER BY total_events DESC, severity
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. The report returns one row per `compliance_events.severity` with total/resolved/unresolved counts, resolution-rate and resolution-time stats (mean and median days), the oldest unresolved incident's age as of 2025-12-31, and the count of distinct event types — ordered by total volume descending.

    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 (262 KB) · Claire Dubois's transcript (838 KB) · Aylin Demir's transcript (263 KB) · the night's plan (6 KB) · the night's reports (4 KB) · the week's memo (3 KB)