Queries · q283 · Head of Risk and Payments · week 2, night 3

February 2025 failed-login pattern diagnostics for the 3 affected merchants

“At the 3 merchants that drove the February 2025 spike, do the failed logins look automated/bot-like (concentrated in a narrow time window, few user agents) or more like a broad client-side bug (spread across the month, many different user agents)?”

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

1. 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

2. Aylin Demir writes to Claire Dubois

   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:

   1. On the February 2025 spike — did it cluster around a handful of accounts, merchants, or sources, or was it spread evenly? And did any of the accounts that failed repeatedly go on to log in successfully shortly after? That tells us whether this was noise or an actual near-miss.
   2. On the three unresolved rejection dates — rather than retrying the same technical path, can we establish, using whatever reliable timestamp we do have, roughly when each decision was made relative to the request date? I don't need the exact figure if it's not retrievable — I need to know whether we were plausibly within the deadline or not, so we can decide how to word that answer.
   3. On flagged incidents (the one we deferred) — now that we're another week in, I want this one: how many incidents have been flagged, how severe, how long they typically take to close, and whether anything has sat open a long time. This is likely to come up in the same breath as the login spike.
   4. On the tables with no retention policy — before I escalate this as a gap, I want to know how exposed we actually are: roughly how far back does that data go today? If it's a few months, this is a minor cleanup; if it's three years, it's a bigger conversation with Rosa.

   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

   Show the whole note

3. Claire Dubois plans the night

   The analyst · week 2, night 3, request 2 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.

   Read the whole plan
   February 2025 failed-login pattern diagnostics for the 3 affected merchants

   Question

   At the 3 merchants that drove the February 2025 spike, do the failed logins look automated/bot-like (concentrated in a narrow time window, few user agents) or more like a broad client-side bug (spread across the month, many different user agents)?

   One row per

   tenant, for the 3 merchants identified last week (Cragsley Outfitters, Marrowby & Vane, Studio Vrij), restricted to their failed login_attempts in February 2025

   Columns

   • tenant_name
   • distinct_user_agents
   • top_user_agent_share_pct (share of this tenant's Feb failed attempts from the single most common user_agent string)
   • distinct_calendar_days_with_failures (out of 28)
   • max_failed_attempts_single_day
   • pct_failed_attempts_in_top_3_days

4. 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 failed_logins as (
     select
       t.id as tenant_id,
       t.name as tenant_name,
       la.user_agent,
       cast(la.occurred_at as date) as failure_day
     from login_attempts la
     join tenants t on t.id = la.tenant_id
     where t.name in ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij')
       and la.succeeded = false
       and la.occurred_at >= timestamp '2025-02-01'
       and la.occurred_at < timestamp '2025-03-01'
   ),
   tenant_totals as (
     select tenant_id, tenant_name, count(*) as total_failed
     from failed_logins
     group by tenant_id, tenant_name
   ),
   user_agent_counts as (
     select tenant_id, user_agent, count(*) as ua_count
     from failed_logins
     group by tenant_id, user_agent
   ),
   distinct_ua as (
     select tenant_id, count(distinct user_agent) as distinct_user_agents
     from failed_logins
     group by tenant_id
   ),
   top_ua as (
     select tenant_id, max(ua_count) as top_ua_count
     from user_agent_counts
     group by tenant_id
   ),
   day_counts as (
     select tenant_id, failure_day, count(*) as day_count
     from failed_logins
     group by tenant_id, failure_day
   ),
   distinct_days as (
     select tenant_id, count(distinct failure_day) as distinct_calendar_days_with_failures
     from failed_logins
     group by tenant_id
   ),
   max_day as (
     select tenant_id, max(day_count) as max_failed_attempts_single_day
     from day_counts
     group by tenant_id
   ),
   ranked_days as (
     select tenant_id, day_count,
       row_number() over (partition by tenant_id order by day_count desc) as rn
     from day_counts
   ),
   top3_days as (
     select tenant_id, sum(day_count) as top3_sum
     from ranked_days
     where rn <= 3
     group by tenant_id
   )
   select
     tt.tenant_name,
     du.distinct_user_agents,
     round(100.0 * tu.top_ua_count / tt.total_failed, 1) as top_user_agent_share_pct,
     dd.distinct_calendar_days_with_failures,
     md.max_failed_attempts_single_day,
     round(100.0 * t3.top3_sum / tt.total_failed, 1) as pct_failed_attempts_in_top_3_days
   from tenant_totals tt
   join distinct_ua du on du.tenant_id = tt.tenant_id
   join top_ua tu on tu.tenant_id = tt.tenant_id
   join distinct_days dd on dd.tenant_id = tt.tenant_id
   join max_day md on md.tenant_id = tt.tenant_id
   join top3_days t3 on t3.tenant_id = tt.tenant_id
   order by tt.tenant_name

   “Query compiles correctly and produces the expected 6 columns at the right 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 gives one row per tenant (Cragsley Outfitters, Marrowby & Vane, Studio Vrij) with distinct user agents, the top user agent's share of failed attempts, how many of February's 28 days saw at least one failure, the single worst day's failure count, and the share of failures concentrated in the top 3 days — which together should make the bot-burst vs. broad-bug distinction clear.”

5. The report comes back overnight

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

   tenant_name         | distinct_user_agents | top_user_agent_share_pct | distinct_calendar_days_with_failures | max_failed_attempts_single_day | pct_failed_attempts_in_top_3_days
   --------------------+----------------------+--------------------------+--------------------------------------+--------------------------------+----------------------------------
   Cragsley Outfitters |                   17 |                     25.4 |                                   21 |                           1091 |                              74.2
   Marrowby & Vane     |                   11 |                     25.7 |                                   11 |                            493 |                              74.6
   Studio Vrij         |                    8 |                     26.7 |                                    6 |                            196 |                              74.8
   (3 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q286Daily failed-login and access-denied alignment for the 3 merchants in February 2025

   It builds on:

   • q276February 2025 failed login concentration by merchant

7. It becomes benchmark query q283

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

   • determinismtie-breaker: tenant_totals is keyed by tenant_id
   • 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 (5×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
   The benchmark text, against the submitted SQL

   with failed_logins as (  select    t.id as tenant_id,    t.name as tenant_name,    la.user_agent,    la.user_agent AS user_agent,    cast(la.occurred_at as date) as failure_day  from login_attempts la  join tenants t on t.id = la.tenant_id  where t.name in ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij')    and la.succeeded = false    and la.occurred_at >= timestamp '2025-02-01'    and la.occurred_at < timestamp '2025-03-01'),tenant_totals as (  select tenant_id, tenant_name, count(*) as total_failed  from failed_logins  group by tenant_id, tenant_name),user_agent_counts as (  select tenant_id, user_agent, count(*) as ua_count  from failed_logins  group by tenant_id, user_agent),distinct_ua as (  select tenant_id, count(distinct user_agent) as distinct_user_agents  from failed_logins  group by tenant_id),top_ua as (  select tenant_id, max(ua_count) as top_ua_count  from user_agent_counts  group by tenant_id),day_counts as (  select tenant_id, failure_day, count(*) as day_count  from failed_logins  group by tenant_id, failure_day),distinct_days as (  select tenant_id, count(distinct failure_day) as distinct_calendar_days_with_failures  from failed_logins  group by tenant_id),max_day as (  select tenant_id, max(day_count) as max_failed_attempts_single_day  from day_counts  group by tenant_id),ranked_days as (  select tenant_id, day_count,    row_number() over (partition by tenant_id order by day_count desc) as rn    row_number() over (partition by tenant_id order by day_count desc NULLS FIRST) as rn  from day_counts),top3_days as (  select tenant_id, sum(day_count) as top3_sum  from ranked_days  where rn <= 3  group by tenant_id)select  tt.tenant_name,  du.distinct_user_agents,  round(100.0 * tu.top_ua_count / tt.total_failed, 1) as top_user_agent_share_pct,  dd.distinct_calendar_days_with_failures,  md.max_failed_attempts_single_day,  round(100.0 * t3.top3_sum / tt.total_failed, 1) as pct_failed_attempts_in_top_3_days  tt.tenant_name AS tenant_name,  du.distinct_user_agents AS distinct_user_agents,  100.0 * tu.top_ua_count / tt.total_failed as top_user_agent_share_pct,  dd.distinct_calendar_days_with_failures AS distinct_calendar_days_with_failures,  md.max_failed_attempts_single_day AS max_failed_attempts_single_day,  100.0 * t3.top3_sum / tt.total_failed as pct_failed_attempts_in_top_3_daysfrom tenant_totals ttjoin distinct_ua du on du.tenant_id = tt.tenant_idjoin top_ua tu on tu.tenant_id = tt.tenant_idjoin distinct_days dd on dd.tenant_id = tt.tenant_idjoin max_day md on md.tenant_id = tt.tenant_idjoin top3_days t3 on t3.tenant_id = tt.tenant_idorder by tt.tenant_nameorder by tt.tenant_name NULLS LAST, tt.tenant_id NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   with failed_logins as (
     select
       t.id as tenant_id,
       t.name as tenant_name,
       la.user_agent AS user_agent,
       cast(la.occurred_at as date) as failure_day
     from login_attempts la
     join tenants t on t.id = la.tenant_id
     where t.name in ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij')
       and la.succeeded = false
       and la.occurred_at >= timestamp '2025-02-01'
       and la.occurred_at < timestamp '2025-03-01'
   ),
   tenant_totals as (
     select tenant_id, tenant_name, count(*) as total_failed
     from failed_logins
     group by tenant_id, tenant_name
   ),
   user_agent_counts as (
     select tenant_id, user_agent, count(*) as ua_count
     from failed_logins
     group by tenant_id, user_agent
   ),
   distinct_ua as (
     select tenant_id, count(distinct user_agent) as distinct_user_agents
     from failed_logins
     group by tenant_id
   ),
   top_ua as (
     select tenant_id, max(ua_count) as top_ua_count
     from user_agent_counts
     group by tenant_id
   ),
   day_counts as (
     select tenant_id, failure_day, count(*) as day_count
     from failed_logins
     group by tenant_id, failure_day
   ),
   distinct_days as (
     select tenant_id, count(distinct failure_day) as distinct_calendar_days_with_failures
     from failed_logins
     group by tenant_id
   ),
   max_day as (
     select tenant_id, max(day_count) as max_failed_attempts_single_day
     from day_counts
     group by tenant_id
   ),
   ranked_days as (
     select tenant_id, day_count,
       row_number() over (partition by tenant_id order by day_count desc NULLS FIRST) as rn
     from day_counts
   ),
   top3_days as (
     select tenant_id, sum(day_count) as top3_sum
     from ranked_days
     where rn <= 3
     group by tenant_id
   )
   select
     tt.tenant_name AS tenant_name,
     du.distinct_user_agents AS distinct_user_agents,
     100.0 * tu.top_ua_count / tt.total_failed as top_user_agent_share_pct,
     dd.distinct_calendar_days_with_failures AS distinct_calendar_days_with_failures,
     md.max_failed_attempts_single_day AS max_failed_attempts_single_day,
     100.0 * t3.top3_sum / tt.total_failed as pct_failed_attempts_in_top_3_days
   from tenant_totals tt
   join distinct_ua du on du.tenant_id = tt.tenant_id
   join top_ua tu on tu.tenant_id = tt.tenant_id
   join distinct_days dd on dd.tenant_id = tt.tenant_id
   join max_day md on md.tenant_id = tt.tenant_id
   join top3_days t3 on t3.tenant_id = tt.tenant_id
   order by tt.tenant_name NULLS LAST, tt.tenant_id NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q283 on the start page, with every configuration.

8. 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 Claire Dubois (analyst for the Head of Risk and Payments):
     
     Title: February 2025 failed-login pattern diagnostics for the 3 affected merchants
     Question: At the 3 merchants that drove the February 2025 spike, do the failed logins look automated/bot-like (concentrated in a narrow time window, few user agents) or more like a broad client-side bug (spread across the month, many different user agents)?
     Grain: one row per tenant, for the 3 merchants identified last week (Cragsley Outfitters, Marrowby & Vane, Studio Vrij), restricted to their failed login_attempts in February 2025
     Columns:
     - tenant_name
     - distinct_user_agents
     - top_user_agent_share_pct (share of this tenant's Feb failed attempts from the single most common user_agent string)
     - distinct_calendar_days_with_failures (out of 28)
     - max_failed_attempts_single_day
     - pct_failed_attempts_in_top_3_days
     Details: Source: login_attempts, filtered to these 3 tenants and succeeded=false, occurred_at in February 2025. Purpose: distinguish a likely automated/attack pattern (few user agents, failures clustered in a short burst of days) from a likely broad technical/client bug (many distinct user agents similar to normal traffic, failures spread fairly evenly across most of the month) — directly feeds Aylin's read on whether this was a near-miss security event or an internal issue. Order by tenant_name.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Grace calls dry_run

     with failed_logins as (
       select
         t.id as tenant_id,
         t.name as tenant_name,
         la.user_agent,
         cast(la.occurred_at as date) as failure_day
       from login_attempts la
       join tenants t on t.id = la.tenant_id
       where t.name in ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij')
         and la.succeeded = false
         and la.occurred_at >= timestamp '2025-02-01'
         and la.occurred_at < timestamp '2025-03-01'
     ),
     tenant_totals as (
       select tenant_id, tenant_name, count(*) as total_failed
       from failed_logins
       group by tenant_id, tenant_name
     ),
     user_agent_counts as (
       select tenant_id, user_agent, count(*) as ua_count
       from failed_logins
       group by tenant_id, user_agent
     ),
     distinct_ua as (
       select tenant_id, count(distinct user_agent) as distinct_user_agents
       from failed_logins
       group by tenant_id
     ),
     top_ua as (
       select tenant_id, max(ua_count) as top_ua_count
       from user_agent_counts
       group by tenant_id
     ),
     day_counts as (
       select tenant_id, failure_day, count(*) as day_count
       from failed_logins
       group by tenant_id, failure_day
     ),
     distinct_days as (
       select tenant_id, count(distinct failure_day) as distinct_calendar_days_with_failures
       from failed_logins
       group by tenant_id
     ),
     max_day as (
       select tenant_id, max(day_count) as max_failed_attempts_single_day
       from day_counts
       group by tenant_id
     ),
     ranked_days as (
       select tenant_id, day_count,
         row_number() over (partition by tenant_id order by day_count desc) as rn
       from day_counts
     ),
     top3_days as (
       select tenant_id, sum(day_count) as top3_sum
       from ranked_days
       where rn <= 3
       group by tenant_id
     )
     select
       tt.tenant_name,
       du.distinct_user_agents,
       round(100.0 * tu.top_ua_count / tt.total_failed, 1) as top_user_agent_share_pct,
       dd.distinct_calendar_days_with_failures,
       md.max_failed_attempts_single_day,
       round(100.0 * t3.top3_sum / tt.total_failed, 1) as pct_failed_attempts_in_top_3_days
     from tenant_totals tt
     join distinct_ua du on du.tenant_id = tt.tenant_id
     join top_ua tu on tu.tenant_id = tt.tenant_id
     join distinct_days dd on dd.tenant_id = tt.tenant_id
     join max_day md on md.tenant_id = tt.tenant_id
     join top3_days t3 on t3.tenant_id = tt.tenant_id
     order by tt.tenant_name

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, distinct_user_agents, top_user_agent_share_pct, distinct_calendar_days_with_failures, max_failed_attempts_single_day, pct_failed_attempts_in_top_3_days

   • GraceQuery compiles correctly and produces the expected 6 columns at the right grain. Submitting.
   • Grace calls submit

     with failed_logins as (
       select
         t.id as tenant_id,
         t.name as tenant_name,
         la.user_agent,
         cast(la.occurred_at as date) as failure_day
       from login_attempts la
       join tenants t on t.id = la.tenant_id
       where t.name in ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij')
         and la.succeeded = false
         and la.occurred_at >= timestamp '2025-02-01'
         and la.occurred_at < timestamp '2025-03-01'
     ),
     tenant_totals as (
       select tenant_id, tenant_name, count(*) as total_failed
       from failed_logins
       group by tenant_id, tenant_name
     ),
     user_agent_counts as (
       select tenant_id, user_agent, count(*) as ua_count
       from failed_logins
       group by tenant_id, user_agent
     ),
     distinct_ua as (
       select tenant_id, count(distinct user_agent) as distinct_user_agents
       from failed_logins
       group by tenant_id
     ),
     top_ua as (
       select tenant_id, max(ua_count) as top_ua_count
       from user_agent_counts
       group by tenant_id
     ),
     day_counts as (
       select tenant_id, failure_day, count(*) as day_count
       from failed_logins
       group by tenant_id, failure_day
     ),
     distinct_days as (
       select tenant_id, count(distinct failure_day) as distinct_calendar_days_with_failures
       from failed_logins
       group by tenant_id
     ),
     max_day as (
       select tenant_id, max(day_count) as max_failed_attempts_single_day
       from day_counts
       group by tenant_id
     ),
     ranked_days as (
       select tenant_id, day_count,
         row_number() over (partition by tenant_id order by day_count desc) as rn
       from day_counts
     ),
     top3_days as (
       select tenant_id, sum(day_count) as top3_sum
       from ranked_days
       where rn <= 3
       group by tenant_id
     )
     select
       tt.tenant_name,
       du.distinct_user_agents,
       round(100.0 * tu.top_ua_count / tt.total_failed, 1) as top_user_agent_share_pct,
       dd.distinct_calendar_days_with_failures,
       md.max_failed_attempts_single_day,
       round(100.0 * t3.top3_sum / tt.total_failed, 1) as pct_failed_attempts_in_top_3_days
     from tenant_totals tt
     join distinct_ua du on du.tenant_id = tt.tenant_id
     join top_ua tu on tu.tenant_id = tt.tenant_id
     join distinct_days dd on dd.tenant_id = tt.tenant_id
     join max_day md on md.tenant_id = tt.tenant_id
     join top3_days t3 on t3.tenant_id = tt.tenant_id
     order by tt.tenant_name

   • The warehouse answers

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

   • GraceSubmitted. The report gives one row per tenant (Cragsley Outfitters, Marrowby & Vane, Studio Vrij) with distinct user agents, the top user agent's share of failed attempts, how many of February's 28 days saw at least one failure, the single worst day's failure count, and the share of failures concentrated in the top 3 days — which together should make the bot-burst vs. broad-bug distinction clear.

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