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…