Products With Three or More Orders in Two Consecutive Years - Solution & Explanation

Problem Overview: You are given an orders table containing product purchases with their order dates. The task is to return product IDs that received at least three orders in two consecutive years. The solution requires grouping orders by product and year, then checking whether any adjacent years both satisfy the minimum order count.

Approach 1: Yearly Aggregation + Self Join (O(n log n) time, O(n) space)

The core idea is to first compute how many orders each product received per year. Use GROUP BY product_id, YEAR(order_date) and filter with HAVING COUNT(*) >= 3. This produces a reduced dataset containing only product-year pairs that already satisfy the three-order requirement. Next, join this dataset with itself on the same product_id where the year difference is exactly 1. If both rows exist, the product had qualifying order counts in consecutive years. The final result selects distinct product IDs from these matches. This pattern is common in SQL interview problems where events must be validated across adjacent time periods.

The key insight is reducing the data early. Instead of comparing every order record across years, aggregation collapses the dataset into one row per product-year. The self join then becomes cheap because it only compares qualifying years rather than raw order rows. In database interviews, this technique demonstrates strong understanding of filtering with HAVING and relational joins.

Approach 2: Aggregation + Window Function (O(n log n) time, O(n) space)

Modern SQL engines such as MySQL 8 support window functions that simplify consecutive-year checks. Start with the same aggregation step to compute yearly order counts per product and filter years where the count is at least three. Then apply LAG(year) partitioned by product_id and ordered by year. This lets you compare the current year with the previous qualifying year for that product. If year - LAG(year) = 1, the product has two consecutive qualifying years. Window functions remove the need for a self join and often produce clearer logic, especially when solving sequence problems involving time-based data. Problems that require detecting adjacent records frequently rely on window functions.

Recommended for interviews: The aggregation plus self-join approach is the most universally accepted answer. It works on nearly all SQL engines and demonstrates a clear understanding of grouping, filtering, and relational joins. Window functions provide a cleaner implementation when supported, but interviewers typically expect the aggregation-first reasoning. Showing the grouped dataset and then validating consecutive years makes your thought process easy to follow.