Consecutive Transactions with Increasing Amounts - Problem

Given a table Transactions with columns transaction_id, customer_id, transaction_date, and amount, write an SQL query to find customers who have made consecutive transactions with increasing amounts for at least three consecutive days.

Return:

  • customer_id
  • consecutive_start - Start date of the consecutive period
  • consecutive_end - End date of the consecutive period

Order results by customer_id, consecutive_start, and consecutive_end in ascending order.

Table Schema

Transactions
Column Name Type Description
transaction_id PK int Primary key - unique transaction identifier
customer_id int Customer identifier
transaction_date date Date of transaction
amount int Transaction amount
Primary Key: transaction_id
Note: Each (customer_id, transaction_date) combination is unique

Input & Output

Example 1 — Multiple Consecutive Periods
Input Table:
transaction_id customer_id transaction_date amount
1 101 2024-01-01 100
2 101 2024-01-02 150
3 101 2024-01-03 200
4 101 2024-01-04 250
5 101 2024-01-06 120
6 101 2024-01-07 180
7 101 2024-01-08 220
8 102 2024-01-01 300
9 102 2024-01-02 250
Output:
customer_id consecutive_start consecutive_end
101 2024-01-01 2024-01-04
101 2024-01-06 2024-01-08
💡 Note:

Customer 101 has two periods of consecutive increasing transactions: from Jan 1-4 (100→150→200→250) and Jan 6-8 (120→180→220). Customer 102 only has 2 consecutive days, which doesn't meet the minimum requirement of 3 days.
Example 2 — No Qualifying Periods
Input Table:
transaction_id customer_id transaction_date amount
1 201 2024-01-01 100
2 201 2024-01-02 200
3 201 2024-01-03 150
4 201 2024-01-04 300
5 202 2024-01-01 500
Output:
customer_id consecutive_start consecutive_end
💡 Note:

No customers qualify. Customer 201 has only 2 consecutive increasing days (Jan 1-2), then amount decreases on Jan 3. Customer 202 has only 1 transaction. Neither meets the 3+ consecutive days requirement.
Example 3 — Exact 3-Day Period
Input Table:
transaction_id customer_id transaction_date amount
1 301 2024-01-01 50
2 301 2024-01-02 75
3 301 2024-01-03 100
4 301 2024-01-05 200
Output:
customer_id consecutive_start consecutive_end
301 2024-01-01 2024-01-03
💡 Note:

Customer 301 has exactly 3 consecutive days with increasing amounts (50→75→100). The transaction on Jan 5 doesn't count as consecutive since Jan 4 is missing.

Constraints

  • 1 ≤ transaction_id ≤ 100000
  • 1 ≤ customer_id ≤ 1000
  • 1 ≤ amount ≤ 100000
  • Each (customer_id, transaction_date) is unique
  • At least 3 consecutive days required

Visualization

Tap to expand
Consecutive Increasing Transactions AnalysisInput: Transaction Datacustomerdateamount101Jan 1100101Jan 2150101Jan 3200101Jan 4250📈IncreasingPatternWindow FunctionLAG AnalysisOutput: Consecutive Periodscustomer_idstartend101Jan 1Jan 4Key Algorithm Steps1. LAG ComparisonCompare each amount withprevious day using LAG()Mark increasing transactions2. Group SequencesGroup consecutiveincreasing periodsFilter for 3+ days3. Output ResultsReturn customer_id,start_date, end_datefor qualifying periods
Understanding the Visualization
1
Input
Customer transactions with dates and amounts
2
Window Analysis
Compare consecutive days using LAG
3
Output
Consecutive periods of 3+ increasing days
Key Takeaway
🎯 Key Insight: Use LAG window function to compare consecutive transactions and identify increasing patterns over time

Related Problems

Asked in
Amazon 28 Google 22 Microsoft 18 Meta 15
33.0K Views
Medium Frequency
~25 min Avg. Time
892 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