Customers With Strictly Increasing Purchases - Problem
Given a table Orders with columns order_id, customer_id, order_date, and price, write a SQL solution to find customers whose total yearly purchases are strictly increasing.
Key requirements:
- Calculate the total purchases (sum of prices) for each customer per year
- For years with no orders, consider total purchases as 0
- Only consider years from the customer's first order year to last order year
- Total purchases must be strictly increasing year over year
Return the customer IDs that satisfy this condition.
Table Schema
Orders
| Column Name | Type | Description |
|---|---|---|
order_id
PK
|
int | Unique identifier for each order |
customer_id
|
int | Customer who placed the order |
order_date
|
date | Date when the order was placed |
price
|
int | Price of the order |
Primary Key: order_id
Note: Each row represents one order with its customer, date, and price
Input & Output
Example 1 — Mixed Customer Purchase Patterns
Input Table:
| order_id | customer_id | order_date | price |
|---|---|---|---|
| 1 | 1 | 2019-07-01 | 100 |
| 2 | 1 | 2020-09-01 | 50 |
| 3 | 1 | 2021-05-01 | 200 |
| 4 | 2 | 2018-11-01 | 80 |
| 5 | 2 | 2019-03-01 | 120 |
| 6 | 2 | 2020-01-01 | 150 |
| 7 | 3 | 2019-06-01 | 300 |
| 8 | 3 | 2021-04-01 | 100 |
Output:
| customer_id |
|---|
| 2 |
💡 Note:
Customer 1: 2019: 100 → 2020: 50 → 2021: 200. Not strictly increasing (50 < 100).
Customer 2: 2018: 80 → 2019: 120 → 2020: 150. Strictly increasing ✓
Customer 3: 2019: 300 → 2020: 0 → 2021: 100. Not strictly increasing (0 < 300).
Example 2 — Single Year Customers
Input Table:
| order_id | customer_id | order_date | price |
|---|---|---|---|
| 1 | 1 | 2020-01-01 | 100 |
| 2 | 1 | 2020-12-31 | 200 |
| 3 | 2 | 2021-06-15 | 50 |
Output:
| customer_id |
|---|
| 1 |
| 2 |
💡 Note:
Both customers have orders in only one year each. Since there's no comparison possible with previous years, they both qualify as having strictly increasing purchases.
Constraints
-
1 ≤ order_id ≤ 1000 -
1 ≤ customer_id ≤ 100 -
1 ≤ price ≤ 1000 -
order_dateis between2018-01-01and2023-12-31
Visualization
Tap to expand
Understanding the Visualization
1
Group by Year
Sum purchases per customer per year
2
Fill Missing Years
Add missing years between first and last with 0
3
Check Trend
Verify strictly increasing pattern
Key Takeaway
🎯 Key Insight: Generate complete year ranges and use LAG() to detect strictly increasing patterns
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code