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 Transactions with Increasing Amounts INPUT: Transactions Table cust_id date amount 101 2024-01-01 100 101 2024-01-02 150 101 2024-01-03 200 101 2024-01-04 250 102 2024-01-01 80 102 2024-01-02 70 Customer 101 Amounts: 100 150 200 250 Increasing Trend ALGORITHM STEPS 1 Order by Customer, Date Sort transactions per customer 2 Use LAG() Function Compare with previous row 3 Check Consecutive Days date - prev_date = 1 day 4 Check Amount Increase amount > prev_amount 5 Group Consecutive Rows Assign group IDs via SUM 6 Filter Groups >= 3 Days HAVING COUNT(*) >= 3 LAG(amount) OVER ( PARTITION BY cust_id ORDER BY date) FINAL RESULT cust_id start_date end_date 101 2024-01-01 2024-01-04 4 Consecutive Days Customer 101 Pattern: Jan 1 $100 Jan 2 $150 Jan 3 $200 Jan 4 $250 Customer 102: Not qualifying $80 --> $70 (decreasing) X Not 3+ days increasing Key Insight: Use Window Functions (LAG) to compare each transaction with the previous one, then create groups using a running SUM that increments when the sequence breaks. Filter groups with 3+ consecutive days. Time Complexity: O(n log n) for sorting | Space Complexity: O(n) for window function results TutorialsPoint - Consecutive Transactions with Increasing Amounts | Optimal Solution

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