Table: Transactions
+------------------+------+ | Column Name | Type | +------------------+------+ | transaction_id | int | | customer_id | int | | transaction_date | date | | amount | int | +------------------+------+ transaction_id is the primary key 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 an SQL query to find the customers who have made consecutive transactions with increasing amount for at least three consecutive days. Include the customer_id, start date of the consecutive transactions period and the end date of the consecutive transactions period. There can be multiple consecutive transactions by a customer.
Return the result table ordered by customer_id, consecutive_start, consecutive_end in ascending order.
The query 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 | | 10 | 105 | 2023-05-04 | 300 | | 11 | 105 | 2023-05-12 | 250 | | 12 | 105 | 2023-05-13 | 260 | | 13 | 105 | 2023-05-14 | 270 | +----------------+-------------+------------------+--------+ Output: +-------------+-------------------+-----------------+ | customer_id | consecutive_start | consecutive_end | +-------------+-------------------+-----------------+ | 101 | 2023-05-01 | 2023-05-03 | | 105 | 2023-05-01 | 2023-05-04 | | 105 | 2023-05-12 | 2023-05-14 | +-------------+-------------------+-----------------+ Explanation: - customer_id 101 has made consecutive transactions with increasing amounts from May 1st, 2023, to May 3rd, 2023 - customer_id 102 does not have any consecutive transactions for at least 3 days. - customer_id 105 has two sets of consecutive transactions: from May 1st, 2023, to May 4th, 2023, and from May 12th, 2023, to May 14th, 2023. customer_id is sorted in ascending order.
Problem Overview: Given a transactions table, identify customers who made transactions on consecutive days where the transaction amount strictly increases each day. Only sequences with at least three consecutive transactions qualify, and the result should report the customer along with the start and end dates of the sequence.
Approach 1: Self Join on Consecutive Days (O(n²) time, O(1) extra space)
A direct approach checks consecutive-day relationships using self joins. Join the transactions table with itself to find records where the same customer_id has transactions on date + 1, date + 2, etc., while ensuring amount increases between rows. This method works but becomes expensive because each row may compare with many others. The query grows complex as you try to detect longer sequences. Use this mainly for understanding the rule structure before optimizing.
Approach 2: Window Functions + Consecutive Grouping (O(n log n) time, O(n) space)
The efficient SQL solution relies on SQL window functions. First, order each customer's transactions by transaction_date. Use LAG() to compare the current amount with the previous transaction and verify the strictly increasing condition. At the same time, compute consecutive-day continuity using date differences.
To group consecutive days, generate a ROW_NUMBER() for each customer's ordered transactions. Subtracting this value from the transaction date creates a stable grouping key for uninterrupted date sequences. Within each group, filter rows where amount > LAG(amount) so the sequence remains strictly increasing. Finally, aggregate each group and keep only those where the count is at least three transactions.
This pattern—ROW_NUMBER() for streak detection combined with LAG() comparisons—is common in analytical queries and appears frequently in database interview problems. It avoids repeated joins and processes rows in a single ordered pass.
Window functions are especially useful for sequence detection problems such as transaction streaks, login streaks, or increasing metrics over time. If you're comfortable with window functions, this query becomes compact and efficient.
Recommended for interviews: The window function approach is the expected solution. It shows you understand streak detection patterns in SQL and how to combine LAG(), ROW_NUMBER(), and grouping logic. The self-join method demonstrates the basic idea but does not scale well for large datasets.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join on Consecutive Days | O(n²) | O(1) | Small datasets or when first reasoning about consecutive-day relationships |
| Window Functions with ROW_NUMBER and LAG | O(n log n) | O(n) | Production SQL queries and interview solutions involving streak detection |
2701. Consecutive Transactions with Increasing Amounts - LeetCode SQL Solution • Code with Carter • 768 views views
Practice Consecutive Transactions with Increasing Amounts with our built-in code editor and test cases.
Practice on FleetCode