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 3+ Orders in Consecutive Years INPUT: Orders Table id prod qty date 1 101 5 2020-03-01 2 101 3 2020-06-15 3 101 2 2020-09-20 4 101 4 2021-02-10 5 101 1 2021-05-22 6 101 2 2021-11-30 7 102 1 2020-01-05 Group by Product + Year Product 101 6 orders total Product 102 1 order total ALGORITHM STEPS 1 Extract Year YEAR(purchase_date) 2 Group & Count GROUP BY product_id, year HAVING COUNT(*) >= 3 3 Self Join Match consecutive years t1.year = t2.year - 1 4 Select Distinct Return unique product_ids Product 101 Analysis: 2020 3 orders 2021 3 orders Consecutive! OK FINAL RESULT product_id 101 Why Product 101? 2020: 3 orders (OK) 2021: 3 orders (OK) 2020-2021 consecutive (OK) All conditions met! Why NOT Product 102? Only 1 order in 2020 Does not meet 3+ threshold Key Insight: Use a CTE/subquery to first aggregate orders by (product_id, year) with HAVING COUNT(*) >= 3. Then self-join this result on product_id WHERE year1 = year2 - 1 to find consecutive years. SELECT DISTINCT ensures each qualifying product appears only once in the result set. TutorialsPoint - Products With Three or More Orders in Two Consecutive Years | Optimal Solution

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