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_date is a valid date
  • 1 ≤ amount ≤ 10^6
  • Each (customer_id, transaction_date) pair is unique

Visualization

Tap to expand
Customers with Max Consecutive Transaction Days INPUT Transactions Table customer_id transaction_date 1 2023-01-01 1 2023-01-02 1 2023-01-03 2 2023-01-05 2 2023-01-06 2 2023-01-07 3 2023-01-10 Unique: (customer_id, transaction_date) Groups by Customer: C1: 3 txn C2: 3 txn C3: 1 txn ALGORITHM STEPS 1 Assign Row Numbers ROW_NUMBER() ordered by date per customer partition 2 Calculate Group Key date - row_num = group_id Consecutive dates share key C1: 01-01 - 1 = 12-31 C1: 01-02 - 2 = 12-31 C1: 01-03 - 3 = 12-31 Same key = consecutive! 3 Count Consecutive GROUP BY customer, group_id COUNT(*) = streak length 4 Find Max and Filter Get global MAX streak Return customers with max C1: 3 | C2: 3 | C3: 1 MAX = 3 FINAL RESULT Customers with maximum consecutive streak (3 days): customer_id 1 2 Streak Visualization: C1: 01 02 03 C2: 05 06 07 C3: 10 (only 1) OUTPUT: [1, 2] Key Insight: The "date minus row_number" technique creates identical values for consecutive dates. When dates are consecutive (Jan 1, 2, 3) and row numbers are sequential (1, 2, 3), subtracting them yields the same result, grouping consecutive transactions together. TutorialsPoint - Customers with Maximum Number of Transactions on Consecutive Days | Optimal Solution

Related Problems

Asked in
Amazon 12 Google 8 Meta 6
25.9K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen