Drill 10 of 10 in Banking questions
0

Interview Question Bank · Rolling COUNT over a time RANGE · first match per group

10. Three declines within 24 hours

HardBankingQuestion #273

Concept: Rolling COUNT over a time RANGE · first match per groupshow

To catch 'N events within T', count events in a time-based window that ends at each event: COUNT(*) OVER (PARTITION BY id ORDER BY ts RANGE BETWEEN INTERVAL 'T' PRECEDING AND CURRENT ROW). Calendar buckets like ts::date split bursts that cross midnight, and a plain GROUP BY ignores the time limit altogether. Filter the events you care about before the window, then take MIN(ts) of the qualifying rows for the first moment each id tripped the rule.

Example (different data):

WITH f AS (
  SELECT user_id, failed_at,
    COUNT(*) OVER (PARTITION BY user_id ORDER BY failed_at
      RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW) AS n
  FROM login_failures
)
SELECT user_id, MIN(failed_at) AS locked_at
FROM f WHERE n >= 5
GROUP BY user_id;

Your task

Ashgrove Federal's fraud team flags an account when 3 or more card authorizations are declined within 24 hours (the first and third at most 24 hours apart), a common sign that someone is testing a stolen card.

Return account_id and flagged_at: the earliest declined attempt at which the account had 3 or more declines in the 24 hours up to and including that moment. One row per flagged account. Sort by account_id.

Starting Postgres in your browser…

Checking your access…