Queries · q107 · Chief Information Security Officer · week 1, night 3
“How many API keys does each merchant have, how many are active, and how many of those active keys look abandoned (never used, or unused for a long time)?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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:
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
The analyst · week 1, night 3, request 2 of 3
Night 2 findings: MFA is flat and low — staff MFA has hovered at 54-57% all year (not improving, not declining — a plateau, which itself is a finding: nobody is pushing adoption), and shopper MFA is a literal 0.0% in every single month across ~11,200 shopper accounts, confirming it isn't sampling noise from report 1 — shoppers simply have no MFA option in use at all. The Nov 24 week spike is now explained: it's broad-based across ~110 tenants with the largest tenant (Cragsley Outfitters) naturally taking the biggest share, distinct-IPs tracking distinct-emails roughly 1:1, and failure rates in line with baseline — looks like ordinary Black Friday/Cyber Monday traffic, not a credential-stuffing event, so I'm closing that thread. The role-landscape report revealed something important report 3 (night 1) didn't show: every tenant also has an 'Administrator' system role carrying almost as many permissions as Owner (25 vs 26), with 122 holders platform-wide and a 6.6% dormancy rate — higher than what we saw for Owner — so Administrator, not just Owner, is the second pool of truly powerful access worth naming individuals in.
With two nights left before Friday's memo, I'm using tonight to (1) get the concrete, named list of dormant Owner/Administrator accounts for the access-governance finding, and open the two untouched topics Henrik raised — (2) API key/service account hygiene and (3) audit trail completeness — so night 4 can go deeper on whichever of those looks worst rather than starting cold.
The engineer · 2 dry runs on the empty schema, then the submission
SELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASC LIMIT 50
SELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_countFROM api_keys akJOIN tenants t ON t.id = ak.tenant_idLEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id) sa ON sa.tenant_id = t.idGROUP BY t.id, t.name, t.status, sa.service_account_countORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASCLIMIT 50
“This compiles with exactly the 9 requested columns. Submitting.”
“Submitted. The report returns one row per tenant with the nine requested columns, ordered to surface the most abandoned-looking merchants first (most active-never-used keys, then most active-unused-90+-days, then tenant name), capped at 50 rows.”
As Noah Fischer saw it, from the small dataset the agents worked on
tenant_name | tenant_status | total_api_keys | active_api_keys | revoked_or_expired_keys | active_never_used | active_unused_90d | oldest_active_key_age_days | service_accounts_count ------------------------------+---------------+----------------+-----------------+-------------------------+-------------------+-------------------+----------------------------+----------------------- Ampere & Co. | active | 1 | 1 | 0 | 0 | 0 | 1964 | 0 Ampfeld Elektronik | active | 5 | 5 | 0 | 0 | 0 | 2252 | 4 Atelier Nouveau | active | 1 | 1 | 0 | 0 | 0 | 1936 | 1 Aurum & Ash | churned | 1 | 1 | 0 | 0 | 0 | 554 | 1 Ballycorran Industrial Supply | active | 3 | 3 | 0 | 0 | 0 | 2372 | 2 Basecamp Nord | active | 1 | 1 | 0 | 0 | 0 | 862 | 1 Bijoux Lune | active | 1 | 1 | 0 | 0 | 0 | 912 | 1 Brennquell Kaffee | active | 5 | 4 | 1 | 0 | 0 | 2155 | 4 Chocolaterie Mireille | active | 1 | 1 | 0 | 0 | 0 | 452 | 1 Cinder Peak Sports Co. | active | 4 | 4 | 0 | 0 | 0 | 1951 | 3 Coastline Interiors | active | 2 | 2 | 0 | 0 | 0 | 1786 | 1 Copper Lane | active | 1 | 1 | 0 | 0 | 0 | 92 | 1 Courtside Collective | active | 1 | 1 | 0 | 0 | 0 | 520 | 1 Cragsley Outfitters | active | 6 | 6 | 0 | 0 | 0 | 2469 | 4 Dewdrop Studio | churned | 1 | 1 | 0 | 0 | 0 | 456 | 1 Fell & Fjell | active | 1 | 0 | 1 | 0 | 0 | NULL | 0 Fennel & Flint | active | 4 | 4 | 0 | 0 | 0 | 2516 | 3 Fern & Fable | active | 1 | 1 | 0 | 0 | 0 | 2003 | 1 Fjordline Wear | active | 1 | 0 | 1 | 0 | 0 | NULL | 1 Folio & Fern | active | 1 | 1 | 0 | 0 | 0 | 1070 | 1 Glasshouse Grooming | active | 1 | 1 | 0 | 0 | 0 | 190 | 1 Gratlinie Outdoor | active | 3 | 3 | 0 | 0 | 0 | 1693 | 2 Grind Collective | past_due | 3 | 3 | 0 | 0 | 0 | 2743 | 2 Hanse Industriebedarf | active | 1 | 1 | 0 | 0 | 0 | 1056 | 1 Hautnah | active | 1 | 1 | 0 | 0 | 0 | 1346 | 1 Honeycomb Larder | active | 1 | 1 | 0 | 0 | 0 | 2388 | 0 Juniper Apothecary | active | 1 | 1 | 0 | 0 | 0 | 2630 | 0 Kaffeekontor Nord | active | 1 | 1 | 0 | 0 | 0 | 139 | 1 Kettle & Key | active | 1 | 1 | 0 | 0 | 0 | 2057 | 1 Kibble Crate | active | 1 | 0 | 1 | 0 | 0 | NULL | 1 Kibblewick | active | 4 | 4 | 0 | 0 | 0 | 699 | 3 Kontor24 Bürobedarf | active | 3 | 3 | 0 | 0 | 0 | 2106 | 2 Linden & Loom | active | 4 | 3 | 1 | 0 | 0 | 1897 | 3 Lumen Collective | active | 1 | 1 | 0 | 0 | 0 | 1156 | 1 Marrowby & Vane | active | 6 | 6 | 0 | 0 | 0 | 2666 | 4 Mirelle | active | 1 | 1 | 0 | 0 | 0 | 2026 | 1 Noordzee Surfwear | active | 1 | 1 | 0 | 0 | 0 | 1330 | 1 Nordic Bloom Cosmetics | active | 2 | 2 | 0 | 0 | 0 | 1263 | 1 Northstar Watches | active | 1 | 1 | 0 | 0 | 0 | 696 | 1 Oakhurst Tailoring | active | 1 | 1 | 0 | 0 | 0 | 1864 | 0 OfficeHub Nordic | active | 3 | 1 | 2 | 0 | 0 | 2275 | 2 Olive & Oak Pantry | active | 1 | 1 | 0 | 0 | 0 | 68 | 1 Ondine Botanicals | active | 1 | 1 | 0 | 0 | 0 | 591 | 1 Ostwind Mode | active | 1 | 1 | 0 | 0 | 0 | 2259 | 1 Papeterie Grossiste | active | 1 | 1 | 0 | 0 | 0 | 276 | 1 Pedal & Pine | active | 1 | 1 | 0 | 0 | 0 | 210 | 1 Petalcourt Beauty | active | 3 | 3 | 0 | 0 | 0 | 1763 | 2 Pixelhaus | active | 1 | 1 | 0 | 0 | 0 | 119 | 1 Purrfect Pantry | active | 1 | 1 | 0 | 0 | 0 | 1334 | 1 Rosehip Lab | active | 1 | 1 | 0 | 0 | 0 | 258 | 1 (50 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_countFROM api_keys akJOIN tenants t ON t.id = ak.tenant_idLEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id) sa ON sa.tenant_id = t.idGROUP BY t.id, t.name, t.status, sa.service_account_countORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASCLIMIT 50ORDER BY active_never_used DESC NULLS FIRST, active_unused_90d DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST{{fetch_first(50)}}
SELECT t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC NULLS FIRST, active_unused_90d DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC NULLS FIRST, active_unused_90d DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST LIMIT 50
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Noah Fischer (analyst for the Chief Information Security Officer): Title: API key hygiene by merchant Question: How many API keys does each merchant have, how many are active, and how many of those active keys look abandoned (never used, or unused for a long time)? Grain: one row per tenant Columns: - tenant name - tenant status - total api keys - active api keys - revoked or expired api keys - active keys never used (last_used_at is null) - active keys unused 90+ days (last_used_at < 2025-10-05) - oldest active key age in days (2026-01-05 minus created_at) - service accounts count (from service_accounts table, for context) Details: Source: api_keys (tenant_id, status, created_at, last_used_at, expires_at) joined to tenants; service_accounts counted separately per tenant (that table has no usage timestamp, so it's included only as a headcount for context, not an activity measure). 'Active' = status = 'active'. Use 2026-01-05 as the fixed reference date for all day-based calculations (per house standards, no CURRENT_DATE). Include every tenant that has at least one api_keys row. Order by 'active keys never used' descending, then 'active keys unused 90+ days' descending, then tenant name as tie-break, so the most abandoned-looking merchants surface first. Cap at 50 rows, note total tenant rows with at least one key. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_id, tenant_name, tenant_status, total_api_keys, active_api_keys, revoked_or_expired_keys, active_never_used, active_unused_90d, oldest_active_key_age_days, service_accounts_count
dry_runSELECT t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, tenant_status, total_api_keys, active_api_keys, revoked_or_expired_keys, active_never_used, active_unused_90d, oldest_active_key_age_days, service_accounts_count
submitSELECT t.name AS tenant_name, t.status AS tenant_status, COUNT(ak.id) AS total_api_keys, SUM(CASE WHEN ak.status = 'active' THEN 1 ELSE 0 END) AS active_api_keys, SUM(CASE WHEN ak.status IN ('revoked','expired') THEN 1 ELSE 0 END) AS revoked_or_expired_keys, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at IS NULL THEN 1 ELSE 0 END) AS active_never_used, SUM(CASE WHEN ak.status = 'active' AND ak.last_used_at < TIMESTAMP '2025-10-05 00:00:00' THEN 1 ELSE 0 END) AS active_unused_90d, MAX(CASE WHEN ak.status = 'active' THEN DATE '2026-01-05' - CAST(ak.created_at AS DATE) ELSE NULL END) AS oldest_active_key_age_days, COALESCE(sa.service_account_count, 0) AS service_accounts_count FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN ( SELECT tenant_id, COUNT(*) AS service_account_count FROM service_accounts GROUP BY tenant_id ) sa ON sa.tenant_id = t.id GROUP BY t.id, t.name, t.status, sa.service_account_count ORDER BY active_never_used DESC, active_unused_90d DESC, t.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.