SQL Fraud Detection: Two Cities | Dataford Interview Questions

Business Context

Capital One's fraud analytics team wants to flag accounts showing rapid geographic movement. A potential alert occurs when the same account has transactions in different cities within one hour.

Task

Write a PostgreSQL query that returns every qualifying pair of transactions for valid Capital One accounts.

Requirements

Match transactions belonging to the same account, while avoiding pairing a transaction with itself or returning the same pair twice.
Keep only pairs occurring within one hour, including exactly one hour, where the cities are different.
Exclude transactions with a missing city and transactions belonging to an account absent from accounts.
Return the account ID, both transaction IDs, both timestamps, both cities, and the elapsed minutes. Sort by account ID and the first transaction timestamp.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique Capital One account identifier
account_name	VARCHAR(100)	Account holder label

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
transaction_ts	TIMESTAMP	Transaction timestamp
city	VARCHAR(100)	City where the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is SQL Fraud Detection: Two Cities. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem