Consecutive Transactions with Increasing Amounts

Consecutive Transactions with Increasing Amounts

What is this problem about?

The Consecutive Transactions with Increasing Amounts interview question is a high-level database problem that asks you to identify specific trends in financial data. You are given a Transactions table containing customer IDs, transaction dates, and amounts. Your task is to find all customers who made at least three consecutive transactions on consecutive days where each transaction's amount was strictly greater than the previous one.

Why is this asked in interviews?

Amazon and other fintech-oriented companies use the Consecutive Transactions with Increasing Amounts coding problem to assess a candidate's ability to handle multi-layered logical constraints in SQL. It tests your mastery of grouping, ordering, and advanced analytical functions. This type of query is essential for identifying patterns such as credit card fraud, tiered reward eligibility, or consumer behavior shifts.

Algorithmic pattern used

This follows the Database interview pattern, specifically focusing on "Gaps and Islands" logic combined with sequence analysis. You typically solve this by:

1. Using LAG() to compare the current amount and date with the previous record.
2. Creating a "group identifier" for consecutive days where the increasing amount condition holds.
3. Grouping by this identifier and filtering for sequences with a length >= 3.

Example explanation

Imagine a user's activity:

• Jan 1: $10
• Jan 2: $20 (Increased, consecutive day)
• Jan 3: $25 (Increased, consecutive day) -> Match!
• Jan 4: $22 (Decreased, resets sequence)
• Jan 5: $30 (Increased, but Jan 4 was a break)

The logic must recognize that the first three days form a valid "island" of increasing transactions.

Common mistakes candidates make

• Ignoring the date constraint: Forgetting that the transactions must happen on consecutive days, not just in chronological order.
• Handling resets incorrectly: Failing to properly reset the count when the amount decreases or a day is skipped.
• Inefficient self-joins: Attempting to solve this with multiple self-joins, which becomes exponentially slower and harder to read compared to window functions.

Interview preparation tip

Practice "Gaps and Islands" problems. Understanding how to use ROW_NUMBER() or LAG() to identify contiguous blocks of data is a differentiator for senior-level SQL roles.