Products With Sustained Annual Order Volume

MEDIUM20 pts

A commerce analytics team stores every purchase event in a single order ledger and wants to identify products with stable demand across adjacent years.

Table: Orders

order_id: unique order identifier
product_id: product identifier
quantity: number of units in that order row
purchase_date: order date (YYYY-MM-DD)

Business rule: A product is considered year-over-year stable if there exists at least one calendar year y where:

the product has at least 3 orders in year y
the product has at least 3 orders in year y + 1

Important interpretation:

Count order rows, not quantity totals.
A product may have many years of activity; qualifying through any adjacent year pair is enough.
Return each qualifying product only once.

Task: Return one column product_id containing all products that satisfy the rule.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Orders:
| order_id | product_id | quantity | purchase_date |
|----------|------------|----------|---------------|
| 1        | 1          | 7        | 2020-03-16    |
| 2        | 1          | 4        | 2020-12-02    |
| 3        | 1          | 7        | 2020-05-10    |
| 4        | 1          | 6        | 2021-12-23    |
| 5        | 1          | 5        | 2021-05-21    |
| 6        | 1          | 6        | 2021-10-11    |
| 7        | 2          | 6        | 2022-10-11    |

Output

[
  {"product_id":1}
]

Explanation

Product 1 has three orders in 2020 and three in 2021, so it qualifies. Product 2 has activity in only one year.

Example

Input

Orders:
| order_id | product_id | quantity | purchase_date |
|----------|------------|----------|---------------|
| 10       | 100        | 2        | 2019-01-02    |
| 11       | 100        | 5        | 2019-02-18    |
| 12       | 100        | 3        | 2019-07-20    |
| 13       | 100        | 4        | 2020-03-11    |
| 14       | 100        | 9        | 2020-11-30    |
| 15       | 101        | 1        | 2021-01-03    |
| 16       | 101        | 2        | 2021-05-09    |
| 17       | 101        | 3        | 2021-10-15    |
| 18       | 101        | 1        | 2022-02-27    |
| 19       | 101        | 2        | 2022-06-19    |
| 20       | 101        | 5        | 2022-09-01    |
| 21       | 102        | 8        | 2020-04-04    |
| 22       | 102        | 8        | 2020-08-08    |
| 23       | 102        | 8        | 2022-08-08    |
| 24       | 102        | 8        | 2022-09-09    |
| 25       | 102        | 8        | 2022-10-10    |

Output

[
  {"product_id":101}
]

Explanation

Product 101 qualifies via years 2021 and 2022. Product 100 misses 2020 because it has only two orders there, and product 102 has no adjacent qualifying years.

Example

Input

Orders:
| order_id | product_id | quantity | purchase_date |
|----------|------------|----------|---------------|
| 31       | 200        | 10000    | 2023-01-05    |
| 32       | 200        | 10000    | 2023-04-12    |
| 33       | 200        | 10000    | 2024-02-02    |
| 34       | 200        | 10000    | 2024-12-20    |
| 35       | 201        | 1        | 2024-01-01    |
| 36       | 201        | 1        | 2024-06-01    |
| 37       | 201        | 1        | 2024-11-01    |
| 38       | 201        | 1        | 2025-01-10    |
| 39       | 201        | 1        | 2025-05-10    |
| 40       | 201        | 1        | 2025-09-10    |

Output

[
  {"product_id":201}
]

Explanation

Qualification depends on order count, not quantity totals. Product 200 has large quantities but only two orders per year, so it does not qualify.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Orders <= 200000
order_id values are unique
1 <= order_id <= 1000000000
1 <= product_id <= 1000000000
1 <= quantity <= 100000
purchase_date is a valid date in YYYY-MM-DD format
A product qualifies if it has at least 3 order rows in year y and at least 3 in year y + 1
Use calendar year extracted from purchase_date
Count rows, not the sum of quantity
Return each qualifying product_id exactly once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame containing exactly one column named product_id

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Orders =

[{"order_id":1,"product_id":1,"quantity":7,"purchase_date":"2020-03-16"},{"order_id":2,"product_id":1,"quantity":4,"purchase_date":"2020-12-02"},{"order_id":3,"product_id":1,"quantity":7,"purchase_date":"2020-05-10"},{"order_id":4,"product_id":1,"quantity":6,"purchase_date":"2021-12-23"},{"order_id":5,"product_id":1,"quantity":5,"purchase_date":"2021-05-21"},{"order_id":6,"product_id":1,"quantity":6,"purchase_date":"2021-10-11"},{"order_id":7,"product_id":2,"quantity":6,"purchase_date":"2022-10-11"}]

Products With Sustained Annual Order Volume

Hints

Products With Sustained Annual Order Volume

Hints