Queries · q101 · Chief Information Security Officer · week 1, night 1
“For each merchant, what share of staff users and what share of shopper (non-staff) users have a confirmed, active MFA device, versus relying on password only?”
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 1, request 2 of 3
Henrik flagged 5 areas but asked us to pick the 3 that give the most defensible starting picture this week, saving the rest for next week. I'm starting with his top 3 stated priorities (sign-in security baseline, MFA posture, privileged access hygiene) and deliberately deferring API-key/service-account hygiene and audit-trail completeness to night 2+, since those depend on first understanding how identity and access actually work here (systems view: authentication -> MFA -> authorization all feed each other; keys/audit are downstream concerns I'll size up once I see how messy or clean the core picture is).
Report 1 establishes a platform-wide time baseline for login behavior so any later "spike" claims have something to compare against. Report 2 gives a per-merchant MFA adoption view, ordered to surface the worst gaps first since that's the actionable finding for a security head walking into enterprise reviews. Report 3 looks at role/permission grants per merchant to see how concentrated and stale ("dormant") privileged access is — the classic access-governance audit question. All three are single-grain, independent, and should each stand on their own without needing follow-up queries to simply read.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0), 1) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0), 1) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_users FROM tenants t JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL LEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id ) um ON um.tenant_id = u.tenant_id AND um.user_id = u.id GROUP BY t.id, t.name, t.status ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC LIMIT 50
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report returns one row per tenant (staff vs. shopper MFA adoption, rates, and totals), ordered by staff MFA rate ascending so the weakest merchants surface first, capped at the 50-row page limit as noted to the analyst.”
As Noah Fischer saw it, from the small dataset the agents worked on
tenant_id | tenant_name | tenant_status | staff_user_count | staff_users_with_mfa | staff_mfa_rate_pct | shopper_user_count | shopper_users_with_mfa | shopper_mfa_rate_pct | total_users
----------+-------------------------------+---------------+------------------+----------------------+--------------------+--------------------+------------------------+----------------------+------------
28 | Atelier Nouveau | active | 5 | 0 | 0.0 | 59 | 0 | 0.0 | 64
91 | Chocolaterie Mireille | active | 3 | 0 | 0.0 | 16 | 0 | 0.0 | 19
90 | Dewdrop Studio | churned | 4 | 0 | 0.0 | 5 | 0 | 0.0 | 9
103 | Kabelwerk | active | 4 | 0 | 0.0 | 5 | 0 | 0.0 | 9
21 | Kettle & Key | active | 4 | 0 | 0.0 | 44 | 0 | 0.0 | 48
22 | Midland Safety Wear | active | 4 | 0 | 0.0 | 53 | 0 | 0.0 | 57
112 | Pixelhaus | active | 4 | 0 | 0.0 | 4 | 0 | 0.0 | 8
111 | Snout & Sons | active | 3 | 0 | 0.0 | 2 | 0 | 0.0 | 5
108 | Alpine Loft | active | 4 | 1 | 25.0 | 5 | 0 | 0.0 | 9
64 | Brick & Bobbin | active | 4 | 1 | 25.0 | 23 | 0 | 0.0 | 27
24 | Fern & Fable | active | 4 | 1 | 25.0 | 50 | 0 | 0.0 | 54
107 | Glasshouse Grooming | active | 4 | 1 | 25.0 | 7 | 0 | 0.0 | 11
42 | Gratlinie Outdoor | active | 8 | 2 | 25.0 | 230 | 0 | 0.0 | 238
120 | Harbour Swim | active | 4 | 1 | 25.0 | 1 | 0 | 0.0 | 5
53 | Noordzee Surfwear | active | 4 | 1 | 25.0 | 57 | 0 | 0.0 | 61
78 | Northstar Watches | active | 4 | 1 | 25.0 | 17 | 0 | 0.0 | 21
72 | Polder Packaging | active | 4 | 1 | 25.0 | 18 | 0 | 0.0 | 22
43 | Roast Republic | active | 4 | 1 | 25.0 | 25 | 0 | 0.0 | 29
100 | Rosehip Lab | active | 4 | 1 | 25.0 | 19 | 0 | 0.0 | 23
74 | Sorella Skin | active | 4 | 1 | 25.0 | 39 | 0 | 0.0 | 43
93 | Strandgut | active | 4 | 1 | 25.0 | 23 | 0 | 0.0 | 27
41 | The Balm Room | active | 4 | 1 | 25.0 | 61 | 0 | 0.0 | 65
17 | Ampfeld Elektronik | active | 13 | 4 | 30.8 | 484 | 0 | 0.0 | 497
83 | Aurum & Ash | churned | 3 | 1 | 33.3 | 4 | 0 | 0.0 | 7
99 | Goldkind | active | 3 | 1 | 33.3 | 6 | 0 | 0.0 | 9
31 | Hound Hamper | active | 3 | 1 | 33.3 | 29 | 0 | 0.0 | 32
113 | Kaffeekontor Nord | active | 3 | 1 | 33.3 | 3 | 0 | 0.0 | 6
66 | Northwire Audio | churned | 3 | 1 | 33.3 | 4 | 0 | 0.0 | 7
76 | Paddock & Pine | churned | 3 | 1 | 33.3 | 4 | 0 | 0.0 | 7
102 | Pedal & Pine | active | 3 | 1 | 33.3 | 6 | 0 | 0.0 | 9
57 | Saltmarsh Knitwear | churned | 3 | 1 | 33.3 | 15 | 0 | 0.0 | 18
115 | Signalbox | active | 3 | 1 | 33.3 | 9 | 0 | 0.0 | 12
12 | Spice Route Box | active | 6 | 2 | 33.3 | 175 | 0 | 0.0 | 181
39 | Kayak Corner | active | 5 | 2 | 40.0 | 107 | 0 | 0.0 | 112
58 | Nordic Bloom Cosmetics | active | 5 | 2 | 40.0 | 83 | 0 | 0.0 | 88
32 | Oakhurst Tailoring | active | 5 | 2 | 40.0 | 114 | 0 | 0.0 | 119
16 | Ostwind Mode | active | 5 | 2 | 40.0 | 83 | 0 | 0.0 | 88
79 | Kibblewick | active | 7 | 3 | 42.9 | 151 | 0 | 0.0 | 158
11 | Ballycorran Industrial Supply | active | 6 | 3 | 50.0 | 103 | 0 | 0.0 | 109
73 | Barkley & Co. | active | 4 | 2 | 50.0 | 15 | 0 | 0.0 | 19
71 | Basecamp Nord | active | 4 | 2 | 50.0 | 19 | 0 | 0.0 | 23
92 | Brewline Coffee | active | 4 | 2 | 50.0 | 9 | 0 | 0.0 | 13
50 | Buchstabe | active | 4 | 2 | 50.0 | 24 | 0 | 0.0 | 28
68 | Bytefield | active | 4 | 2 | 50.0 | 16 | 0 | 0.0 | 20
38 | Coastline Interiors | active | 4 | 2 | 50.0 | 134 | 0 | 0.0 | 138
67 | Deskline Supplies | active | 4 | 2 | 50.0 | 15 | 0 | 0.0 | 19
51 | Driftwood Home | active | 4 | 2 | 50.0 | 24 | 0 | 0.0 | 28
46 | Feather & Fin | active | 4 | 2 | 50.0 | 45 | 0 | 0.0 | 49
36 | Fjordline Wear | active | 4 | 2 | 50.0 | 47 | 0 | 0.0 | 51
60 | Haven Linen | active | 4 | 2 | 50.0 | 22 | 0 | 0.0 | 26
(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.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0), 1) AS staff_mfa_rate_pct, 100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0), 1) AS shopper_mfa_rate_pct, 100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_usersFROM tenants tJOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULLLEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id) um ON um.tenant_id = u.tenant_id AND um.user_id = u.idGROUP BY t.id, t.name, t.statusORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASCLIMIT 50ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, 100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, 100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_users FROM tenants t JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL LEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id ) um ON um.tenant_id = u.tenant_id AND um.user_id = u.id GROUP BY t.id, t.name, t.status ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, 100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, 100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_users FROM tenants t JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL LEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id ) um ON um.tenant_id = u.tenant_id AND um.user_id = u.id GROUP BY t.id, t.name, t.status ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC NULLS LAST, t.id ASC 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: MFA adoption rate by merchant, staff vs. shoppers Question: For each merchant, what share of staff users and what share of shopper (non-staff) users have a confirmed, active MFA device, versus relying on password only? Grain: one row per tenant (merchant) Columns: - tenant name - tenant status - staff user count - staff users with confirmed MFA - staff MFA rate (%) - shopper user count - shopper users with confirmed MFA - shopper MFA rate (%) - total users Details: Source: users (tenant_id, is_staff, deleted_at) joined to tenants for name/status, and to mfa_devices (tenant_id, user_id, confirmed_at) to determine MFA status. A user counts as having 'confirmed MFA' if they have at least one mfa_devices row with confirmed_at not null. Exclude soft-deleted users (deleted_at is null). Split counts by is_staff = true (staff) vs is_staff = false (shopper/end-user). Compute staff_mfa_rate = staff_with_mfa / staff_count (null-safe if staff_count = 0), same for shopper rate, rounded to 1 decimal percent. Include all 120 tenants that have at least one user; order by staff MFA rate ascending (worst coverage first) with tenant name as tie-break, so the report surfaces the weakest merchants first. Note total tenant count returned vs. the 50-row cap. 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(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0), 1) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0), 1) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_users FROM tenants t JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL LEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id ) um ON um.tenant_id = u.tenant_id AND um.user_id = u.id GROUP BY t.id, t.name, t.status ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_id, tenant_name, tenant_status, staff_user_count, staff_users_with_mfa, staff_mfa_rate_pct, shopper_user_count, shopper_users_with_mfa, shopper_mfa_rate_pct, total_users
submitSELECT t.id AS tenant_id, t.name AS tenant_name, t.status AS tenant_status, COUNT(CASE WHEN u.is_staff THEN u.id END) AS staff_user_count, COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) AS staff_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN u.is_staff THEN u.id END), 0), 1) AS staff_mfa_rate_pct, COUNT(CASE WHEN NOT u.is_staff THEN u.id END) AS shopper_user_count, COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) AS shopper_users_with_mfa, ROUND(100.0 * COUNT(CASE WHEN NOT u.is_staff AND um.has_mfa THEN u.id END) / NULLIF(COUNT(CASE WHEN NOT u.is_staff THEN u.id END), 0), 1) AS shopper_mfa_rate_pct, COUNT(u.id) AS total_users FROM tenants t JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL LEFT JOIN ( SELECT tenant_id, user_id, true AS has_mfa FROM mfa_devices WHERE confirmed_at IS NOT NULL GROUP BY tenant_id, user_id ) um ON um.tenant_id = u.tenant_id AND um.user_id = u.id GROUP BY t.id, t.name, t.status ORDER BY staff_mfa_rate_pct ASC NULLS LAST, tenant_name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.