Customers With Strict Annual Purchase Growth

HARD30 pts

A growth analytics team needs to identify customers whose yearly purchase spend keeps rising without any stagnation or decline.

Table: Orders

order_id: unique order identifier
customer_id: customer identifier
order_date: order date in YYYY-MM-DD format
price: order value for that row

Yearly total definition: For each customer and calendar year, yearly_total is SUM(price) for orders in that year.

Evaluation window per customer:

Start at the customer's first order year.
End at the customer's last order year.
Every year in this closed interval must be evaluated.
If a year inside the interval has no orders, its yearly_total is 0.

Qualification rule: A customer is valid only if yearly totals are strictly increasing across every adjacent year pair in the interval. That means for each year y in the interval (except the last): total(y + 1) > total(y)

Task: Return all qualifying customer IDs.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Orders:
| 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 has yearly totals 2300 < 3000 < 3100 < 4700, so they qualify. Customer 2 fails because year 2016 is missing and therefore contributes 0 between 700 and 1000. Customer 3 fails because 900 is not strictly less than 900.

Example

Input

Orders:
| order_id | customer_id | order_date  | price |
|----------|-------------|-------------|-------|
| 10       | 10          | 2020-03-05  | 400   |
| 11       | 10          | 2020-10-20  | 600   |
| 12       | 10          | 2021-07-04  | 1001  |
| 13       | 11          | 2020-02-14  | 500   |
| 14       | 11          | 2022-08-01  | 900   |
| 15       | 12          | 2021-11-11  | 400   |

Output

[
  {"customer_id":10},
  {"customer_id":12}
]

Explanation

Customer 10 rises from 1000 to 1001 and qualifies. Customer 11 fails because year 2021 has zero spend between first and last years. Customer 12 has only one year and therefore has no violating adjacent pair.

Example

Input

Orders:
| order_id | customer_id | order_date  | price |
|----------|-------------|-------------|-------|
| 20       | 20          | 2018-01-02  | 100   |
| 21       | 20          | 2019-02-03  | 200   |
| 22       | 20          | 2020-03-04  | 400   |
| 23       | 21          | 2018-05-05  | 150   |
| 24       | 21          | 2019-06-06  | 150   |
| 25       | 22          | 2021-12-31  | 900   |

Output

[
  {"customer_id":20},
  {"customer_id":22}
]

Explanation

Customer 20 is strictly increasing each year. Customer 21 has equal totals in 2018 and 2019, so strict growth is violated. Customer 22 appears in one year only and qualifies.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Orders <= 200000
order_id values are unique
1 <= order_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= price <= 100000
order_date is a valid date in YYYY-MM-DD format
Yearly total is SUM(price) over the customer's orders in that year
Missing intermediate years between first and last order year count as yearly total 0
Strict rule means total(next_year) > total(current_year) for every adjacent pair
Single-year customers qualify
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named customer_id

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Orders =

[{"order_id":1,"customer_id":1,"order_date":"2019-07-01","price":1100},{"order_id":2,"customer_id":1,"order_date":"2019-11-01","price":1200},{"order_id":3,"customer_id":1,"order_date":"2020-05-26","price":3000},{"order_id":4,"customer_id":1,"order_date":"2021-08-31","price":3100},{"order_id":5,"customer_id":1,"order_date":"2022-12-07","price":4700},{"order_id":6,"customer_id":2,"order_date":"2015-01-01","price":700},{"order_id":7,"customer_id":2,"order_date":"2017-11-07","price":1000},{"order_id":8,"customer_id":3,"order_date":"2017-01-01","price":900},{"order_id":9,"customer_id":3,"order_date":"2018-11-07","price":900}]

Customers With Strict Annual Purchase Growth

Hints

Customers With Strict Annual Purchase Growth

Hints