Products With Sustained Annual Order Volume

Medium

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 1

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 2

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 3

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.

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

Loading problem...

Preparing your editor. You can read the problem while it loads.

Products With Sustained Annual Order Volume

Medium

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 1

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 2

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 3

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.

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