Products With Three or More Orders in Two Consecutive Years

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

Database Medium

Products With Three or More Orders in Two Consecutive Years

Imagine you're working as a data analyst for an e-commerce company, and your manager wants to identify which products have been consistently popular over time. Specifically, they want to find products that were ordered at least 3 times in two consecutive years.

You have access to an Orders table with the following structure:

Column Name	Type
order_id	int
product_id	int
quantity	int
purchase_date	date

Goal: Write a SQL query to find all product_ids that were ordered 3 or more times in at least one pair of consecutive years.

Example: If a product was ordered 3 times in 2019 and 4 times in 2020, it qualifies. If it was ordered 5 times in 2021 and 2 times in 2022, it doesn't qualify (since 2022 has only 2 orders).

Return the result in any order.

Input & Output

example_1.sql — Basic Case

$ Input: Orders table:\n| order_id | product_id | quantity | purchase_date |\n|----------|------------|----------|---------------|\n| 1 | 1 | 5 | 2019-02-17 |\n| 2 | 1 | 6 | 2019-03-01 |\n| 3 | 1 | 2 | 2019-03-20 |\n| 4 | 1 | 4 | 2020-02-17 |\n| 5 | 1 | 3 | 2020-05-12 |\n| 6 | 1 | 2 | 2020-06-13 |

› Output: | product_id |\n|------------|\n| 1 |

💡 Note: Product 1 was ordered 3 times in 2019 and 3 times in 2020 (consecutive years), meeting our criteria of 3+ orders in both years.

example_2.sql — Multiple Products

$ Input: Orders table:\n| order_id | product_id | quantity | purchase_date |\n|----------|------------|----------|---------------|\n| 1 | 1 | 1 | 2019-01-01 |\n| 2 | 1 | 1 | 2019-02-01 |\n| 3 | 1 | 1 | 2019-03-01 |\n| 4 | 1 | 1 | 2020-01-01 |\n| 5 | 1 | 1 | 2020-02-01 |\n| 6 | 1 | 1 | 2020-03-01 |\n| 7 | 2 | 1 | 2020-01-01 |\n| 8 | 2 | 1 | 2020-02-01 |\n| 9 | 2 | 1 | 2021-01-01 |\n| 10 | 2 | 1 | 2021-02-01 |\n| 11 | 2 | 1 | 2021-03-01 |

› Output: | product_id |\n|------------|\n| 1 |

💡 Note: Product 1 has 3 orders in 2019 and 3 orders in 2020 (consecutive years with 3+ each). Product 2 has 2 orders in 2020 and 3 orders in 2021, but doesn't qualify because 2020 has only 2 orders.

example_3.sql — Edge Case - No Qualifying Products

› Output: | product_id |\n|------------|\n(empty result)

💡 Note: Product 1 has 2 orders in 2019 and 3 orders in 2021, but these are not consecutive years. No product meets the criteria.

Constraints

1 ≤ order_id ≤ 10⁶
1 ≤ product_id ≤ 10⁴
1 ≤ quantity ≤ 100
purchase_date is between 2017-01-01 and 2030-12-31
All order_id values are unique

Visualization

Tap to expand

Understanding the Visualization

Collect Annual Data

Count how many times each product was ordered per year

Compare Adjacent Years

Use LAG window function to check if consecutive years both have 3+ orders

Identify Winners

Return products that maintained popularity across consecutive years

Key Takeaway

🎯 Key Insight: Window functions eliminate the need for expensive self-joins by allowing us to access previous row data within the same query, making consecutive year comparisons efficient and elegant.

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses window functions with LAG to compare consecutive years efficiently. First, we count orders per product per year, then use LAG() OVER (PARTITION BY product_id ORDER BY order_year) to access the previous year's data. This avoids expensive self-joins and completes in O(n log n) time with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Self-Join for Every Year Pair)	O(n²)	O(n)	Join the table with itself for every possible consecutive year combination
Window Function (Optimal)	O(n log n)	O(n)	Use LAG window function to compare consecutive years in a single scan

Brute Force (Self-Join for Every Year Pair) — Algorithm Steps

Extract year from purchase_date and count orders per product per year
Self-join the result table on product_id where year2 = year1 + 1
Filter products where both years have >= 3 orders
Select distinct product_ids

Visualization

Tap to expand

Step-by-Step Walkthrough

Count Orders by Year

Group orders by product and year, counting occurrences

Self-Join on Consecutive Years

Join the yearly counts table with itself where year2 = year1 + 1

Filter Results

Keep only products with 3+ orders in both consecutive years

Code -

solution.c — C

// SQL Solution (Brute Force)
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = "WITH yearly_counts AS (\n"
                         "    SELECT product_id, YEAR(purchase_date) as order_year, COUNT(*) as order_count\n"
                         "    FROM Orders\n"
                         "    GROUP BY product_id, YEAR(purchase_date)\n"
                         ")\n"
                         "SELECT DISTINCT y1.product_id\n"
                         "FROM yearly_counts y1\n"
                         "JOIN yearly_counts y2\n"
                         "    ON y1.product_id = y2.product_id\n"
                         "    AND y2.order_year = y1.order_year + 1\n"
                         "WHERE y1.order_count >= 3 AND y2.order_count >= 3;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Self-join creates quadratic complexity as it compares each row with potentially many others

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary tables store intermediate results for year-wise counts

⚡ Linearithmic Space

42.4K Views

Medium Frequency

~18 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Self-Join for Every Year Pair) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler