Consecutive Transactions with Increasing Amounts

Consecutive Transactions with Increasing Amounts - Problem

Database Hard

Imagine you're a financial analyst tracking customer spending patterns to identify potential high-value customers. You need to find customers who demonstrate consistent growth in their spending over consecutive days.

Problem: Given a Transactions table with customer transactions, find customers who have made transactions with increasing amounts for at least 3 consecutive days.

Table Schema:

Key Rules:

Each (customer_id, transaction_date) combination is unique
Consecutive means consecutive calendar days
Amounts must be strictly increasing
Minimum 3 consecutive days required

Output: Return customer_id, start_date, and end_date of each consecutive period, ordered by customer_id, start_date, end_date.

Input & Output

example_1.sql — Basic Consecutive Increasing

$ 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-02 | 25 |

› Output: | customer_id | consecutive_start | consecutive_end | |-------------|-------------------|------------------| | 101 | 2023-05-01 | 2023-05-03 |

💡 Note: Customer 101 has consecutive transactions on 05-01 ($100), 05-02 ($150), 05-03 ($200) with increasing amounts. Customer 102 doesn't qualify as amounts decrease from $50 to $25.

example_2.sql — Multiple Consecutive Periods

$ Input: Transactions table: | transaction_id | customer_id | transaction_date | amount | |---------------|-------------|------------------|--------| | 1 | 201 | 2023-06-01 | 100 | | 2 | 201 | 2023-06-02 | 120 | | 3 | 201 | 2023-06-03 | 140 | | 4 | 201 | 2023-06-05 | 200 | | 5 | 201 | 2023-06-06 | 250 | | 6 | 201 | 2023-06-07 | 300 |

› Output: | customer_id | consecutive_start | consecutive_end | |-------------|-------------------|------------------| | 201 | 2023-06-01 | 2023-06-03 | | 201 | 2023-06-05 | 2023-06-07 |

💡 Note: Customer 201 has two separate consecutive increasing periods: 06-01 to 06-03 (3 days) and 06-05 to 06-07 (3 days), with a gap on 06-04.

example_3.sql — Edge Case: Exactly 3 Days

$ Input: Transactions table: | transaction_id | customer_id | transaction_date | amount | |---------------|-------------|------------------|--------| | 1 | 301 | 2023-07-01 | 80 | | 2 | 301 | 2023-07-02 | 90 | | 3 | 301 | 2023-07-03 | 100 | | 4 | 301 | 2023-07-04 | 95 | | 5 | 302 | 2023-07-01 | 100 | | 6 | 302 | 2023-07-02 | 110 |

› Output: | customer_id | consecutive_start | consecutive_end | |-------------|-------------------|------------------| | 301 | 2023-07-01 | 2023-07-03 |

💡 Note: Customer 301 qualifies with exactly 3 consecutive increasing days, then amount drops on 07-04. Customer 302 only has 2 consecutive days, which doesn't meet the minimum requirement of 3.

Visualization

Tap to expand

Understanding the Visualization

Sort by Timeline

Arrange all transactions chronologically for each customer

Compare with Yesterday

For each transaction, check if it's consecutive and amount increased from previous day

Track Streak Boundaries

Mark where increasing streaks start and end

Group Consecutive Days

Assign unique IDs to each streak of consecutive increasing days

Filter Valid Patterns

Keep only streaks with 3 or more consecutive increasing days

Key Takeaway

🎯 Key Insight: Use LAG() window function to compare consecutive days, then employ running sum technique to group consecutive increasing streaks efficiently in a single SQL pass.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single sort by customer and date, then linear pass with window functions

⚡ Linearithmic

Space Complexity

O(n)

Space for final result set and window function intermediate calculations

⚡ Linearithmic Space

Constraints

1 ≤ Number of transactions ≤ 10⁴
1 ≤ customer_id ≤ 1000
1 ≤ amount ≤ 10⁶
Each (customer_id, transaction_date) pair is unique
transaction_date is a valid date in format YYYY-MM-DD
Amounts must be strictly increasing (not equal)

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 18

The optimal solution uses window functions with LAG() to compare each transaction with the previous day's transaction in a single pass. Key techniques include using conditional logic to identify streak starts, running sum to group consecutive increasing sequences, and HAVING clause to filter for sequences with 3+ days.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions with ROW_NUMBER	O(n log n)	O(n)	Use window functions to identify consecutive sequences, then filter for increasing amounts
Optimal Window Functions (Single Pass)	O(n log n)	O(n)	Use LAG() and conditional logic to identify consecutive increasing sequences in one pass
Brute Force (Nested Subqueries)	O(n²)	O(n)	Check every possible consecutive date range for each customer manually

Window Functions with ROW_NUMBER — Algorithm Steps

Use ROW_NUMBER() to assign sequence numbers to transactions ordered by date for each customer
Create groups of consecutive dates by subtracting row number from date
Within each consecutive date group, use LAG() to compare current amount with previous amount
Identify sequences where amounts are strictly increasing
Filter for consecutive increasing sequences with 3+ days
Extract start and end dates for each valid sequence

Visualization

Tap to expand

Step-by-Step Walkthrough

Assign row numbers

Number transactions by date for each customer

Create date groups

Group consecutive dates using date - row_number formula

Check increasing amounts

Use LAG() to compare each amount with previous amount

Filter valid groups

Keep only groups with 3+ days where all amounts increase

Extract date ranges

Get start and end dates for each valid consecutive period

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting transactions by customer and date, then linear scan with window functions

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for intermediate results with row numbers and group identifiers

⚡ Linearithmic Space

Constraints

1 ≤ Number of transactions ≤ 10⁴
1 ≤ customer_id ≤ 1000
1 ≤ amount ≤ 10⁶
Each (customer_id, transaction_date) pair is unique
transaction_date is a valid date in format YYYY-MM-DD
Amounts must be strictly increasing (not equal)

42.0K Views

Medium-High Frequency

~25 min Avg. Time

1.9K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Functions with ROW_NUMBER — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler