Customers With Strictly Increasing Purchases

Customers With Strictly Increasing Purchases

What is this problem about?

The Customers With Strictly Increasing Purchases interview question is a high-difficulty database problem. You need to find customers whose total annual spending has increased every single year since their first purchase. If a customer skips a year or their spending stays the same or decreases, they are disqualified. This Customers With Strictly Increasing Purchases coding problem requires identifying continuous trends over multiple rows of data.

Why is this asked in interviews?

Amazon uses this to test advanced SQL skills like "Gaps and Islands" detection and recursive window functions. It evaluates your ability to compare a value in the current row with a value in the previous row across a time series. It also checks if you can handle the constraint that there must be no gaps in years.

Algorithmic pattern used

This follows the Database interview pattern of "Consecutive Trend Analysis."

1. Aggregate: Calculate the total spending per customer per year.
2. Compare: Use LAG() to compare the current year's spending with the previous year's and ensure it's strictly greater.
3. Sequence Check: Use ROW_NUMBER() or a year-difference check to ensure there are no missing years between the first and last recorded purchase.

Example explanation

Customer A:

• 2020: $100
• 2021: $150
• 2022: $200 Result: Included (increasing and no gaps). Customer B:
• 2020: $100
• 2022: $300 Result: Excluded (gap in 2021). Customer C:
• 2020: $100
• 2021: $100 Result: Excluded (not strictly increasing).

Common mistakes candidates make

• Ignoring Year Gaps: Successfully checking that $300 >$100 but forgetting that 2022 is not consecutive to 2020.
• Strict vs. Non-Strict: Using > instead of >= or vice versa.
• Complexity: Writing massive queries with five or six joins instead of using window functions like LEAD or LAG.

Interview preparation tip

When faced with trend problems, simplify. First, get the data into a "one row per year per customer" format. Then, apply the window functions to check the trend. Breaking it into steps makes it easier to write and debug.