Watch 2 video solutions for Customers With Strictly Increasing Purchases, a hard level problem involving Database. This walkthrough by Everyday Data Science has 1,033 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
0.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.
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.
| 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 |