Queries · q068 · Head of Risk and Payments · week 1, night 1

Audit log volume and actor identification by month

“Is our audit trail continuous over time with no unexplained gaps, and are logged actions reliably tied to an identifiable actor rather than null/system placeholders?”

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 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 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
Aylin Demir writes to Claire Dubois
The manager, at the start of week 1
Subject: Evidence we need for the security/compliance questionnaire (this week's priority)

Hi Claire,

This questionnaire is going to a serious enterprise prospect, and Sales wants it backed by evidence from our own systems, not by our policy wording. Rosa and I are co-owning this, so your reports will feed both the questionnaire answers themselves and a broader readiness review Rosa's side is doing. Given you only get a handful of report slots a night, let's be selective and prioritize what a security reviewer will actually probe on. In my own words, here's what I need to know:
1. Who can get into a tenant's account, and how tightly is that controlled? I want a sense of how access is granted and revoked in practice — e.g. how many people hold elevated/admin-type access, whether access grants look current and reviewed, and whether we can show that departed or inactive staff lose access promptly.
2. Do we actually enforce strong login security, or does it just sit in a policy doc? I'd like evidence on real login behavior — things like how much of our staff and customer access is protected by extra verification steps, and whether we can see failed-login and suspicious-access patterns being caught rather than ignored.
3. When someone asks us to access, correct, or delete their data, do we actually meet our own deadlines? This is the one I most expect to be pressed on. I want to see how data subject requests are tracked end to end — how many come in, how quickly they're handled, and whether any are overdue or slipping past the commitments we've made.
4. Is there a real, tamper-evident trail of who did what, and is it kept long enough to be useful? I want evidence that meaningful changes and sensitive data access are actually being logged consistently (not just in theory), and that we're not quietly losing history before our stated retention period is up.
5. If something does go wrong, do we notice? I'd like a sense of how many flagged security or compliance incidents we've had, how quickly they get resolved, and whether any are sitting open for a long time — that's the kind of thing a prospect's security team will zero in on.
Given the slot constraints, please treat questions 3 and 1 as the top two for this week — those are the ones Sales says the prospect calls out by name. If we only get through three of the five, drop 5 first and fold it into next week.

One more thing: since this report touches account security and personal data, please keep the output at a safe aggregate level — no need to name individual customers or staff unless I specifically ask.

Let's sync briefly before you lock in Thursday night's batch so nothing gets wasted on a bounced or bundled request.

Thanks, Aylin
A note from the Head of Data, Rosa Van den Berg
Hi Claire,

Before we hand evidence to this prospect, I'd want us to be confident the underlying records are actually solid enough to stand behind in writing. A few things from my side:
1. Completeness and continuity of the audit trail itself – do we have reason to believe the security/access logs are gap-free over the period we'd quote, or are there known stretches where logging was thin, delayed, or archived in a way that could make "no incidents" look like "no visibility"?
2. One actor, one identity – when we pull evidence on who accessed or changed what, can we actually tie an action back to a single, consistent user record, or do duplicate/merged accounts and inconsistent actor references risk us over- or under-counting who touched sensitive data?
3. Timestamp trust – for anything time-bound (response times on data requests, retention windows, how quickly access was revoked), are we confident the timestamps we'd cite are clean and consistently recorded, rather than something we'd need to caveat?
If any of these raise flags on your end, I'd rather we know before it goes into a customer-facing answer than after.

Thanks, Rosa
Claire Dubois plans the night
The analyst · week 1, night 1, request 3 of 3
Aylin flagged DSAR timeliness (#3) and access control currency (#1) as the top two priorities this week, named specifically by the prospect. I'm spending all 3 slots tonight on: (a) the DSAR timeliness report, since it's the single most-probed item and a time series fits my working style; (b) a staff access-currency snapshot (elevated/system roles by account status, including whether deactivated/suspended staff still hold grants) to start on priority #1; and (c) a foundational data-quality check Rosa asked for — audit trail volume/continuity and actor-identification completeness by month — because every later report on logging, retention, and incidents this week depends on trusting that the audit trail has no silent gaps and that actions tie back to identifiable actors. I'm deliberately not touching MFA/login-security (#2) or incidents (#4/#5) tonight per Aylin's steer to drop #5 first and prioritize #1/#3; I'll plan #2 and #4 for a later night pending Thursday's sync with Aylin. All reports are kept at aggregate level (no named individuals/tenants) per Aylin's privacy instruction.
Audit log volume and actor identification by month
Question
Is our audit trail continuous over time with no unexplained gaps, and are logged actions reliably tied to an identifiable actor rather than null/system placeholders?
One row per
calendar month (based on occurred_at), combining live audit_log and audit_log_archive, from 2023-01 through 2025-12
Columns
- month (YYYY-MM)
- audit_log_live_events (count from audit_log with occurred_at in month)
- audit_log_archived_events (count from audit_log_archive with occurred_at in month)
- total_audit_events (sum of the two)
- pct_actor_user_id_null (share of combined rows where actor_user_id is null)
- distinct_actor_type_values (count distinct actor_type across combined rows in month)
- pct_actor_type_system_or_api (share where actor_type in ('system','api_key','service_account') rather than 'user')
- access_log_events (count from access_log with occurred_at in month, as a separate sensitive-data-read signal)

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)

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
live AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS live_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS live_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS live_system_api
  FROM audit_log
  GROUP BY 1
),
archive AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS archived_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS archived_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS archived_system_api
  FROM audit_log_archive
  GROUP BY 1
),
actor_types AS (
  SELECT month_start, actor_type
  FROM (
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log
    UNION ALL
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log_archive
  ) t
  GROUP BY month_start, actor_type
),
distinct_actor_types AS (
  SELECT month_start, count(*) AS distinct_actor_type_values
  FROM actor_types
  GROUP BY month_start
),
access AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start, count(*) AS access_events
  FROM access_log
  GROUP BY 1
)
SELECT
  m.month_label AS month,
  coalesce(l.live_events,0) AS audit_log_live_events,
  coalesce(a.archived_events,0) AS audit_log_archived_events,
  coalesce(l.live_events,0) + coalesce(a.archived_events,0) AS total_audit_events,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_user_id_null,
  coalesce(dat.distinct_actor_type_values,0) AS distinct_actor_type_values,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_type_system_or_api,
  coalesce(acc.access_events,0) AS access_log_events
FROM months m
LEFT JOIN live l ON l.month_start = m.month_start
LEFT JOIN archive a ON a.month_start = m.month_start
LEFT JOIN distinct_actor_types dat ON dat.month_start = m.month_start
LEFT JOIN access acc ON acc.month_start = m.month_start
ORDER BY m.month_start

“Query compiles and returns exactly the requested 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 gives one row per month (2023-01 through 2025-12), with live/archived/total audit event counts, the share of rows missing actor_user_id, the count of distinct actor_type values seen, the share tagged as system/api_key/service_account, and a parallel access_log event count — all computed platform-wide so Claire can scan for suspiciously low-volume months (gaps) or rising non-user actor shares (identity weakness) before citing the audit trail in the questionnaire.”

The report comes back overnight

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

month   | audit_log_live_events | audit_log_archived_events | total_audit_events | pct_actor_user_id_null | distinct_actor_type_values | pct_actor_type_system_or_api | access_log_events
--------+-----------------------+---------------------------+--------------------+------------------------+----------------------------+------------------------------+------------------
2023-01 |                     0 |                      2869 |               2869 |                 0.0784 |                          2 |                       0.0784 |               143
2023-02 |                     0 |                      2761 |               2761 |                 0.0699 |                          2 |                       0.0699 |               162
2023-03 |                     0 |                      3091 |               3091 |                 0.0754 |                          2 |                       0.0754 |               145
2023-04 |                     0 |                      2991 |               2991 |                 0.0809 |                          3 |                       0.0796 |               156
2023-05 |                     0 |                      3118 |               3118 |                 0.0834 |                          3 |                       0.0827 |               154
2023-06 |                     0 |                      3152 |               3152 |                 0.0853 |                          3 |                       0.0841 |               184
2023-07 |                     0 |                      3113 |               3113 |                 0.0887 |                          2 |                       0.0887 |               141
2023-08 |                     0 |                      3751 |               3751 |                 0.1682 |                          3 |                       0.1674 |               169
2023-09 |                     0 |                      3412 |               3412 |                 0.0709 |                          3 |                       0.0703 |               157
2023-10 |                     0 |                      3138 |               3138 |                 0.0822 |                          3 |                       0.0816 |               189
2023-11 |                     0 |                      3391 |               3391 |                 0.0767 |                          3 |                       0.0761 |               245
2023-12 |                  3589 |                       142 |               3731 |                 0.0718 |                          3 |                       0.0713 |               312
2024-01 |                  3731 |                         0 |               3731 |                 0.0759 |                          3 |                       0.0742 |               200
2024-02 |                  8448 |                         0 |               8448 |                  0.129 |                          3 |                       0.1287 |               170
2024-03 |                  4067 |                         0 |               4067 |                 0.1611 |                          2 |                       0.1611 |               181
2024-04 |                  3474 |                         0 |               3474 |                 0.0832 |                          3 |                       0.0818 |               204
2024-05 |                  3688 |                         0 |               3688 |                 0.0808 |                          3 |                       0.0803 |               201
2024-06 |                  3588 |                         0 |               3588 |                   0.08 |                          3 |                       0.0789 |               212
2024-07 |                  3991 |                         0 |               3991 |                 0.0677 |                          3 |                       0.0666 |               212
2024-08 |                  4273 |                         0 |               4273 |                 0.1495 |                          3 |                       0.1488 |               196
2024-09 |                  4000 |                         0 |               4000 |                  0.064 |                          3 |                        0.063 |               206
2024-10 |                  4299 |                         0 |               4299 |                 0.0642 |                          3 |                        0.064 |               224
2024-11 |                  3824 |                         0 |               3824 |                 0.0709 |                          3 |                       0.0696 |               265
2024-12 |                  4547 |                         0 |               4547 |                 0.0622 |                          3 |                       0.0603 |               531
2025-01 |                  4187 |                         0 |               4187 |                 0.0642 |                          3 |                       0.0633 |               263
2025-02 |                  8786 |                         0 |               8786 |                 0.1228 |                          4 |                       0.1224 |               189
2025-03 |                  4543 |                         0 |               4543 |                 0.1343 |                          3 |                       0.1332 |               207
2025-04 |                  4097 |                         0 |               4097 |                 0.0703 |                          3 |                       0.0696 |               227
2025-05 |                  4486 |                         0 |               4486 |                  0.058 |                          3 |                       0.0573 |               284
2025-06 |                  3955 |                         0 |               3955 |                 0.0668 |                          3 |                       0.0665 |               285
2025-07 |                  4525 |                         0 |               4525 |                 0.0606 |                          3 |                       0.0601 |               257
2025-08 |                  4777 |                         0 |               4777 |                 0.1505 |                          2 |                       0.1505 |               242
2025-09 |                  5030 |                         0 |               5030 |                 0.0517 |                          3 |                       0.0509 |               274
2025-10 |                  4709 |                         0 |               4709 |                  0.066 |                          3 |                       0.0652 |               305
2025-11 |                  4446 |                         0 |               4446 |                 0.0558 |                          3 |                       0.0549 |               393
2025-12 |                  4902 |                         0 |               4902 |                 0.0037 |                          3 |                       0.0024 |               617
(36 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:

It becomes benchmark query q068

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

spellingcast (7×)
determinismtie-breaker: month_start does not determine month_label by the schema
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
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

WITH months AS (  SELECT DISTINCT month_label, month_start  FROM calendar_days  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'),live AS (  SELECT date_trunc('month', occurred_at)::date AS month_start,  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start,         count(*) AS live_events,         count(*) FILTER (WHERE actor_user_id IS NULL) AS live_null_actor,         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS live_system_api  FROM audit_log  GROUP BY 1),archive AS (  SELECT date_trunc('month', occurred_at)::date AS month_start,  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start,         count(*) AS archived_events,         count(*) FILTER (WHERE actor_user_id IS NULL) AS archived_null_actor,         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS archived_system_api  FROM audit_log_archive  GROUP BY 1),actor_types AS (  SELECT month_start, actor_type  FROM (    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log    SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, actor_type FROM audit_log    UNION ALL    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log_archive    SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, actor_type FROM audit_log_archive  ) t  GROUP BY month_start, actor_type),distinct_actor_types AS (  SELECT month_start, count(*) AS distinct_actor_type_values  FROM actor_types  GROUP BY month_start),access AS (  SELECT date_trunc('month', occurred_at)::date AS month_start, count(*) AS access_events  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, count(*) AS access_events  FROM access_log  GROUP BY 1)SELECT  m.month_label AS month,  coalesce(l.live_events,0) AS audit_log_live_events,  coalesce(a.archived_events,0) AS audit_log_archived_events,  coalesce(l.live_events,0) + coalesce(a.archived_events,0) AS total_audit_events,  round(    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL    ELSE (coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))    END, 4) AS pct_actor_user_id_null,  CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL  ELSE CAST((coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0)) AS numeric(18,6)) / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))  END AS pct_actor_user_id_null,  coalesce(dat.distinct_actor_type_values,0) AS distinct_actor_type_values,  round(    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL    ELSE (coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))    END, 4) AS pct_actor_type_system_or_api,  CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL  ELSE CAST((coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0)) AS numeric(18,6)) / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))  END AS pct_actor_type_system_or_api,  coalesce(acc.access_events,0) AS access_log_eventsFROM months mLEFT JOIN live l ON l.month_start = m.month_startLEFT JOIN archive a ON a.month_start = m.month_startLEFT JOIN distinct_actor_types dat ON dat.month_start = m.month_startLEFT JOIN access acc ON acc.month_start = m.month_startORDER BY m.month_startORDER BY m.month_start NULLS LAST, m.month_label NULLS LAST

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

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
live AS (
  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start,
         count(*) AS live_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS live_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS live_system_api
  FROM audit_log
  GROUP BY 1
),
archive AS (
  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start,
         count(*) AS archived_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS archived_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS archived_system_api
  FROM audit_log_archive
  GROUP BY 1
),
actor_types AS (
  SELECT month_start, actor_type
  FROM (
    SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, actor_type FROM audit_log
    UNION ALL
    SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, actor_type FROM audit_log_archive
  ) t
  GROUP BY month_start, actor_type
),
distinct_actor_types AS (
  SELECT month_start, count(*) AS distinct_actor_type_values
  FROM actor_types
  GROUP BY month_start
),
access AS (
  SELECT CAST(date_trunc('month', occurred_at) AS date) AS month_start, count(*) AS access_events
  FROM access_log
  GROUP BY 1
)
SELECT
  m.month_label AS month,
  coalesce(l.live_events,0) AS audit_log_live_events,
  coalesce(a.archived_events,0) AS audit_log_archived_events,
  coalesce(l.live_events,0) + coalesce(a.archived_events,0) AS total_audit_events,
  CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
  ELSE CAST((coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0)) AS numeric(18,6)) / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
  END AS pct_actor_user_id_null,
  coalesce(dat.distinct_actor_type_values,0) AS distinct_actor_type_values,
  CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
  ELSE CAST((coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0)) AS numeric(18,6)) / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
  END AS pct_actor_type_system_or_api,
  coalesce(acc.access_events,0) AS access_log_events
FROM months m
LEFT JOIN live l ON l.month_start = m.month_start
LEFT JOIN archive a ON a.month_start = m.month_start
LEFT JOIN distinct_actor_types dat ON dat.month_start = m.month_start
LEFT JOIN access acc ON acc.month_start = m.month_start
ORDER BY m.month_start NULLS LAST, m.month_label NULLS LAST

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

The request, as Ines received it

Report request from Claire Dubois (analyst for the Head of Risk and Payments):

Title: Audit log volume and actor identification by month
Question: Is our audit trail continuous over time with no unexplained gaps, and are logged actions reliably tied to an identifiable actor rather than null/system placeholders?
Grain: one row per calendar month (based on occurred_at), combining live audit_log and audit_log_archive, from 2023-01 through 2025-12
Columns:
- month (YYYY-MM)
- audit_log_live_events (count from audit_log with occurred_at in month)
- audit_log_archived_events (count from audit_log_archive with occurred_at in month)
- total_audit_events (sum of the two)
- pct_actor_user_id_null (share of combined rows where actor_user_id is null)
- distinct_actor_type_values (count distinct actor_type across combined rows in month)
- pct_actor_type_system_or_api (share where actor_type in ('system','api_key','service_account') rather than 'user')
- access_log_events (count from access_log with occurred_at in month, as a separate sensitive-data-read signal)
Details: Sources: audit_log, audit_log_archive (same shape, union on occurred_at month), access_log (separate, not unioned — just a parallel count). Purpose is purely diagnostic: look for months with suspiciously low total_audit_events (possible logging gaps) and for high/rising pct_actor_user_id_null or pct_actor_type_system_or_api (possible actor-identity weakness, relevant before we cite audit evidence in the questionnaire). Order by month ascending.

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

Ines calls dry_run

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
live AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS live_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS live_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS live_system_api
  FROM audit_log
  GROUP BY 1
),
archive AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS archived_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS archived_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS archived_system_api
  FROM audit_log_archive
  GROUP BY 1
),
actor_types AS (
  SELECT month_start, actor_type
  FROM (
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log
    UNION ALL
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log_archive
  ) t
  GROUP BY month_start, actor_type
),
distinct_actor_types AS (
  SELECT month_start, count(*) AS distinct_actor_type_values
  FROM actor_types
  GROUP BY month_start
),
access AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start, count(*) AS access_events
  FROM access_log
  GROUP BY 1
)
SELECT
  m.month_label AS month,
  coalesce(l.live_events,0) AS audit_log_live_events,
  coalesce(a.archived_events,0) AS audit_log_archived_events,
  coalesce(l.live_events,0) + coalesce(a.archived_events,0) AS total_audit_events,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_user_id_null,
  coalesce(dat.distinct_actor_type_values,0) AS distinct_actor_type_values,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_type_system_or_api,
  coalesce(acc.access_events,0) AS access_log_events
FROM months m
LEFT JOIN live l ON l.month_start = m.month_start
LEFT JOIN archive a ON a.month_start = m.month_start
LEFT JOIN distinct_actor_types dat ON dat.month_start = m.month_start
LEFT JOIN access acc ON acc.month_start = m.month_start
ORDER BY m.month_start

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, audit_log_live_events, audit_log_archived_events, total_audit_events, pct_actor_user_id_null, distinct_actor_type_values, pct_actor_type_system_or_api, access_log_events

InesQuery compiles and returns exactly the requested columns. Submitting.

Ines calls submit

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
live AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS live_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS live_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS live_system_api
  FROM audit_log
  GROUP BY 1
),
archive AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start,
         count(*) AS archived_events,
         count(*) FILTER (WHERE actor_user_id IS NULL) AS archived_null_actor,
         count(*) FILTER (WHERE actor_type IN ('system','api_key','service_account')) AS archived_system_api
  FROM audit_log_archive
  GROUP BY 1
),
actor_types AS (
  SELECT month_start, actor_type
  FROM (
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log
    UNION ALL
    SELECT date_trunc('month', occurred_at)::date AS month_start, actor_type FROM audit_log_archive
  ) t
  GROUP BY month_start, actor_type
),
distinct_actor_types AS (
  SELECT month_start, count(*) AS distinct_actor_type_values
  FROM actor_types
  GROUP BY month_start
),
access AS (
  SELECT date_trunc('month', occurred_at)::date AS month_start, count(*) AS access_events
  FROM access_log
  GROUP BY 1
)
SELECT
  m.month_label AS month,
  coalesce(l.live_events,0) AS audit_log_live_events,
  coalesce(a.archived_events,0) AS audit_log_archived_events,
  coalesce(l.live_events,0) + coalesce(a.archived_events,0) AS total_audit_events,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_null_actor,0)+coalesce(a.archived_null_actor,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_user_id_null,
  coalesce(dat.distinct_actor_type_values,0) AS distinct_actor_type_values,
  round(
    CASE WHEN (coalesce(l.live_events,0)+coalesce(a.archived_events,0)) = 0 THEN NULL
    ELSE (coalesce(l.live_system_api,0)+coalesce(a.archived_system_api,0))::numeric / (coalesce(l.live_events,0)+coalesce(a.archived_events,0))
    END, 4) AS pct_actor_type_system_or_api,
  coalesce(acc.access_events,0) AS access_log_events
FROM months m
LEFT JOIN live l ON l.month_start = m.month_start
LEFT JOIN archive a ON a.month_start = m.month_start
LEFT JOIN distinct_actor_types dat ON dat.month_start = m.month_start
LEFT JOIN access acc ON acc.month_start = m.month_start
ORDER BY m.month_start

The warehouse answers

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

InesSubmitted. The report gives one row per month (2023-01 through 2025-12), with live/archived/total audit event counts, the share of rows missing `actor_user_id`, the count of distinct `actor_type` values seen, the share tagged as system/api_key/service_account, and a parallel `access_log` event count — all computed platform-wide so Claire can scan for suspiciously low-volume months (gaps) or rising non-user actor shares (identity weakness) before citing the audit trail in the questionnaire.

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

Audit log volume and actor identification by month

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

Aylin Demir writes to Claire Dubois

Claire Dubois plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q068

Everything, as recorded