Consecutive Transactions with Increasing Amounts - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min read

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

2701. Consecutive Transactions with Increasing Amounts - LeetCode SQL Solution • Code with Carter • 777 views views

Frequently Asked Questions

Is Consecutive Transactions with Increasing Amounts easy or hard?

Consecutive Transactions with Increasing Amounts is classified as Hard because it combines multiple SQL techniques: window functions, consecutive date detection, and conditional filtering of increasing values. Many candidates struggle with the row_number grouping trick used to identify streaks.

Consecutive Transactions with Increasing Amounts Python/Java solution

This problem is typically solved using SQL rather than Python or Java because it operates directly on relational tables. In application code, a similar solution would sort transactions by customer and date, then scan the list while tracking increasing amounts and consecutive-day streaks.

How to solve Consecutive Transactions with Increasing Amounts in O(n)?

Pure O(n) performance is difficult in SQL because ordering by date is required to evaluate consecutive transactions. The closest practical solution uses window functions after sorting. Using LAG() to compare amounts and ROW_NUMBER() to detect consecutive dates efficiently processes the ordered rows in a single pass.

What is the best approach for Consecutive Transactions with Increasing Amounts?

The best approach uses SQL window functions such as ROW_NUMBER() and LAG(). First order transactions by customer and date, then compare each amount with the previous row using LAG(). A row_number-based grouping trick identifies consecutive date streaks, and groups with at least three rows form valid results. This runs in roughly O(n log n) due to sorting.

Is Consecutive Transactions with Increasing Amounts asked at Google/Amazon/Meta?

Problems involving transaction streaks and time‑series comparisons frequently appear in database interviews at companies like Amazon, Google, and Meta. Variants often require detecting consecutive events, increasing metrics, or activity streaks using SQL window functions.

What data structure is used in Consecutive Transactions with Increasing Amounts?

The problem relies on relational database operations rather than traditional data structures. SQL window functions such as LAG() and ROW_NUMBER() act like sequential comparisons over ordered rows, enabling detection of increasing sequences and consecutive date groups.

What is the time complexity of Consecutive Transactions with Increasing Amounts?

The optimal SQL solution runs in O(n log n) time because transactions must be sorted by customer and date before applying window functions. Window operations such as LAG() and ROW_NUMBER() then process rows linearly. Space complexity is O(n) for intermediate result sets during grouping.

Ready to solve this problem?

Practice Consecutive Transactions with Increasing Amounts with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance33.0%

Approaches1

Reading time5 min

Practice this problem

Open in Editor