Customers with Maximum Number of Transactions on Consecutive Days - Problem
Given a Transactions table, find all customers who made the maximum number of transactions on consecutive days.
The table contains transaction information with unique (customer_id, transaction_date) combinations. You need to:
- Identify consecutive transaction days for each customer
- Count the maximum consecutive streak across all customers
- Return all customers who achieved this maximum streak
Return the result ordered by customer_id in ascending order.
Table Schema
Transactions
| Column Name | Type | Description |
|---|---|---|
transaction_id
PK
|
int | Unique identifier for each transaction |
customer_id
|
int | Customer identifier |
transaction_date
|
date | Date of the transaction |
amount
|
int | Transaction amount |
Primary Key: transaction_id
Note: Each row has unique (customer_id, transaction_date) combination
Input & Output
Example 1 — Multiple Customers with Different Streaks
Input Table:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 1 | 1 | 2023-01-01 | 150 |
| 2 | 1 | 2023-01-02 | 300 |
| 3 | 1 | 2023-01-03 | 200 |
| 4 | 2 | 2023-01-01 | 400 |
| 5 | 2 | 2023-01-02 | 250 |
| 6 | 3 | 2023-01-01 | 100 |
Output:
| customer_id |
|---|
| 1 |
💡 Note:
Customer 1 has transactions on 3 consecutive days (2023-01-01, 2023-01-02, 2023-01-03). Customer 2 has transactions on 2 consecutive days. Customer 3 has only 1 transaction day. The maximum consecutive streak is 3, achieved only by Customer 1.
Example 2 — Multiple Customers Tie for Maximum
Input Table:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 1 | 1 | 2023-01-01 | 100 |
| 2 | 1 | 2023-01-02 | 200 |
| 3 | 2 | 2023-01-03 | 300 |
| 4 | 2 | 2023-01-04 | 150 |
| 5 | 3 | 2023-01-01 | 250 |
Output:
| customer_id |
|---|
| 1 |
| 2 |
💡 Note:
Both Customer 1 and Customer 2 have 2 consecutive transaction days, which is the maximum streak. Customer 3 has only 1 transaction day. Both customers with the maximum streak of 2 are returned.
Constraints
-
1 ≤ transaction_id ≤ 10^5 -
1 ≤ customer_id ≤ 10^4 -
transaction_dateis a valid date -
1 ≤ amount ≤ 10^6 -
Each
(customer_id, transaction_date)pair is unique
Visualization
Tap to expand
Understanding the Visualization
1
Input
Transaction data with dates
2
Group Consecutive
Use date arithmetic to group consecutive days
3
Find Maximum
Count streaks and return customers with maximum
Key Takeaway
🎯 Key Insight: Use date arithmetic with ROW_NUMBER() to detect consecutive sequences efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code