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_date is between 2018-01-01 and 2023-12-31

Visualization

Tap to expand
Customers With Strictly Increasing Purchases INPUT cust_id year price 1 2019 100 1 2020 150 1 2021 200 2 2019 50 2 2021 40 3 2020 80 3 2021 90 Customer Purchase Patterns Cust 1: OK Cust 2: Gap! Cust 3: OK 2019 2020 2021 ALGORITHM STEPS 1 Group by Customer Aggregate orders per customer 2 Calculate Yearly Totals SUM(price) GROUP BY year 3 Fill Missing Years Set 0 for gaps in range 4 Check Strict Increase year[n] total > year[n-1] total Validation Process Cust 1: 100 --> 150 --> 200 PASS Cust 2: 50 --> 0 --> 40 FAIL Cust 3: 80 --> 90 PASS 0 breaks strict increase for Cust 2 FINAL RESULT Qualifying Customers Customer 1 2019: 100 --> 2020: 150 --> 2021: 200 Customer 3 2020: 80 --> 2021: 90 Excluded: Customer 2 Gap year 2020 = 0 (not increasing) OUTPUT customer_id: [1, 3] 2 Customers Key Insight: Missing years between first and last purchase must be treated as 0 total. This causes automatic disqualification since 0 breaks the strictly increasing pattern. Use window functions (LAG) to compare consecutive yearly totals and ensure each year exceeds the previous without any exceptions. TutorialsPoint - Customers With Strictly Increasing Purchases | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
24.5K Views
Medium Frequency
~20 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen