Products With Three or More Orders in Two Consecutive Years - Problem

Given a table Orders containing order information including product IDs and purchase dates, write a SQL solution to find all product IDs that were ordered three or more times in two consecutive years.

The table structure is:

  • order_id: Unique identifier for each order
  • product_id: ID of the product purchased
  • quantity: Number of items ordered
  • purchase_date: Date when the order was placed

Return the result table in any order.

Table Schema

Orders
Column Name Type Description
order_id PK int Unique identifier for each order
product_id int ID of the product purchased
quantity int Number of items ordered
purchase_date date Date when the order was placed
Primary Key: order_id
Note: Each row represents a single order with unique order_id

Input & Output

Example 1 — Basic Consecutive Years
Input Table:
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 2 2021-05-25
6 1 4 2021-01-11
7 2 5 2020-02-17
8 2 4 2021-06-02
9 3 4 2021-01-01
10 3 4 2021-05-25
Output:
product_id
1
💡 Note:

Product 1 has 3 orders in 2020 and 3 orders in 2021 (consecutive years), meeting the criteria. Product 2 has only 1 order in each year. Product 3 has 2 orders in 2021 but none in 2020/2022.
Example 2 — Multiple Valid Products
Input Table:
order_id product_id quantity purchase_date
1 1 5 2019-02-17
2 1 6 2019-05-28
3 1 2 2019-12-25
4 1 4 2020-01-11
5 1 4 2020-08-17
6 1 6 2020-06-02
7 2 4 2018-06-04
8 2 6 2018-08-20
9 2 7 2018-09-12
10 2 5 2019-01-01
11 2 2 2019-08-05
12 2 3 2019-11-28
Output:
product_id
1
2
💡 Note:

Product 1 has 3 orders in 2019 and 3 orders in 2020. Product 2 has 3 orders in 2018 and 3 orders in 2019. Both products meet the criteria for consecutive years with 3+ orders each.
Example 3 — No Valid Products
Input Table:
order_id product_id quantity purchase_date
1 1 4 2020-02-17
2 1 4 2020-05-02
3 1 4 2022-01-01
4 1 4 2022-05-25
5 1 4 2022-08-17
Output:
product_id
💡 Note:

Product 1 has only 2 orders in 2020 and 3 orders in 2022, but 2020 and 2022 are not consecutive years. No products meet the criteria.

Constraints

  • 1 ≤ order_id ≤ 100
  • 1 ≤ product_id ≤ 100
  • 1 ≤ quantity ≤ 100
  • purchase_date is a valid date

Visualization

Tap to expand
Products With Consecutive Year OrdersInput: Orders Tableproduct_idquantitydate172020-03142020-12172020-05162021-12122021-05142021-01GROUP BY product_id, yearYearly Aggregatesproduct_idyearcount120203120213SELF JOINconsecutive yearsOutput: Valid Productsproduct_id1Product 1 qualifies:3 orders in 20203 orders in 2021(consecutive years)
Understanding the Visualization
1
Group
Group orders by product and year
2
Filter
Keep products with 3+ orders per year
3
Join
Find consecutive year pairs
Key Takeaway
🎯 Key Insight: Use CTE to aggregate by year, then self-join to find consecutive year patterns with sufficient order counts

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 19 Meta 15
28.4K Views
Medium Frequency
~18 min Avg. Time
892 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