Queries · q276 · Head of Risk and Payments · week 2, night 1

February 2025 failed login concentration by merchant

“Did the February 2025 failed-login spike concentrate in a handful of merchants (and within them, a handful of emails or IPs), or was it spread evenly across the platform?”

Manager · asked the questionAylin DemirHead of Risk and Payments. Previously in fraud operations at a bank. Responsible for payment performance and for keeping bad actors out.

Analyst · planned the reportClaire DuboisTime-series minded: weekly and daily trends, before and after, seasonality.

Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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

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
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
Claire Dubois plans the night
The analyst · week 2, night 1, request 1 of 3
Aylin flagged the Feb-2025 login spike as her top worry this week and split it into two distinct questions (concentration vs. near-miss outcome), so I'm giving it two of tonight's three slots since they're different grains (merchant-level vs. account-outcome-level) and shouldn't be bundled. For the third slot I'm taking a cheap, low-risk shot at the DSAR rejection-timing question using a completely different, simpler path than the one that broke twice last week: data_subject_requests.updated_at itself, with no JSON/change_history involved — if these 3 records weren't touched again after rejection, updated_at is a reasonable (if imperfect) proxy for "when was this decided," and it avoids re-spending slots on the broken query. I'm holding incidents (#3) and the retention-exposure check (#4) for night 2, since we have 4 nights this week and the spike is clearly the most time-sensitive item. Keeping outputs aggregate (merchant names are fine, per other teams' precedent; no individual emails/usernames).
February 2025 failed login concentration by merchant
Question
Did the February 2025 failed-login spike concentrate in a handful of merchants (and within them, a handful of emails or IPs), or was it spread evenly across the platform?
One row per
tenant (merchant), restricted to login_attempts with occurred_at between 2025-02-01 and 2025-02-28 inclusive (UTC)
Columns
- tenant_name
- total_attempts_feb
- failed_attempts_feb
- pct_failed_feb
- distinct_emails_with_any_failed_attempt
- distinct_ips_with_any_failed_attempt
- max_failed_attempts_single_ip
- max_failed_attempts_single_email

Felix 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)

SELECT
  t.name AS tenant_name,
  COUNT(*) AS total_attempts_feb,
  SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) AS failed_attempts_feb,
  ROUND(100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS pct_failed_feb,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.email END) AS distinct_emails_with_any_failed_attempt,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.ip_address END) AS distinct_ips_with_any_failed_attempt,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la2
      WHERE la2.tenant_id = t.id
        AND la2.succeeded = false
        AND la2.occurred_at >= DATE '2025-02-01'
        AND la2.occurred_at < DATE '2025-03-01'
      GROUP BY la2.ip_address
  ) ip_counts) AS max_failed_attempts_single_ip,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la3
      WHERE la3.tenant_id = t.id
        AND la3.succeeded = false
        AND la3.occurred_at >= DATE '2025-02-01'
        AND la3.occurred_at < DATE '2025-03-01'
      GROUP BY la3.email
  ) email_counts) AS max_failed_attempts_single_email
FROM login_attempts la
JOIN tenants t ON t.id = la.tenant_id
WHERE la.occurred_at >= DATE '2025-02-01'
  AND la.occurred_at < DATE '2025-03-01'
GROUP BY t.id, t.name
ORDER BY failed_attempts_feb DESC, tenant_name ASC

“Query compiles cleanly with the right columns. Submitting now.”

Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

“Submitted. The query computes, per tenant, total vs. failed February 2025 login attempts, the failure rate, and concentration signals (distinct emails/IPs touched by failures, plus the single busiest IP and email by failed-attempt count) — exactly what's needed to distinguish a concentrated credential-stuffing pattern from a platform-wide bug/outage.”

The report comes back overnight

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

tenant_name            | total_attempts_feb | failed_attempts_feb | pct_failed_feb | distinct_emails_with_any_failed_attempt | distinct_ips_with_any_failed_attempt | max_failed_attempts_single_ip | max_failed_attempts_single_email
-----------------------+--------------------+---------------------+----------------+-----------------------------------------+--------------------------------------+-------------------------------+---------------------------------
Cragsley Outfitters    |               5005 |                4407 |           88.1 |                                    2786 |                                 1903 |                             9 |                                8
Marrowby & Vane        |               2297 |                1978 |           86.1 |                                    1243 |                                 1265 |                             7 |                                7
Studio Vrij            |                930 |                 783 |           84.2 |                                     481 |                                  642 |                             4 |                                7
Brennquell Kaffee      |                306 |                  17 |            5.6 |                                      11 |                                   11 |                             2 |                                2
Gratlinie Outdoor      |                 86 |                   9 |           10.5 |                                       6 |                                    5 |                             3 |                                2
Honeycomb Larder       |                 89 |                   9 |           10.1 |                                       6 |                                    5 |                             3 |                                2
Noordzee Surfwear      |                 49 |                   9 |           18.4 |                                       5 |                                    5 |                             2 |                                2
OfficeHub Nordic       |                 49 |                   8 |           16.3 |                                       4 |                                    4 |                             2 |                                2
Petalcourt Beauty      |                161 |                   8 |            5.0 |                                       5 |                                    5 |                             2 |                                2
Ampfeld Elektronik     |                137 |                   7 |            5.1 |                                       5 |                                    5 |                             2 |                                2
Cinder Peak Sports Co. |                 94 |                   6 |            6.4 |                                       2 |                                    1 |                             6 |                                4
Linden & Loom          |                 85 |                   6 |            7.1 |                                       4 |                                    4 |                             2 |                                2
Nordic Bloom Cosmetics |                 49 |                   6 |           12.2 |                                       3 |                                    2 |                             4 |                                3
Sourdough Club         |                 93 |                   6 |            6.5 |                                       4 |                                    3 |                             3 |                                2
Spice Route Box        |                 77 |                   6 |            7.8 |                                       3 |                                    2 |                             4 |                                2
Velo Noord             |                 32 |                   6 |           18.8 |                                       4 |                                    2 |                             5 |                                2
Atelier Nouveau        |                 42 |                   5 |           11.9 |                                       3 |                                    3 |                             2 |                                2
Brewline Coffee        |                 24 |                   5 |           20.8 |                                       2 |                                    1 |                             5 |                                3
Castell Fasteners      |                 49 |                   5 |           10.2 |                                       3 |                                    1 |                             5 |                                2
Chocolaterie Mireille  |                 25 |                   5 |           20.0 |                                       4 |                                    4 |                             2 |                                2
Kontor24 Bürobedarf    |                 75 |                   5 |            6.7 |                                       3 |                                    3 |                             2 |                                2
Velvet Ash             |                 55 |                   5 |            9.1 |                                       5 |                                    4 |                             2 |                                1
Wiesgrund Wohnen       |                 89 |                   5 |            5.6 |                                       4 |                                    4 |                             2 |                                2
Deskline Supplies      |                 30 |                   4 |           13.3 |                                       2 |                                    1 |                             4 |                                2
Fell & Fjell           |                 48 |                   4 |            8.3 |                                       2 |                                    2 |                             3 |                                3
Kibblewick             |                 87 |                   4 |            4.6 |                                       3 |                                    2 |                             3 |                                2
Lumen Collective       |                 40 |                   4 |           10.0 |                                       2 |                                    2 |                             2 |                                2
Midland Safety Wear    |                 41 |                   4 |            9.8 |                                       3 |                                    3 |                             2 |                                2
Northstar Watches      |                 28 |                   4 |           14.3 |                                       2 |                                    2 |                             3 |                                3
Ostwind Mode           |                 46 |                   4 |            8.7 |                                       2 |                                    1 |                             4 |                                3
Strandgut              |                 27 |                   4 |           14.8 |                                       2 |                                    2 |                             2 |                                2
Willow & Wren          |                 54 |                   4 |            7.4 |                                       2 |                                    2 |                             2 |                                2
Courtside Collective   |                 31 |                   3 |            9.7 |                                       2 |                                    1 |                             3 |                                2
Hanse Industriebedarf  |                 29 |                   3 |           10.3 |                                       2 |                                    1 |                             3 |                                2
Maison Clair           |                 34 |                   3 |            8.8 |                                       2 |                                    2 |                             2 |                                2
Meadow Row             |                 25 |                   3 |           12.0 |                                       2 |                                    2 |                             2 |                                2
Mirelle                |                 23 |                   3 |           13.0 |                                       2 |                                    2 |                             2 |                                2
Ondine Botanicals      |                 24 |                   3 |           12.5 |                                       2 |                                    1 |                             3 |                                2
Polder Packaging       |                 23 |                   3 |           13.0 |                                       3 |                                    2 |                             2 |                                1
Ridgeback Gear         |                 45 |                   3 |            6.7 |                                       2 |                                    2 |                             2 |                                2
Roast Republic         |                 26 |                   3 |           11.5 |                                       2 |                                    2 |                             2 |                                2
Saffron & Sage         |                 43 |                   3 |            7.0 |                                       1 |                                    1 |                             3 |                                3
Thistlecombe           |                 34 |                   3 |            8.8 |                                       2 |                                    2 |                             2 |                                2
Tiny Atelier           |                 29 |                   3 |           10.3 |                                       2 |                                    2 |                             2 |                                2
Trailmark              |                 35 |                   3 |            8.6 |                                       2 |                                    2 |                             2 |                                2
Wohnglück              |                 51 |                   3 |            5.9 |                                       2 |                                    1 |                             3 |                                2
Basecamp Nord          |                 31 |                   2 |            6.5 |                                       2 |                                    1 |                             2 |                                1
Bijoux Lune            |                 30 |                   2 |            6.7 |                                       1 |                                    1 |                             2 |                                2
Brick & Bobbin         |                 23 |                   2 |            8.7 |                                       2 |                                    2 |                             1 |                                1
Buchstabe              |                 24 |                   2 |            8.3 |                                       1 |                                    1 |                             2 |                                2
(92 rows, first 50 printed)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
- q074Failed login attempts and suspicious access patterns by month

It becomes benchmark query q276

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

determinismtie-breaker: tenant names are not unique
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
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

SELECT  t.name AS tenant_name,  COUNT(*) AS total_attempts_feb,  SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) AS failed_attempts_feb,  ROUND(100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS pct_failed_feb,  100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS pct_failed_feb,  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.email END) AS distinct_emails_with_any_failed_attempt,  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.ip_address END) AS distinct_ips_with_any_failed_attempt,  (SELECT MAX(cnt) FROM (      SELECT COUNT(*) AS cnt      FROM login_attempts la2      WHERE la2.tenant_id = t.id        AND la2.succeeded = false        AND la2.occurred_at >= DATE '2025-02-01'        AND la2.occurred_at < DATE '2025-03-01'      GROUP BY la2.ip_address  ) ip_counts) AS max_failed_attempts_single_ip,  (SELECT MAX(cnt) FROM (      SELECT COUNT(*) AS cnt      FROM login_attempts la3      WHERE la3.tenant_id = t.id        AND la3.succeeded = false        AND la3.occurred_at >= DATE '2025-02-01'        AND la3.occurred_at < DATE '2025-03-01'      GROUP BY la3.email  ) email_counts) AS max_failed_attempts_single_emailFROM login_attempts laJOIN tenants t ON t.id = la.tenant_idWHERE la.occurred_at >= DATE '2025-02-01'  AND la.occurred_at < DATE '2025-03-01'GROUP BY t.id, t.nameORDER BY failed_attempts_feb DESC, tenant_name ASCORDER BY failed_attempts_feb DESC NULLS FIRST, tenant_name ASC NULLS LAST, t.id NULLS LAST

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

SELECT
  t.name AS tenant_name,
  COUNT(*) AS total_attempts_feb,
  SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) AS failed_attempts_feb,
  100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS pct_failed_feb,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.email END) AS distinct_emails_with_any_failed_attempt,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.ip_address END) AS distinct_ips_with_any_failed_attempt,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la2
      WHERE la2.tenant_id = t.id
        AND la2.succeeded = false
        AND la2.occurred_at >= DATE '2025-02-01'
        AND la2.occurred_at < DATE '2025-03-01'
      GROUP BY la2.ip_address
  ) ip_counts) AS max_failed_attempts_single_ip,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la3
      WHERE la3.tenant_id = t.id
        AND la3.succeeded = false
        AND la3.occurred_at >= DATE '2025-02-01'
        AND la3.occurred_at < DATE '2025-03-01'
      GROUP BY la3.email
  ) email_counts) AS max_failed_attempts_single_email
FROM login_attempts la
JOIN tenants t ON t.id = la.tenant_id
WHERE la.occurred_at >= DATE '2025-02-01'
  AND la.occurred_at < DATE '2025-03-01'
GROUP BY t.id, t.name
ORDER BY failed_attempts_feb DESC NULLS FIRST, tenant_name ASC NULLS LAST, t.id NULLS LAST

DataFusionSchema error: No field named ip_counts.tenant_id. Did you mean 'ip_counts.cnt'?

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

Felix's whole session, 7 turns

The request, as Felix received it

Report request from Claire Dubois (analyst for the Head of Risk and Payments):

Title: February 2025 failed login concentration by merchant
Question: Did the February 2025 failed-login spike concentrate in a handful of merchants (and within them, a handful of emails or IPs), or was it spread evenly across the platform?
Grain: one row per tenant (merchant), restricted to login_attempts with occurred_at between 2025-02-01 and 2025-02-28 inclusive (UTC)
Columns:
- tenant_name
- total_attempts_feb
- failed_attempts_feb
- pct_failed_feb
- distinct_emails_with_any_failed_attempt
- distinct_ips_with_any_failed_attempt
- max_failed_attempts_single_ip
- max_failed_attempts_single_email
Details: Source: login_attempts joined to tenants for the name. Filter strictly to February 2025. Order by failed_attempts_feb descending so the most-affected merchants sort to the top; note the total number of tenants with any February login activity even if only the top 50 are printed. Purpose: see whether the spike is a handful of merchants/accounts/sources (a likely credential-stuffing or targeted-account event) versus a platform-wide even rise (more consistent with a client bug or outage).

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

Felix calls dry_run

SELECT
  t.name AS tenant_name,
  COUNT(*) AS total_attempts_feb,
  SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) AS failed_attempts_feb,
  ROUND(100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS pct_failed_feb,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.email END) AS distinct_emails_with_any_failed_attempt,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.ip_address END) AS distinct_ips_with_any_failed_attempt,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la2
      WHERE la2.tenant_id = t.id
        AND la2.succeeded = false
        AND la2.occurred_at >= DATE '2025-02-01'
        AND la2.occurred_at < DATE '2025-03-01'
      GROUP BY la2.ip_address
  ) ip_counts) AS max_failed_attempts_single_ip,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la3
      WHERE la3.tenant_id = t.id
        AND la3.succeeded = false
        AND la3.occurred_at >= DATE '2025-02-01'
        AND la3.occurred_at < DATE '2025-03-01'
      GROUP BY la3.email
  ) email_counts) AS max_failed_attempts_single_email
FROM login_attempts la
JOIN tenants t ON t.id = la.tenant_id
WHERE la.occurred_at >= DATE '2025-02-01'
  AND la.occurred_at < DATE '2025-03-01'
GROUP BY t.id, t.name
ORDER BY failed_attempts_feb DESC, tenant_name ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, total_attempts_feb, failed_attempts_feb, pct_failed_feb, distinct_emails_with_any_failed_attempt, distinct_ips_with_any_failed_attempt, max_failed_attempts_single_ip, max_failed_attempts_single_email

FelixQuery compiles cleanly with the right columns. Submitting now.

Felix calls submit

SELECT
  t.name AS tenant_name,
  COUNT(*) AS total_attempts_feb,
  SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) AS failed_attempts_feb,
  ROUND(100.0 * SUM(CASE WHEN la.succeeded = false THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS pct_failed_feb,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.email END) AS distinct_emails_with_any_failed_attempt,
  COUNT(DISTINCT CASE WHEN la.succeeded = false THEN la.ip_address END) AS distinct_ips_with_any_failed_attempt,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la2
      WHERE la2.tenant_id = t.id
        AND la2.succeeded = false
        AND la2.occurred_at >= DATE '2025-02-01'
        AND la2.occurred_at < DATE '2025-03-01'
      GROUP BY la2.ip_address
  ) ip_counts) AS max_failed_attempts_single_ip,
  (SELECT MAX(cnt) FROM (
      SELECT COUNT(*) AS cnt
      FROM login_attempts la3
      WHERE la3.tenant_id = t.id
        AND la3.succeeded = false
        AND la3.occurred_at >= DATE '2025-02-01'
        AND la3.occurred_at < DATE '2025-03-01'
      GROUP BY la3.email
  ) email_counts) AS max_failed_attempts_single_email
FROM login_attempts la
JOIN tenants t ON t.id = la.tenant_id
WHERE la.occurred_at >= DATE '2025-02-01'
  AND la.occurred_at < DATE '2025-03-01'
GROUP BY t.id, t.name
ORDER BY failed_attempts_feb DESC, tenant_name ASC

The warehouse answers

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

FelixSubmitted. The query computes, per tenant, total vs. failed February 2025 login attempts, the failure rate, and concentration signals (distinct emails/IPs touched by failures, plus the single busiest IP and email by failed-attempt count) — exactly what's needed to distinguish a concentrated credential-stuffing pattern from a platform-wide bug/outage.

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

Felix's transcript (266 KB) · Claire Dubois's transcript (838 KB) · Aylin Demir's transcript (263 KB) · the night's plan (4 KB) · the night's reports (15 KB) · the week's memo (3 KB)

February 2025 failed login concentration by merchant

A business event lands on the Head of Risk and Payments's desk

Aylin Demir writes to Claire Dubois

Claire Dubois plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q276

Everything, as recorded