Queries · q284 · Head of Risk and Payments · week 2, night 3
“For the 3 merchants behind the February 2025 spike, was that an isolated one-quarter event, or do they show a recurring pattern of elevated login failures over the full history?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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.
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:
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
The analyst · week 2, night 3, request 3 of 3
Night 2 results, read critically:
Report 1 (corrected near-miss) now sums cleanly: 98 (tenant,email) pairs, 6 succeeded within 1h, 2 within 24h, 12 later in the month, and 78 (79.6%) never succeeded at all in February. That's an important nuance for Aylin: the overwhelming majority of repeat-failers simply never got in — this looks more like failed/abandoned attempts than a wave of accounts being compromised. Only 8 pairs (across the same handful of merchants) are genuine near-misses worth a closer look.
Report 2 (incidents): clean and reassuring — only 8 compliance_events total in 3 years (4 high, 4 medium), 100% resolved, nothing open, average resolution 7-18 days. Good answer for question #3, no further digging needed.
Report 3 (retention exposure) raised a new problem I have to resolve before telling Aylin/Rosa anything: compliance_events (earliest 2021-10-23), data_subject_requests (earliest 2020-04-24), and change_history (earliest 2021-11-10) all show records predating 2023-01-01 — but the company history is only supposed to start 2023-01-01 (per the system cutover). access_log and login_attempts correctly start right at 2023-01-01/02. This is either a handful of bad/seed timestamps or something more systematic, and I won't quote "this data goes back to 2020" without checking which it is first.
Tonight (3 slots): (1) a quick year-bucketed row-count diagnostic on the three tables with impossible pre-2023 dates, to see if it's a handful of outliers or a real pattern; (2) a pattern diagnostic on the Feb 2025 spike at the 3 affected merchants (user-agent diversity and day-of-month concentration) to give Aylin a firmer read on bug-vs-attack, since last night's volume numbers (many distinct emails and IPs, very low max-per-account) already lean toward a technical issue rather than credential stuffing; (3) a quarterly history of failed-login rates at those same 3 merchants, to see whether Feb 2025 was an isolated event or part of a recurring pattern — relevant since these are the same merchants already flagged elsewhere for support/response problems.
The engineer · 1 dry run on the empty schema, then the submission
WITH base AS ( SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num, c.quarter_num FROM tenants t CROSS JOIN ( SELECT DISTINCT year_num, quarter_num FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ) c WHERE t.name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij') ) SELECT b.tenant_name AS tenant_name, b.year_num || '-Q' || b.quarter_num AS quarter, COALESCE(ta.total_attempts, 0) AS total_attempts, COALESCE(fa.failed_attempts, 0) AS failed_attempts, CASE WHEN COALESCE(ta.total_attempts, 0) = 0 THEN 0 ELSE ROUND(100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts, 2) END AS pct_failed FROM base b LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS total_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) ta ON ta.tenant_id = b.tenant_id AND ta.year_num = b.year_num AND ta.quarter_num = b.quarter_num LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS failed_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) WHERE la.succeeded = false GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) fa ON fa.tenant_id = b.tenant_id AND fa.year_num = b.year_num AND fa.quarter_num = b.quarter_num ORDER BY b.tenant_name, b.year_num, b.quarter_num
“Query compiles and returns the expected columns. Submitting now.”
“Submitted. The report returns the requested 36-row grid (3 merchants × 12 quarters, 2023-Q1 through 2025-Q4) with total attempts, failed attempts, and failure rate per quarter, ordered by tenant name then quarter — ready for Claire to check whether Feb 2025 was isolated or part of a recurring pattern.”
As Claire Dubois saw it, from the small dataset the agents worked on
tenant_name | quarter | total_attempts | failed_attempts | pct_failed --------------------+---------+----------------+-----------------+----------- Cragsley Outfitters | 2023-Q1 | 1231 | 72 | 5.85 Cragsley Outfitters | 2023-Q2 | 1426 | 75 | 5.26 Cragsley Outfitters | 2023-Q3 | 1503 | 80 | 5.32 Cragsley Outfitters | 2023-Q4 | 2012 | 97 | 4.82 Cragsley Outfitters | 2024-Q1 | 1815 | 101 | 5.56 Cragsley Outfitters | 2024-Q2 | 2118 | 132 | 6.23 Cragsley Outfitters | 2024-Q3 | 2213 | 119 | 5.38 Cragsley Outfitters | 2024-Q4 | 2758 | 141 | 5.11 Cragsley Outfitters | 2025-Q1 | 6609 | 4512 | 68.27 Cragsley Outfitters | 2025-Q2 | 2418 | 132 | 5.46 Cragsley Outfitters | 2025-Q3 | 2531 | 144 | 5.69 Cragsley Outfitters | 2025-Q4 | 3353 | 199 | 5.93 Marrowby & Vane | 2023-Q1 | 598 | 29 | 4.85 Marrowby & Vane | 2023-Q2 | 653 | 35 | 5.36 Marrowby & Vane | 2023-Q3 | 726 | 44 | 6.06 Marrowby & Vane | 2023-Q4 | 1055 | 52 | 4.93 Marrowby & Vane | 2024-Q1 | 882 | 52 | 5.9 Marrowby & Vane | 2024-Q2 | 966 | 69 | 7.14 Marrowby & Vane | 2024-Q3 | 1041 | 62 | 5.96 Marrowby & Vane | 2024-Q4 | 1305 | 76 | 5.82 Marrowby & Vane | 2025-Q1 | 3127 | 2028 | 64.85 Marrowby & Vane | 2025-Q2 | 1191 | 70 | 5.88 Marrowby & Vane | 2025-Q3 | 1227 | 75 | 6.11 Marrowby & Vane | 2025-Q4 | 1646 | 86 | 5.22 Studio Vrij | 2023-Q1 | 343 | 25 | 7.29 Studio Vrij | 2023-Q2 | 355 | 20 | 5.63 Studio Vrij | 2023-Q3 | 388 | 21 | 5.41 Studio Vrij | 2023-Q4 | 454 | 24 | 5.29 Studio Vrij | 2024-Q1 | 389 | 28 | 7.2 Studio Vrij | 2024-Q2 | 402 | 28 | 6.97 Studio Vrij | 2024-Q3 | 415 | 14 | 3.37 Studio Vrij | 2024-Q4 | 564 | 26 | 4.61 Studio Vrij | 2025-Q1 | 1305 | 806 | 61.76 Studio Vrij | 2025-Q2 | 617 | 32 | 5.19 Studio Vrij | 2025-Q3 | 522 | 34 | 6.51 Studio Vrij | 2025-Q4 | 682 | 30 | 4.4 (36 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH base AS ( SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num, c.quarter_num SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num AS year_num, c.quarter_num AS quarter_num FROM tenants t CROSS JOIN ( SELECT DISTINCT year_num, quarter_num FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ) c WHERE t.name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij'))SELECT b.tenant_name AS tenant_name, b.year_num || '-Q' || b.quarter_num AS quarter, COALESCE(ta.total_attempts, 0) AS total_attempts, COALESCE(fa.failed_attempts, 0) AS failed_attempts, CASE WHEN COALESCE(ta.total_attempts, 0) = 0 THEN 0 ELSE ROUND(100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts, 2) ELSE 100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts END AS pct_failedFROM base bLEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS total_attempts SELECT la.tenant_id AS tenant_id, cd.year_num AS year_num, cd.quarter_num AS quarter_num, COUNT(*) AS total_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) GROUP BY la.tenant_id, cd.year_num, cd.quarter_num) ta ON ta.tenant_id = b.tenant_id AND ta.year_num = b.year_num AND ta.quarter_num = b.quarter_numLEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS failed_attempts SELECT la.tenant_id AS tenant_id, cd.year_num AS year_num, cd.quarter_num AS quarter_num, COUNT(*) AS failed_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) WHERE la.succeeded = false GROUP BY la.tenant_id, cd.year_num, cd.quarter_num) fa ON fa.tenant_id = b.tenant_id AND fa.year_num = b.year_num AND fa.quarter_num = b.quarter_numORDER BY b.tenant_name, b.year_num, b.quarter_numORDER BY b.tenant_name NULLS LAST, b.year_num NULLS LAST, b.quarter_num NULLS LAST, b.tenant_id NULLS LAST
WITH base AS ( SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num AS year_num, c.quarter_num AS quarter_num FROM tenants t CROSS JOIN ( SELECT DISTINCT year_num, quarter_num FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ) c WHERE t.name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij') ) SELECT b.tenant_name AS tenant_name, b.year_num || '-Q' || b.quarter_num AS quarter, COALESCE(ta.total_attempts, 0) AS total_attempts, COALESCE(fa.failed_attempts, 0) AS failed_attempts, CASE WHEN COALESCE(ta.total_attempts, 0) = 0 THEN 0 ELSE 100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts END AS pct_failed FROM base b LEFT JOIN ( SELECT la.tenant_id AS tenant_id, cd.year_num AS year_num, cd.quarter_num AS quarter_num, COUNT(*) AS total_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) ta ON ta.tenant_id = b.tenant_id AND ta.year_num = b.year_num AND ta.quarter_num = b.quarter_num LEFT JOIN ( SELECT la.tenant_id AS tenant_id, cd.year_num AS year_num, cd.quarter_num AS quarter_num, COUNT(*) AS failed_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) WHERE la.succeeded = false GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) fa ON fa.tenant_id = b.tenant_id AND fa.year_num = b.year_num AND fa.quarter_num = b.quarter_num ORDER BY b.tenant_name NULLS LAST, b.year_num NULLS LAST, b.quarter_num NULLS LAST, b.tenant_id NULLS LAST
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 Claire Dubois (analyst for the Head of Risk and Payments): Title: Quarterly failed-login rate history for the 3 Feb-2025-affected merchants Question: For the 3 merchants behind the February 2025 spike, was that an isolated one-quarter event, or do they show a recurring pattern of elevated login failures over the full history? Grain: one row per combination of tenant (the same 3 merchants) and calendar quarter, from 2023-Q1 through 2025-Q4 Columns: - tenant_name - quarter (YYYY-Q#) - total_attempts - failed_attempts - pct_failed Details: Source: login_attempts, filtered to these 3 tenants (Cragsley Outfitters, Marrowby & Vane, Studio Vrij), all quarters 2023-2025 (12 quarters x 3 tenants = 36 rows, include quarters with zero activity as 0 rather than omitting). Purpose: context for whether Feb 2025 (Q1 2025) was a one-off anomaly or whether these merchants — already flagged elsewhere for the heaviest support ticket load and worst response times — show other elevated-failure quarters too. Order by tenant_name, then quarter ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH base AS ( SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num, c.quarter_num FROM tenants t CROSS JOIN ( SELECT DISTINCT year_num, quarter_num FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ) c WHERE t.name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij') ) SELECT b.tenant_name AS tenant_name, b.year_num || '-Q' || b.quarter_num AS quarter, COALESCE(ta.total_attempts, 0) AS total_attempts, COALESCE(fa.failed_attempts, 0) AS failed_attempts, CASE WHEN COALESCE(ta.total_attempts, 0) = 0 THEN 0 ELSE ROUND(100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts, 2) END AS pct_failed FROM base b LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS total_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) ta ON ta.tenant_id = b.tenant_id AND ta.year_num = b.year_num AND ta.quarter_num = b.quarter_num LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS failed_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) WHERE la.succeeded = false GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) fa ON fa.tenant_id = b.tenant_id AND fa.year_num = b.year_num AND fa.quarter_num = b.quarter_num ORDER BY b.tenant_name, b.year_num, b.quarter_num
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, quarter, total_attempts, failed_attempts, pct_failed
submitWITH base AS ( SELECT t.id AS tenant_id, t.name AS tenant_name, c.year_num, c.quarter_num FROM tenants t CROSS JOIN ( SELECT DISTINCT year_num, quarter_num FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ) c WHERE t.name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij') ) SELECT b.tenant_name AS tenant_name, b.year_num || '-Q' || b.quarter_num AS quarter, COALESCE(ta.total_attempts, 0) AS total_attempts, COALESCE(fa.failed_attempts, 0) AS failed_attempts, CASE WHEN COALESCE(ta.total_attempts, 0) = 0 THEN 0 ELSE ROUND(100.0 * COALESCE(fa.failed_attempts, 0) / ta.total_attempts, 2) END AS pct_failed FROM base b LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS total_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) ta ON ta.tenant_id = b.tenant_id AND ta.year_num = b.year_num AND ta.quarter_num = b.quarter_num LEFT JOIN ( SELECT la.tenant_id, cd.year_num, cd.quarter_num, COUNT(*) AS failed_attempts FROM login_attempts la JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS DATE) WHERE la.succeeded = false GROUP BY la.tenant_id, cd.year_num, cd.quarter_num ) fa ON fa.tenant_id = b.tenant_id AND fa.year_num = b.year_num AND fa.quarter_num = b.quarter_num ORDER BY b.tenant_name, b.year_num, b.quarter_num
Submitted to tonight's batch. Your work on this request is done.