Customers With Strictly Increasing Purchases - Solution & Explanation
Problem Statement
Table: Orders
+--------------+------+ | Column Name | Type | +--------------+------+ | order_id | int | | customer_id | int | | order_date | date | | price | int | +--------------+------+ order_id is the column with unique values for this table. Each row contains the id of an order, the id of customer that ordered it, the date of the order, and its price.
Write a solution to report the IDs of the customers with the total purchases strictly increasing yearly.
- The total purchases of a customer in one year is the sum of the prices of their orders in that year. If for some year the customer did not make any order, we consider the total purchases
0. - The first year to consider for each customer is the year of their first order.
- The last year to consider for each customer is the year of their last order.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Orders table: +----------+-------------+------------+-------+ | order_id | customer_id | order_date | price | +----------+-------------+------------+-------+ | 1 | 1 | 2019-07-01 | 1100 | | 2 | 1 | 2019-11-01 | 1200 | | 3 | 1 | 2020-05-26 | 3000 | | 4 | 1 | 2021-08-31 | 3100 | | 5 | 1 | 2022-12-07 | 4700 | | 6 | 2 | 2015-01-01 | 700 | | 7 | 2 | 2017-11-07 | 1000 | | 8 | 3 | 2017-01-01 | 900 | | 9 | 3 | 2018-11-07 | 900 | +----------+-------------+------------+-------+ Output: +-------------+ | customer_id | +-------------+ | 1 | +-------------+ Explanation: Customer 1: The first year is 2019 and the last year is 2022 - 2019: 1100 + 1200 = 2300 - 2020: 3000 - 2021: 3100 - 2022: 4700 We can see that the total purchases are strictly increasing yearly, so we include customer 1 in the answer. Customer 2: The first year is 2015 and the last year is 2017 - 2015: 700 - 2016: 0 - 2017: 1000 We do not include customer 2 in the answer because the total purchases are not strictly increasing. Note that customer 2 did not make any purchases in 2016. Customer 3: The first year is 2017, and the last year is 2018 - 2017: 900 - 2018: 900 We do not include customer 3 in the answer because the total purchases are not strictly increasing.
Approach Overview
Problem Overview: You are given purchase records for customers. The goal is to return customers whose total purchase amount per year is strictly increasing compared to their previous year of purchases. If a customer spends more each year than the year before, they qualify.
Approach 1: Self-Join on Yearly Aggregates (O(n log n) time, O(n) space)
Start by aggregating purchases per customer_id and year using GROUP BY. This produces a table of yearly totals. Then self‑join this aggregated table where the same customer appears in two consecutive rows (previous year and next year). Compare the totals to ensure the later year has a larger value. Finally group again by customer and verify that all adjacent year comparisons satisfy the strictly increasing condition.
This approach works in databases without strong window function support. The downside is multiple joins and grouping operations, which increases query complexity and sorting overhead.
Approach 2: Window Function with LAG (O(n log n) time, O(n) space)
The cleaner solution aggregates yearly spending first, then uses the SQL window function LAG() to access the previous year's purchase total for the same customer. After computing yearly totals with SUM(amount) grouped by customer_id and year, apply LAG(total) partitioned by customer_id and ordered by year. This exposes the previous year's value directly in the same row.
Filter rows where the current total is greater than the previous year's total. If every comparable row satisfies this condition, the customer's spending is strictly increasing. Finally group by customer_id and ensure at least two yearly records exist.
This pattern—aggregation followed by window comparison—is common in analytical SQL problems. Window functions reduce joins and make the intent clearer.
Recommended for interviews: The LAG() window function approach is what most interviewers expect. It demonstrates strong SQL fundamentals: aggregation, partitioned ordering, and row-wise comparison. Understanding the self‑join approach still helps because it shows how the logic works without advanced features. Window functions are frequently used in SQL, database, and window function interview questions.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join on Yearly Aggregates | O(n log n) | O(n) | When window functions are unavailable or restricted |
| Window Function with LAG | O(n log n) | O(n) | Preferred modern SQL solution for comparing values across ordered rows |
Video Solution
Leetcode HARD 2474 - Customers with Strictly Increasing Purchase -Explained by Everyday Data Science • Everyday Data Science • 1,136 views views
Watch 1 more video solutions →Frequently Asked Questions
Is Customers With Strictly Increasing Purchases easy or hard?
Customers With Strictly Increasing Purchases Python/Java solution
How to solve Customers With Strictly Increasing Purchases in O(n)?
What is the best approach for Customers With Strictly Increasing Purchases?
Is Customers With Strictly Increasing Purchases asked at Google/Amazon/Meta?
What data structure is used in Customers With Strictly Increasing Purchases?
What is the time complexity of Customers With Strictly Increasing Purchases?
Ready to solve this problem?
Practice Customers With Strictly Increasing Purchases with our built-in code editor and test cases.
Practice on FleetCode