Repeated payments within 10 minutes

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

card_transactions records payment swipes with columns txn_id, merchant_id, card_number, amount, and txn_time. A repeated payment is a transaction that has the same merchant_id, card_number, and amount as an earlier transaction, occurring within 10 minutes (<= 10 minutes) of the immediately preceding transaction for that card, merchant, and amount. For each merchant that experienced repeated payments, count the number of repeated payment transactions. Return merchant_id and repeated_payment_count, ordered by merchant_id ascending.

Requirements

• Output columns: merchant_id, repeated_payment_count.
• Order by merchant_id ascending.

Constraints

• amount > 0.
• txn_time is a timestamp.
• Only count merchants that have at least one repeated payment.

Examples

Input: card_transactions txn_id,merchant_id,card_number,amount,txn_time T1,M1,CARD-111,500,2024-09-01 10:00:00 T2,M1,CARD-111,500,2024-09-01 10:05:00 T3,M1,CARD-111,500,2024-09-01 10:25:00 T4,M2,CARD-222,1200,2024-09-01 11:00:00 T5,M2,CARD-222,1200,2024-09-01 11:08:00 T6,M2,CARD-222,1200,2024-09-01 11:15:00 Output: merchant_id | repeated_payment_count M1 | 1 M2 | 2 Why this passes: M1's 10:00 and 10:05 payments are within 10 minutes (1 repeat) while 10:25 is not; M2's 11:00, 11:08 and 11:15 chain gives 2 repeats.

Input: card_transactions txn_id | merchant_id | card_number | amount | txn_time 1 | M1 | C1 | 50 | 2024-09-01 12:00:00 2 | M1 | C1 | 50 | 2024-09-01 12:04:00 Output: merchant_id | repeated_payment_count M1 | 1 Why this passes: Transaction 2 occurred 4 minutes after transaction 1, counting as 1 repeat.

Topics: lakebench, sql, lag, time-delta.

More SQL interview questions · All interview problems · Learn data engineering