Queries · q103 · Chief Information Security Officer · week 1, night 2

Platform-wide MFA adoption trend by month, staff vs. shoppers

“What is the actual company-wide MFA adoption rate today for staff and for shoppers, and has it been improving or flat over the past year?”

Manager · asked the questionHenrik AaltoChief Information Security Officer. Joined from a cloud provider's security team. Several enterprise deals depend on passing security reviews.

Analyst · planned the reportNoah FischerSystems thinker: maps how one process feeds the next before measuring anything.

Engineer · wrote the SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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

Henrik Aalto writes to Noah Fischer
The manager, at the start of week 1
Subject: Where I'd like your help first — account security snapshot

Hi Noah,

Good to have you working with me this week. I'm still building my mental map of the business, so let's start broad and sharpen later. Here's what's on my mind, roughly in priority order:
1. How exposed are we on sign-in security today? I want to understand how merchant staff and shoppers are authenticating — are we seeing risky patterns like repeated failed logins, credential-stuffing-type behavior, or logins from unusual places? I need a feel for the baseline before I can say whether we have a problem.
2. How strong is our multi-factor authentication posture? I'd like to know roughly how many of our merchants' staff (and ideally shoppers, if meaningful) actually have MFA set up and active, versus how many are relying on password-only access. This matters because it's one of the first things enterprise security reviewers ask about, and I need to know whether we can answer confidently or whether it's a gap we need to close.
3. Who holds powerful access, and is it still appropriate? I'm interested in how permissions and roles are granted and whether there's good hygiene around it — e.g., are there dormant privileged accounts, or grants that nobody seems to be reviewing. This feeds directly into access-control questions in security audits.
4. How healthy are our API keys and service accounts? These are often the weakest link — long-lived tokens that nobody rotates. I'd like a sense of how many are active, how old they are, and whether any look abandoned or over-privileged.
5. Can we actually reconstruct "who did what, when" if asked? I want to understand how complete and reliable our audit trail is in practice — are sensitive actions consistently logged, and would we be able to produce a credible record if a merchant or regulator asked us to investigate an incident?
What I need this for: I'm walking into enterprise deals where security questionnaires will ask us point-blank about authentication strength, access governance, credential hygiene, and auditability. I need an honest, evidence-based picture — good or bad — so I can brief the CEO and sales on what we can credibly claim today versus what needs fixing before the next review cycle.

Given the slot budget, let's pick whichever 3 of these will give us the most defensible starting picture this week, and we can go deeper on the rest next week.

Thanks, Henrik
Noah Fischer plans the night
The analyst · week 1, night 2, request 1 of 3
Reading critically: (1) login volumes look like a calm baseline — failure rate holds 5-8% and avg attempts-per-IP stays ~1.5 all 26 weeks, which doesn't look like credential stuffing. But the week of 2025-11-24 jumps ~41% in total attempts (and max-attempts-per-single-IP jumps to 24, the second-highest in the series) while failure rate stays normal — plausibly just Black Friday/Cyber Monday shopping traffic, but I want to confirm it's broad-based across tenants rather than concentrated on one tenant/IP before ruling out an attack. (2) MFA report 2 is a bombshell: shopper MFA is 0.0% for every one of the 50 worst-covered tenants, and staff MFA tops out at 50% even among the better of that bottom 50 — but this report only shows the worst half of tenants by staff rate, so I don't yet have the company-wide headline number Henrik actually needs to quote, nor do I know if MFA adoption is improving over time or flat. (3) Report 3's 50-row page got entirely consumed by the single "Owner" system role (26 permissions, tied for the most) across ~50 tenants, out of 434 qualifying tenant-role rows — I haven't seen any non-Owner privileged roles yet, so I can't say anything about custom role hygiene. I did spot two dormant privileged accounts worth keeping (Aurum & Ash: Owner account active in status but last login 2024-12-26, over a year stale, on a churned tenant; Halden Boots: Owner last login 2025-10-03). Tonight's three requests: a monthly MFA-adoption trend giving the defensible overall number and trajectory, a tenant breakdown of the anomalous Nov 24 week to test the Black-Friday-vs-attack hypothesis, and a company-wide rollup by role name (collapsing the 434 tenant-role rows) to see privileged access beyond "Owner" and its dormancy rate.
Platform-wide MFA adoption trend by month, staff vs. shoppers
Question
What is the actual company-wide MFA adoption rate today for staff and for shoppers, and has it been improving or flat over the past year?
One row per
calendar month (month_label), platform-wide across all tenants
Columns
- month_label (YYYY-MM, month end snapshot)
- staff users as of month end
- staff users with confirmed MFA as of month end
- staff MFA rate (%)
- shopper users as of month end
- shopper users with confirmed MFA as of month end
- shopper MFA rate (%)

Grace 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 month_label, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE year_num = 2025
  GROUP BY month_label
),
eligible_users AS (
  SELECT m.month_label, m.month_end, u.id AS user_id, u.is_staff
  FROM months m
  JOIN users u
    ON u.created_at <= m.month_end
   AND (u.deleted_at IS NULL OR u.deleted_at > m.month_end)
),
user_mfa AS (
  SELECT eu.month_label, eu.month_end, eu.user_id, eu.is_staff,
    EXISTS (
      SELECT 1 FROM mfa_devices md
      WHERE md.user_id = eu.user_id AND md.confirmed_at <= eu.month_end
    ) AS has_mfa
  FROM eligible_users eu
),
monthly_summary AS (
  SELECT month_label,
    COUNT(*) FILTER (WHERE is_staff) AS staff_users,
    COUNT(*) FILTER (WHERE is_staff AND has_mfa) AS staff_mfa_users,
    COUNT(*) FILTER (WHERE NOT is_staff) AS shopper_users,
    COUNT(*) FILTER (WHERE NOT is_staff AND has_mfa) AS shopper_mfa_users
  FROM user_mfa
  GROUP BY month_label
)
SELECT
  month_label,
  staff_users,
  staff_mfa_users AS staff_users_with_mfa,
  ROUND(100.0 * staff_mfa_users / NULLIF(staff_users,0), 1) AS staff_mfa_rate_pct,
  shopper_users,
  shopper_mfa_users AS shopper_users_with_mfa,
  ROUND(100.0 * shopper_mfa_users / NULLIF(shopper_users,0), 1) AS shopper_mfa_rate_pct
FROM monthly_summary
ORDER BY month_label

“Query compiles cleanly and returns exactly the 7 requested columns at one-row-per-month grain. 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 produces one row per month (Jan–Dec 2025) with staff and shopper user counts, confirmed-MFA counts, and rates (%), ordered chronologically, as requested — 7 columns, well within the width limit.”

The report comes back overnight

As Noah Fischer saw it, from the small dataset the agents worked on

month_label | staff_users | staff_users_with_mfa | staff_mfa_rate_pct | shopper_users | shopper_users_with_mfa | shopper_mfa_rate_pct
------------+-------------+----------------------+--------------------+---------------+------------------------+---------------------
2025-01     |         462 |                  251 |               54.3 |          7741 |                      0 |                  0.0
2025-02     |         464 |                  263 |               56.7 |          7941 |                      0 |                  0.0
2025-03     |         468 |                  266 |               56.8 |          8188 |                      0 |                  0.0
2025-04     |         472 |                  268 |               56.8 |          8426 |                      0 |                  0.0
2025-05     |         483 |                  273 |               56.5 |          8749 |                      0 |                  0.0
2025-06     |         496 |                  278 |               56.0 |          8997 |                      0 |                  0.0
2025-07     |         507 |                  282 |               55.6 |          9268 |                      0 |                  0.0
2025-08     |         520 |                  285 |               54.8 |          9537 |                      0 |                  0.0
2025-09     |         531 |                  291 |               54.8 |          9837 |                      0 |                  0.0
2025-10     |         539 |                  295 |               54.7 |         10208 |                      0 |                  0.0
2025-11     |         545 |                  299 |               54.9 |         10632 |                      0 |                  0.0
2025-12     |         547 |                  300 |               54.8 |         11207 |                      0 |                  0.0
(12 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:
- q318Shopper authentication method mix: OAuth-linked vs password-only
It builds on:
- q101MFA adoption rate by merchant, staff vs. shoppers

It becomes benchmark query q103

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

numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
made explicita selected t.col gets AS col, its standard name made explicit (7×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results

The benchmark text, against the submitted SQL

WITH months AS (  SELECT month_label, MAX(calendar_date) AS month_end  FROM calendar_days  WHERE year_num = 2025  GROUP BY month_label),eligible_users AS (  SELECT m.month_label, m.month_end, u.id AS user_id, u.is_staff  SELECT m.month_label AS month_label, m.month_end AS month_end, u.id AS user_id, u.is_staff AS is_staff  FROM months m  JOIN users u    ON u.created_at <= m.month_end   AND (u.deleted_at IS NULL OR u.deleted_at > m.month_end)),user_mfa AS (  SELECT eu.month_label, eu.month_end, eu.user_id, eu.is_staff,  SELECT eu.month_label AS month_label, eu.month_end AS month_end, eu.user_id AS user_id, eu.is_staff AS is_staff,    EXISTS (      SELECT 1 FROM mfa_devices md      WHERE md.user_id = eu.user_id AND md.confirmed_at <= eu.month_end    ) AS has_mfa  FROM eligible_users eu),monthly_summary AS (  SELECT month_label,    COUNT(*) FILTER (WHERE is_staff) AS staff_users,    COUNT(*) FILTER (WHERE is_staff AND has_mfa) AS staff_mfa_users,    COUNT(*) FILTER (WHERE NOT is_staff) AS shopper_users,    COUNT(*) FILTER (WHERE NOT is_staff AND has_mfa) AS shopper_mfa_users  FROM user_mfa  GROUP BY month_label)SELECT  month_label,  staff_users,  staff_mfa_users AS staff_users_with_mfa,  ROUND(100.0 * staff_mfa_users / NULLIF(staff_users,0), 1) AS staff_mfa_rate_pct,  100.0 * staff_mfa_users / NULLIF(staff_users,0) AS staff_mfa_rate_pct,  shopper_users,  shopper_mfa_users AS shopper_users_with_mfa,  ROUND(100.0 * shopper_mfa_users / NULLIF(shopper_users,0), 1) AS shopper_mfa_rate_pct  100.0 * shopper_mfa_users / NULLIF(shopper_users,0) AS shopper_mfa_rate_pctFROM monthly_summaryORDER BY month_labelORDER BY month_label NULLS LAST

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

WITH months AS (
  SELECT month_label, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE year_num = 2025
  GROUP BY month_label
),
eligible_users AS (
  SELECT m.month_label AS month_label, m.month_end AS month_end, u.id AS user_id, u.is_staff AS is_staff
  FROM months m
  JOIN users u
    ON u.created_at <= m.month_end
   AND (u.deleted_at IS NULL OR u.deleted_at > m.month_end)
),
user_mfa AS (
  SELECT eu.month_label AS month_label, eu.month_end AS month_end, eu.user_id AS user_id, eu.is_staff AS is_staff,
    EXISTS (
      SELECT 1 FROM mfa_devices md
      WHERE md.user_id = eu.user_id AND md.confirmed_at <= eu.month_end
    ) AS has_mfa
  FROM eligible_users eu
),
monthly_summary AS (
  SELECT month_label,
    COUNT(*) FILTER (WHERE is_staff) AS staff_users,
    COUNT(*) FILTER (WHERE is_staff AND has_mfa) AS staff_mfa_users,
    COUNT(*) FILTER (WHERE NOT is_staff) AS shopper_users,
    COUNT(*) FILTER (WHERE NOT is_staff AND has_mfa) AS shopper_mfa_users
  FROM user_mfa
  GROUP BY month_label
)
SELECT
  month_label,
  staff_users,
  staff_mfa_users AS staff_users_with_mfa,
  100.0 * staff_mfa_users / NULLIF(staff_users,0) AS staff_mfa_rate_pct,
  shopper_users,
  shopper_mfa_users AS shopper_users_with_mfa,
  100.0 * shopper_mfa_users / NULLIF(shopper_users,0) AS shopper_mfa_rate_pct
FROM monthly_summary
ORDER BY month_label NULLS LAST

Fireboltno result within 30 s
DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })

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

Grace's whole session, 7 turns

The request, as Grace received it

Report request from Noah Fischer (analyst for the Chief Information Security Officer):

Title: Platform-wide MFA adoption trend by month, staff vs. shoppers
Question: What is the actual company-wide MFA adoption rate today for staff and for shoppers, and has it been improving or flat over the past year?
Grain: one row per calendar month (month_label), platform-wide across all tenants
Columns:
- month_label (YYYY-MM, month end snapshot)
- staff users as of month end
- staff users with confirmed MFA as of month end
- staff MFA rate (%)
- shopper users as of month end
- shopper users with confirmed MFA as of month end
- shopper MFA rate (%)
Details: Source: users (tenant_id, is_staff, created_at, deleted_at) and mfa_devices (user_id, confirmed_at). For each of the 12 months Jan 2025 through Dec 2025 (use calendar_days distinct month_start/month_label values), take month_end = last day of that month. 'Staff users as of month end' = count of users with is_staff = true, created_at <= month_end, and (deleted_at is null or deleted_at > month_end). 'Staff users with confirmed MFA as of month end' = of those, count with at least one mfa_devices row where confirmed_at <= month_end. Same logic for shoppers (is_staff = false). Rates rounded to 1 decimal percent. This is a cumulative snapshot each month, not new signups in that month. Order by month_label ascending (already unique).

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

Grace calls dry_run

WITH months AS (
  SELECT month_label, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE year_num = 2025
  GROUP BY month_label
),
eligible_users AS (
  SELECT m.month_label, m.month_end, u.id AS user_id, u.is_staff
  FROM months m
  JOIN users u
    ON u.created_at <= m.month_end
   AND (u.deleted_at IS NULL OR u.deleted_at > m.month_end)
),
user_mfa AS (
  SELECT eu.month_label, eu.month_end, eu.user_id, eu.is_staff,
    EXISTS (
      SELECT 1 FROM mfa_devices md
      WHERE md.user_id = eu.user_id AND md.confirmed_at <= eu.month_end
    ) AS has_mfa
  FROM eligible_users eu
),
monthly_summary AS (
  SELECT month_label,
    COUNT(*) FILTER (WHERE is_staff) AS staff_users,
    COUNT(*) FILTER (WHERE is_staff AND has_mfa) AS staff_mfa_users,
    COUNT(*) FILTER (WHERE NOT is_staff) AS shopper_users,
    COUNT(*) FILTER (WHERE NOT is_staff AND has_mfa) AS shopper_mfa_users
  FROM user_mfa
  GROUP BY month_label
)
SELECT
  month_label,
  staff_users,
  staff_mfa_users AS staff_users_with_mfa,
  ROUND(100.0 * staff_mfa_users / NULLIF(staff_users,0), 1) AS staff_mfa_rate_pct,
  shopper_users,
  shopper_mfa_users AS shopper_users_with_mfa,
  ROUND(100.0 * shopper_mfa_users / NULLIF(shopper_users,0), 1) AS shopper_mfa_rate_pct
FROM monthly_summary
ORDER BY month_label

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month_label, staff_users, staff_users_with_mfa, staff_mfa_rate_pct, shopper_users, shopper_users_with_mfa, shopper_mfa_rate_pct

GraceQuery compiles cleanly and returns exactly the 7 requested columns at one-row-per-month grain. Submitting.

Grace calls submit

WITH months AS (
  SELECT month_label, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE year_num = 2025
  GROUP BY month_label
),
eligible_users AS (
  SELECT m.month_label, m.month_end, u.id AS user_id, u.is_staff
  FROM months m
  JOIN users u
    ON u.created_at <= m.month_end
   AND (u.deleted_at IS NULL OR u.deleted_at > m.month_end)
),
user_mfa AS (
  SELECT eu.month_label, eu.month_end, eu.user_id, eu.is_staff,
    EXISTS (
      SELECT 1 FROM mfa_devices md
      WHERE md.user_id = eu.user_id AND md.confirmed_at <= eu.month_end
    ) AS has_mfa
  FROM eligible_users eu
),
monthly_summary AS (
  SELECT month_label,
    COUNT(*) FILTER (WHERE is_staff) AS staff_users,
    COUNT(*) FILTER (WHERE is_staff AND has_mfa) AS staff_mfa_users,
    COUNT(*) FILTER (WHERE NOT is_staff) AS shopper_users,
    COUNT(*) FILTER (WHERE NOT is_staff AND has_mfa) AS shopper_mfa_users
  FROM user_mfa
  GROUP BY month_label
)
SELECT
  month_label,
  staff_users,
  staff_mfa_users AS staff_users_with_mfa,
  ROUND(100.0 * staff_mfa_users / NULLIF(staff_users,0), 1) AS staff_mfa_rate_pct,
  shopper_users,
  shopper_mfa_users AS shopper_users_with_mfa,
  ROUND(100.0 * shopper_mfa_users / NULLIF(shopper_users,0), 1) AS shopper_mfa_rate_pct
FROM monthly_summary
ORDER BY month_label

The warehouse answers

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

GraceSubmitted. The report produces one row per month (Jan–Dec 2025) with staff and shopper user counts, confirmed-MFA counts, and rates (%), ordered chronologically, as requested — 7 columns, well within the width limit.

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

Grace's transcript (264 KB) · Noah Fischer's transcript (875 KB) · Henrik Aalto's transcript (262 KB) · the night's plan (6 KB) · the night's reports (16 KB) · the week's memo (3 KB)

Platform-wide MFA adoption trend by month, staff vs. shoppers

Henrik Aalto writes to Noah Fischer

Noah Fischer plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q103

Everything, as recorded