Skip to main content
Talentd

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

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

Problem Statement

Table: Orders

+---------------+------+
| Column Name   | Type |
+---------------+------+
| order_id      | int  |
| product_id    | int  |
| quantity      | int  |
| purchase_date | date |
+---------------+------+
order_id contains unique values.
Each row in this table contains the ID of an order, the id of the product purchased, the quantity, and the purchase date.

 

Write a solution to report the IDs of all the products that were ordered three or more times in two consecutive years.

Return the result table in any order.

The result format is shown in the following example.

 

Example 1:

Input: 
Orders 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          | 5        | 2021-05-21    |
| 6        | 1          | 6        | 2021-10-11    |
| 7        | 2          | 6        | 2022-10-11    |
+----------+------------+----------+---------------+
Output: 
+------------+
| product_id |
+------------+
| 1          |
+------------+
Explanation: 
Product 1 was ordered in 2020 three times and in 2021 three times. Since it was ordered three times in two consecutive years, we include it in the answer.
Product 2 was ordered one time in 2022. We do not include it in the answer.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Yearly Aggregation + Self JoinO(n log n)O(n)Most portable SQL solution; works across MySQL, PostgreSQL, and SQL Server
Aggregation + Window Function (LAG)O(n log n)O(n)Cleaner logic when the database supports window functions like MySQL 8+

Video Solution

Leetcode MEDIUM 2292 - Products with 3 or More Orders in 2 Consecutive Years - Explained by EDS • Everyday Data Science • 589 views views

Frequently Asked Questions

Is Products With Three or More Orders in Two Consecutive Years easy or hard?
The problem is rated Medium because it combines multiple SQL concepts: date extraction, aggregation with HAVING, and validating consecutive years. The query is straightforward once you recognize that the dataset should first be reduced to product-year counts before checking adjacency.
Products With Three or More Orders in Two Consecutive Years Python/Java solution
This problem is categorized under SQL on LeetCode, so the expected solution is written as a database query rather than Python or Java code. The logic uses GROUP BY product_id and YEAR(order_date), filters with HAVING COUNT(*) >= 3, and checks consecutive years using either a self join or a LAG window function.
How to solve Products With Three or More Orders in Two Consecutive Years in O(n)?
Pure O(n) complexity is uncommon in SQL because grouping and sorting steps are usually required. The practical solution aggregates orders per product per year and then compares adjacent years using either a self join or a window function like LAG. These approaches typically run around O(n log n) but are efficient for large datasets.
What is the best approach for Products With Three or More Orders in Two Consecutive Years?
The most reliable approach aggregates orders by product_id and year, filters years with at least three orders using HAVING COUNT(*) >= 3, then performs a self join where the year difference equals 1. This confirms that the same product met the requirement in two consecutive years. The method works across most SQL databases and runs in roughly O(n log n) time due to grouping operations.
Is Products With Three or More Orders in Two Consecutive Years asked at Google/Amazon/Meta?
SQL aggregation and time-based grouping problems appear frequently in interviews at companies like Amazon, Meta, and Google. Variations often require identifying consecutive time periods, minimum counts per period, or trends across years. This problem tests understanding of GROUP BY, HAVING filters, and relational joins.
What data structure is used in Products With Three or More Orders in Two Consecutive Years?
The solution relies on relational database operations rather than traditional in-memory data structures. SQL grouping creates a temporary aggregated dataset mapping product_id to yearly order counts. Joins or window functions are then used to compare adjacent years for the same product.
What is the time complexity of Products With Three or More Orders in Two Consecutive Years?
The SQL solution mainly spends time grouping orders by product and year. Aggregation and sorting operations typically result in about O(n log n) processing time depending on the database engine. The additional self join operates on the aggregated dataset, which is much smaller than the original table.

Ready to solve this problem?

Practice Products With Three or More Orders in Two Consecutive Years with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary