Customers with Maximum Number of Transactions on Consecutive Days - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min read

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

Leetcode HARD 2752 - Customers with Maximum Transactions on Consecutive Days : Everyday Data Science • Everyday Data Science • 866 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Customers with Maximum Number of Transactions on Consecutive Days easy or hard?

Customers with Maximum Number of Transactions on Consecutive Days is classified as a Hard database problem. The challenge comes from recognizing the gaps-and-islands pattern and correctly using SQL window functions to detect consecutive date sequences.

Customers with Maximum Number of Transactions on Consecutive Days Python/Java solution

On SQL-focused platforms like LeetCode, the expected answer is written in SQL (MySQL). A Python or Java equivalent would typically load transactions, group them by customer, sort dates, and scan linearly to track the longest consecutive-day streak. That approach runs in O(n log n) time due to sorting.

How to solve Customers with Maximum Number of Transactions on Consecutive Days in O(n)?

Pure O(n) performance is difficult in SQL because ordering by transaction date is required to detect consecutive sequences. Most database engines perform a sort step, resulting in O(n log n) time. The practical solution uses ROW_NUMBER() with date subtraction to group consecutive transactions and then aggregates streak lengths.

What is the best approach for Customers with Maximum Number of Transactions on Consecutive Days?

The best approach uses the SQL gaps-and-islands pattern with window functions. Assign a ROW_NUMBER() per customer ordered by transaction date, subtract it from the date to create grouping keys, and group consecutive rows together. Then compute streak lengths and return the customer with the maximum streak. This runs in about O(n log n) time due to sorting.

Is Customers with Maximum Number of Transactions on Consecutive Days asked at Google/Amazon/Meta?

Problems involving consecutive sequences and gaps-and-islands patterns appear frequently in SQL interviews at companies like Amazon, Meta, and Google. Variants ask for longest login streaks, consecutive purchases, or continuous activity windows. This problem tests strong knowledge of window functions and grouping logic.

What data structure is used in Customers with Maximum Number of Transactions on Consecutive Days?

The solution relies on relational database operations rather than traditional data structures. Window functions such as ROW_NUMBER(), ordered partitions, and grouped aggregates act as the core mechanisms for tracking sequences of dates per customer.

What is the time complexity of Customers with Maximum Number of Transactions on Consecutive Days?

The optimal SQL solution runs in O(n log n) time because the database must sort transactions per customer when applying window functions such as ROW_NUMBER(). Aggregations and grouping then process the sorted rows in linear time. Space complexity is O(n) for intermediate window results.

Ready to solve this problem?

Practice Customers with Maximum Number of Transactions on Consecutive Days with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance39.6%

Approaches1

Reading time4 min

Practice this problem

Open in Editor