Table: Transactions
+------------------+------+ | Column Name | Type | +------------------+------+ | transaction_id | int | | customer_id | int | | transaction_date | date | | amount | int | +------------------+------+ transaction_id is the column with unique values of this table. Each row contains information about transactions that includes unique (customer_id, transaction_date) along with the corresponding customer_id and amount.
Write a solution to find all customer_id who made the maximum number of transactions on consecutive days.
Return all customer_id with the maximum number of consecutive transactions. Order the result table by customer_id in ascending order.
The result format is in the following example.
Example 1:
Input: Transactions table: +----------------+-------------+------------------+--------+ | transaction_id | customer_id | transaction_date | amount | +----------------+-------------+------------------+--------+ | 1 | 101 | 2023-05-01 | 100 | | 2 | 101 | 2023-05-02 | 150 | | 3 | 101 | 2023-05-03 | 200 | | 4 | 102 | 2023-05-01 | 50 | | 5 | 102 | 2023-05-03 | 100 | | 6 | 102 | 2023-05-04 | 200 | | 7 | 105 | 2023-05-01 | 100 | | 8 | 105 | 2023-05-02 | 150 | | 9 | 105 | 2023-05-03 | 200 | +----------------+-------------+------------------+--------+ Output: +-------------+ | customer_id | +-------------+ | 101 | | 105 | +-------------+ Explanation: - customer_id 101 has a total of 3 transactions, and all of them are consecutive. - customer_id 102 has a total of 3 transactions, but only 2 of them are consecutive. - customer_id 105 has a total of 3 transactions, and all of them are consecutive. In total, the highest number of consecutive transactions is 3, achieved by customer_id 101 and 105. The customer_id are sorted in ascending order.
Problem Overview: Given a transactions table, you need to identify which customer has the highest number of transactions occurring on consecutive calendar days. The task is to compute each customer's longest streak of continuous daily activity and return the customer(s) with the maximum streak.
Approach 1: Gaps and Islands with Window Functions (O(n log n) time, O(n) space)
This problem fits the classic gaps and islands pattern in SQL. For each customer, sort transactions by date and assign a ROW_NUMBER() window value. Subtract the row index from the transaction date to create a grouping key; consecutive dates collapse into the same "island" because the difference stays constant. After grouping by this derived key and customer, compute the length of each consecutive streak using COUNT(*).
Once all streak lengths are calculated, compute the maximum streak per customer and then compare across customers. The final query returns the customer(s) whose longest streak equals the global maximum. The key operations are window ranking, date arithmetic, and aggregation. This approach relies heavily on window functions and grouping patterns commonly used in database interview problems.
Sorting transactions per customer is the dominant cost, giving roughly O(n log n) time depending on the database engine's execution plan. Intermediate window function results require O(n) space.
Recommended for interviews: The window-function gaps-and-islands pattern is the expected solution. It demonstrates strong SQL fundamentals: partitioning, ordering, and transforming row sequences into groups. A brute-force approach using self-joins or iterative date comparisons shows the idea of checking consecutive days but scales poorly. Interviewers usually look for the window-function grouping trick because it solves consecutive sequence problems cleanly and efficiently.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join Consecutive Date Checks | O(n²) | O(1) | Conceptual brute-force method to compare transactions day by day; useful for understanding consecutive relationships but inefficient for large datasets |
| Gaps and Islands with ROW_NUMBER() | O(n log n) | O(n) | Best general SQL approach for detecting consecutive sequences per customer using window functions and grouping |
| Window Aggregation + Date Difference Grouping | O(n log n) | O(n) | Alternative formulation of the gaps-and-islands technique when using dense ranking or computed date offsets |
Leetcode HARD 2752 - Customers with Maximum Transactions on Consecutive Days : Everyday Data Science • Everyday Data Science • 804 views views
Watch 2 more video solutions →Practice Customers with Maximum Number of Transactions on Consecutive Days with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor