Interview Question Bank · Self-join on a time window
6. Same card, two cities, one hour
HardFintechQuestion #105
Concept: Self-join on a time windowshow
To compare every event with every later event of the same entity, self-join the table: same key, the second row strictly later and inside the window (b.t > a.t AND b.t <= a.t + INTERVAL '...'). LAG and LEAD only look one row away, so they miss pairs with another event in between. Then add the condition that makes a pair interesting.
Example (different data):
SELECT a.badge_id, a.building AS first_building, b.building AS second_building FROM badge_swipes a JOIN badge_swipes b ON b.badge_id = a.badge_id AND b.swiped_at > a.swiped_at AND b.swiped_at <= a.swiped_at + INTERVAL '5 minutes' AND b.building <> a.building;
Your task
Northpine Payments' fraud team treats a card used in two different cities within an hour as likely cloned, whether or not the second attempt was approved.
Find every pair of authorizations in fin_card_auths on the same card where the second one was in a different city, after the first and at most 60 minutes later (exactly 60 counts). Approved and declined attempts both count. Return card_id, first_city, first_at, second_city and second_at. Sort by card_id, first_at, second_at.
Starting Postgres in your browser…
Checking your access…