Drill 6 of 8 in Fintech questions
0

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…