Repeated Payments
ASKED IN INTERVIEWStripe
Problem Statement
Find the total count of repeated payments per merchant. A payment is a repeat if it shares the same credit_card_id, merchant_id, and amount as a transaction occurring within the previous 10 minutes.
Rules:
- Output columns: merchant_id, repeated_payments_count.
- Window: <= 10 minutes.
- Sort by repeated_payments_count DESC.
Tests your understanding of
Window Functions, LEAD, LAG and Time Intervals
Input Tables
transactions
| transaction_id(INTEGER) | merchant_id(INTEGER) | credit_card_id(INTEGER) | amount(DECIMAL) | transaction_timestamp(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 101 | 1 | 50 | 2024-01-01 10:00:00 |
| 2 | 101 | 1 | 50 | 2024-01-01 10:05:00 |
| 3 | 101 | 1 | 50 | 2024-01-01 10:20:00 |
| 4 | 102 | 2 | 20 | 2024-01-01 10:00:00 |
| 5 | 102 | 2 | 20 | 2024-01-01 10:09:00 |
merchants
| merchant_id(INTEGER) | merchant_name(VARCHAR) |
|---|---|
| 101 | CloudServices |
| 102 | CoffeeShop |
card_details
| credit_card_id(INTEGER) | card_brand(VARCHAR) |
|---|---|
| 1 | Visa |
Expected Output
| merchant_id(INTEGER) | repeated_payments_count(INTEGER) |
|---|---|
| 101 | 1 |
| 102 | 1 |
Tags
HardASKED IN INTERVIEWWindow FunctionsLEADLAGTime Intervals
30-40 min
35%