Queries · q100 · Chief Information Security Officer · week 1, night 1

Weekly sign-in attempt volume and failure pattern

“Over the last 26 weeks, how many login attempts are we seeing platform-wide, what share fail, and are there signs of credential-stuffing-style behavior (many attempts concentrated on few IPs)?”

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

1. Henrik Aalto writes to Noah Fischer

   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:

   1. How exposed are we on sign-in security today? I want to understand how merchant staff and shoppers are authenticating — are we seeing risky patterns like repeated failed logins, credential-stuffing-type behavior, or logins from unusual places? I need a feel for the baseline before I can say whether we have a problem.
   2. How strong is our multi-factor authentication posture? I'd like to know roughly how many of our merchants' staff (and ideally shoppers, if meaningful) actually have MFA set up and active, versus how many are relying on password-only access. This matters because it's one of the first things enterprise security reviewers ask about, and I need to know whether we can answer confidently or whether it's a gap we need to close.
   3. Who holds powerful access, and is it still appropriate? I'm interested in how permissions and roles are granted and whether there's good hygiene around it — e.g., are there dormant privileged accounts, or grants that nobody seems to be reviewing. This feeds directly into access-control questions in security audits.
   4. How healthy are our API keys and service accounts? These are often the weakest link — long-lived tokens that nobody rotates. I'd like a sense of how many are active, how old they are, and whether any look abandoned or over-privileged.
   5. Can we actually reconstruct "who did what, when" if asked? I want to understand how complete and reliable our audit trail is in practice — are sensitive actions consistently logged, and would we be able to produce a credible record if a merchant or regulator asked us to investigate an incident?

   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

   Show the whole note

2. Noah Fischer plans the night

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

   Read the whole plan
   Weekly sign-in attempt volume and failure pattern

   Question

   Over the last 26 weeks, how many login attempts are we seeing platform-wide, what share fail, and are there signs of credential-stuffing-style behavior (many attempts concentrated on few IPs)?

   One row per

   ISO week (week_start), platform-wide across all tenants

   Columns

   • week_start (Monday of the ISO week)
   • total login attempts
   • successful logins
   • failed logins
   • failure rate (failed / total, %)
   • distinct emails/users attempted
   • distinct IP addresses used
   • average attempts per distinct IP (total attempts / distinct IPs)
   • max attempts from a single IP in the week

3. Ines 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 target AS (
     SELECT week_start
     FROM calendar_days
     WHERE calendar_date = DATE '2025-12-31'
   ),
   windowed AS (
     SELECT
       cd.week_start,
       la.email,
       la.ip_address,
       la.succeeded
     FROM login_attempts la
     JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS date)
     CROSS JOIN target t
     WHERE cd.week_start BETWEEN t.week_start - INTERVAL '175 days' AND t.week_start
   ),
   ip_counts AS (
     SELECT week_start, ip_address, COUNT(*) AS cnt
     FROM windowed
     GROUP BY week_start, ip_address
   ),
   ip_max AS (
     SELECT week_start, MAX(cnt) AS max_ip_attempts
     FROM ip_counts
     GROUP BY week_start
   ),
   weekly AS (
     SELECT
       week_start,
       COUNT(*) AS total_attempts,
       SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS successful_logins,
       SUM(CASE WHEN NOT succeeded THEN 1 ELSE 0 END) AS failed_logins,
       COUNT(DISTINCT email) AS distinct_identities,
       COUNT(DISTINCT ip_address) AS distinct_ips
     FROM windowed
     GROUP BY week_start
   )
   SELECT
     w.week_start,
     w.total_attempts,
     w.successful_logins,
     w.failed_logins,
     ROUND(100.0 * w.failed_logins / NULLIF(w.total_attempts, 0), 1) AS failure_rate_pct,
     w.distinct_identities AS distinct_emails_attempted,
     w.distinct_ips,
     ROUND(w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0), 1) AS avg_attempts_per_ip,
     m.max_ip_attempts AS max_attempts_single_ip
   FROM weekly w
   JOIN ip_max m ON m.week_start = w.week_start
   ORDER BY w.week_start

   “Query compiles and returns the expected columns. Submitting.”

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

   “Submitted. Summary of what the report delivers: - Grain: one row per ISO week (week_start), platform-wide across all tenants, for the 26 weeks ending with the week containing 2025-12-31. - Columns: total attempts, successful logins, failed logins, failure rate % (rounded to 1 decimal), distinct emails attempted, distinct IPs, average attempts per IP, and max attempts from any single IP that week — the last two are the credential-stuffing signal (a spike in max-per-IP or avg-per-IP alongside flat distinct-email counts would indicate concentrated automated attempts). - Note on deviation: "distinct emails/users attempted" is measured via distinct email on the attempt itself (not user_id), since failed/unauthenticated attempts often lack a resolved user_id — this is also the more relevant signal for spotting stuffing attacks against unknown accounts.”

4. The report comes back overnight

   As Noah Fischer saw it, from the small dataset the agents worked on

   week_start | total_attempts | successful_logins | failed_logins | failure_rate_pct | distinct_emails_attempted | distinct_ips | avg_attempts_per_ip | max_attempts_single_ip
   -----------+----------------+-------------------+---------------+------------------+---------------------------+--------------+---------------------+-----------------------
   2025-07-07 |           1368 |              1275 |            93 |              6.8 |                      1068 |          920 |                 1.5 |                     13
   2025-07-14 |           1373 |              1293 |            80 |              5.8 |                      1056 |          911 |                 1.5 |                     15
   2025-07-21 |           1331 |              1259 |            72 |              5.4 |                      1031 |          874 |                 1.5 |                     16
   2025-07-28 |           1295 |              1224 |            71 |              5.5 |                       985 |          830 |                 1.6 |                     15
   2025-08-04 |           1335 |              1233 |           102 |              7.6 |                      1015 |          868 |                 1.5 |                     17
   2025-08-11 |           1330 |              1262 |            68 |              5.1 |                      1026 |          876 |                 1.5 |                     14
   2025-08-18 |           1389 |              1283 |           106 |              7.6 |                      1027 |          880 |                 1.6 |                     16
   2025-08-25 |           1378 |              1292 |            86 |              6.2 |                      1061 |          901 |                 1.5 |                     19
   2025-09-01 |           1375 |              1299 |            76 |              5.5 |                      1085 |          927 |                 1.5 |                     21
   2025-09-08 |           1419 |              1328 |            91 |              6.4 |                      1081 |          919 |                 1.5 |                     16
   2025-09-15 |           1436 |              1355 |            81 |              5.6 |                      1092 |          932 |                 1.5 |                     13
   2025-09-22 |           1420 |              1328 |            92 |              6.5 |                      1125 |          970 |                 1.5 |                     18
   2025-09-29 |           1495 |              1388 |           107 |              7.2 |                      1141 |          974 |                 1.5 |                     14
   2025-10-06 |           1400 |              1312 |            88 |              6.3 |                      1085 |          932 |                 1.5 |                     17
   2025-10-13 |           1454 |              1369 |            85 |              5.8 |                      1105 |          943 |                 1.5 |                     12
   2025-10-20 |           1530 |              1444 |            86 |              5.6 |                      1181 |         1019 |                 1.5 |                     18
   2025-10-27 |           1572 |              1477 |            95 |              6.0 |                      1225 |         1063 |                 1.5 |                     19
   2025-11-03 |           1559 |              1449 |           110 |              7.1 |                      1200 |         1039 |                 1.5 |                     15
   2025-11-10 |           1579 |              1487 |            92 |              5.8 |                      1226 |         1067 |                 1.5 |                     17
   2025-11-17 |           1712 |              1612 |           100 |              5.8 |                      1343 |         1160 |                 1.5 |                     17
   2025-11-24 |           2417 |              2255 |           162 |              6.7 |                      1913 |         1745 |                 1.4 |                     24
   2025-12-01 |           1928 |              1823 |           105 |              5.4 |                      1531 |         1371 |                 1.4 |                     19
   2025-12-08 |           1629 |              1536 |            93 |              5.7 |                      1269 |         1106 |                 1.5 |                     15
   2025-12-15 |           1650 |              1543 |           107 |              6.5 |                      1270 |         1116 |                 1.5 |                     18
   2025-12-22 |           1493 |              1393 |           100 |              6.7 |                      1151 |          981 |                 1.5 |                     22
   2025-12-29 |            911 |               873 |            38 |              4.2 |                       805 |          702 |                 1.3 |                      9
   (26 rows)

5. What came of it

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

   Later queries that build on this report:

   • q104Login attempts by tenant during the week of 2025-11-24 spike

6. It becomes benchmark query q100

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

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

   WITH target AS (  SELECT week_start  FROM calendar_days  WHERE calendar_date = DATE '2025-12-31'),windowed AS (  SELECT    cd.week_start,    la.email,    la.ip_address,    la.succeeded    cd.week_start AS week_start,    la.email AS email,    la.ip_address AS ip_address,    la.succeeded AS succeeded  FROM login_attempts la  JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS date)  CROSS JOIN target t  WHERE cd.week_start BETWEEN t.week_start - INTERVAL '175 days' AND t.week_start),ip_counts AS (  SELECT week_start, ip_address, COUNT(*) AS cnt  FROM windowed  GROUP BY week_start, ip_address),ip_max AS (  SELECT week_start, MAX(cnt) AS max_ip_attempts  FROM ip_counts  GROUP BY week_start),weekly AS (  SELECT    week_start,    COUNT(*) AS total_attempts,    SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS successful_logins,    SUM(CASE WHEN NOT succeeded THEN 1 ELSE 0 END) AS failed_logins,    COUNT(DISTINCT email) AS distinct_identities,    COUNT(DISTINCT ip_address) AS distinct_ips  FROM windowed  GROUP BY week_start)SELECT  w.week_start,  w.total_attempts,  w.successful_logins,  w.failed_logins,  ROUND(100.0 * w.failed_logins / NULLIF(w.total_attempts, 0), 1) AS failure_rate_pct,  w.week_start AS week_start,  w.total_attempts AS total_attempts,  w.successful_logins AS successful_logins,  w.failed_logins AS failed_logins,  100.0 * w.failed_logins / NULLIF(w.total_attempts, 0) AS failure_rate_pct,  w.distinct_identities AS distinct_emails_attempted,  w.distinct_ips,  ROUND(w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0), 1) AS avg_attempts_per_ip,  w.distinct_ips AS distinct_ips,  w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0) AS avg_attempts_per_ip,  m.max_ip_attempts AS max_attempts_single_ipFROM weekly wJOIN ip_max m ON m.week_start = w.week_startORDER BY w.week_startORDER BY w.week_start 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 target AS (
     SELECT week_start
     FROM calendar_days
     WHERE calendar_date = DATE '2025-12-31'
   ),
   windowed AS (
     SELECT
       cd.week_start AS week_start,
       la.email AS email,
       la.ip_address AS ip_address,
       la.succeeded AS succeeded
     FROM login_attempts la
     JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS date)
     CROSS JOIN target t
     WHERE cd.week_start BETWEEN t.week_start - INTERVAL '175 days' AND t.week_start
   ),
   ip_counts AS (
     SELECT week_start, ip_address, COUNT(*) AS cnt
     FROM windowed
     GROUP BY week_start, ip_address
   ),
   ip_max AS (
     SELECT week_start, MAX(cnt) AS max_ip_attempts
     FROM ip_counts
     GROUP BY week_start
   ),
   weekly AS (
     SELECT
       week_start,
       COUNT(*) AS total_attempts,
       SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS successful_logins,
       SUM(CASE WHEN NOT succeeded THEN 1 ELSE 0 END) AS failed_logins,
       COUNT(DISTINCT email) AS distinct_identities,
       COUNT(DISTINCT ip_address) AS distinct_ips
     FROM windowed
     GROUP BY week_start
   )
   SELECT
     w.week_start AS week_start,
     w.total_attempts AS total_attempts,
     w.successful_logins AS successful_logins,
     w.failed_logins AS failed_logins,
     100.0 * w.failed_logins / NULLIF(w.total_attempts, 0) AS failure_rate_pct,
     w.distinct_identities AS distinct_emails_attempted,
     w.distinct_ips AS distinct_ips,
     w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0) AS avg_attempts_per_ip,
     m.max_ip_attempts AS max_attempts_single_ip
   FROM weekly w
   JOIN ip_max m ON m.week_start = w.week_start
   ORDER BY w.week_start NULLS LAST

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

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

   Ines's whole session, 7 turns

   • The request, as Ines received it

     Report request from Noah Fischer (analyst for the Chief Information Security Officer):
     
     Title: Weekly sign-in attempt volume and failure pattern
     Question: Over the last 26 weeks, how many login attempts are we seeing platform-wide, what share fail, and are there signs of credential-stuffing-style behavior (many attempts concentrated on few IPs)?
     Grain: one row per ISO week (week_start), platform-wide across all tenants
     Columns:
     - week_start (Monday of the ISO week)
     - total login attempts
     - successful logins
     - failed logins
     - failure rate (failed / total, %)
     - distinct emails/users attempted
     - distinct IP addresses used
     - average attempts per distinct IP (total attempts / distinct IPs)
     - max attempts from a single IP in the week
     Details: Source: login_attempts (occurred_at, succeeded, email, user_id, ip_address). Bucket occurred_at::date into ISO weeks via calendar_days.week_start. Window: the 26 ISO weeks ending with the week containing 2025-12-31 (i.e. roughly 2025-07 through 2025-12, last available data). Aggregate across all tenants (no currency conversion needed, no money involved). failure rate = failed_logins / total_attempts, rounded to 1 decimal. 'max attempts from a single IP in the week' = the highest per-IP attempt count within that week (compute per-IP counts, then take the max per week). Order by week_start ascending, which is already unique so no further tie-break needed.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     WITH target AS (
       SELECT week_start
       FROM calendar_days
       WHERE calendar_date = DATE '2025-12-31'
     ),
     windowed AS (
       SELECT
         cd.week_start,
         la.email,
         la.ip_address,
         la.succeeded
       FROM login_attempts la
       JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS date)
       CROSS JOIN target t
       WHERE cd.week_start BETWEEN t.week_start - INTERVAL '175 days' AND t.week_start
     ),
     ip_counts AS (
       SELECT week_start, ip_address, COUNT(*) AS cnt
       FROM windowed
       GROUP BY week_start, ip_address
     ),
     ip_max AS (
       SELECT week_start, MAX(cnt) AS max_ip_attempts
       FROM ip_counts
       GROUP BY week_start
     ),
     weekly AS (
       SELECT
         week_start,
         COUNT(*) AS total_attempts,
         SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS successful_logins,
         SUM(CASE WHEN NOT succeeded THEN 1 ELSE 0 END) AS failed_logins,
         COUNT(DISTINCT email) AS distinct_identities,
         COUNT(DISTINCT ip_address) AS distinct_ips
       FROM windowed
       GROUP BY week_start
     )
     SELECT
       w.week_start,
       w.total_attempts,
       w.successful_logins,
       w.failed_logins,
       ROUND(100.0 * w.failed_logins / NULLIF(w.total_attempts, 0), 1) AS failure_rate_pct,
       w.distinct_identities AS distinct_emails_attempted,
       w.distinct_ips,
       ROUND(w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0), 1) AS avg_attempts_per_ip,
       m.max_ip_attempts AS max_attempts_single_ip
     FROM weekly w
     JOIN ip_max m ON m.week_start = w.week_start
     ORDER BY w.week_start

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: week_start, total_attempts, successful_logins, failed_logins, failure_rate_pct, distinct_emails_attempted, distinct_ips, avg_attempts_per_ip, max_attempts_single_ip

   • InesQuery compiles and returns the expected columns. Submitting.
   • Ines calls submit

     WITH target AS (
       SELECT week_start
       FROM calendar_days
       WHERE calendar_date = DATE '2025-12-31'
     ),
     windowed AS (
       SELECT
         cd.week_start,
         la.email,
         la.ip_address,
         la.succeeded
       FROM login_attempts la
       JOIN calendar_days cd ON cd.calendar_date = CAST(la.occurred_at AS date)
       CROSS JOIN target t
       WHERE cd.week_start BETWEEN t.week_start - INTERVAL '175 days' AND t.week_start
     ),
     ip_counts AS (
       SELECT week_start, ip_address, COUNT(*) AS cnt
       FROM windowed
       GROUP BY week_start, ip_address
     ),
     ip_max AS (
       SELECT week_start, MAX(cnt) AS max_ip_attempts
       FROM ip_counts
       GROUP BY week_start
     ),
     weekly AS (
       SELECT
         week_start,
         COUNT(*) AS total_attempts,
         SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS successful_logins,
         SUM(CASE WHEN NOT succeeded THEN 1 ELSE 0 END) AS failed_logins,
         COUNT(DISTINCT email) AS distinct_identities,
         COUNT(DISTINCT ip_address) AS distinct_ips
       FROM windowed
       GROUP BY week_start
     )
     SELECT
       w.week_start,
       w.total_attempts,
       w.successful_logins,
       w.failed_logins,
       ROUND(100.0 * w.failed_logins / NULLIF(w.total_attempts, 0), 1) AS failure_rate_pct,
       w.distinct_identities AS distinct_emails_attempted,
       w.distinct_ips,
       ROUND(w.total_attempts * 1.0 / NULLIF(w.distinct_ips, 0), 1) AS avg_attempts_per_ip,
       m.max_ip_attempts AS max_attempts_single_ip
     FROM weekly w
     JOIN ip_max m ON m.week_start = w.week_start
     ORDER BY w.week_start

   • The warehouse answers

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

   • InesSubmitted. Summary of what the report delivers: - **Grain:** one row per ISO week (`week_start`), platform-wide across all tenants, for the 26 weeks ending with the week containing 2025-12-31. - **Columns:** total attempts, successful logins, failed logins, failure rate % (rounded to 1 decimal), distinct emails attempted, distinct IPs, average attempts per IP, and max attempts from any single IP that week — the last two are the credential-stuffing signal (a spike in max-per-IP or avg-per-IP alongside flat distinct-email counts would indicate concentrated automated attempts). - **Note on deviation:** "distinct emails/users attempted" is measured via distinct `email` on the attempt itself (not `user_id`), since failed/unauthenticated attempts often lack a resolved `user_id` — this is also the more relevant signal for spotting stuffing attacks against unknown accounts.

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

   Ines's transcript (266 KB) · Noah Fischer's transcript (875 KB) · Henrik Aalto's transcript (262 KB) · the night's plan (5 KB) · the night's reports (26 KB) · the week's memo (3 KB)