Products With Three Or More Orders In Two Consecutive Years - Leetcode Solution

Problem Statement:

You are given a table Orders containing data of orders sorted by the order_id column.

Write a SQL query to find all the products that were ordered for at least three times by the same customer in two consecutive years.

The Orders table is defined as follows:

Solution:

This is a complex SQL query problem. We need to find the products that were ordered for at least three times by the same customer in two consecutive years. Let's break the problem down into smaller parts and solve them one by one.

1. Find the orders made by the same customer for each product

We can use GROUP BY clause to group the orders by customer_id and product_id. We can then use COUNT() function to count the number of orders made by each customer for each product. The resulting table will have the following columns: product_id, customer_id, order_count.

The query for the above step is as follows:

SELECT product_id, customer_id, COUNT(*) as order_count
FROM Orders
GROUP BY product_id, customer_id;

2. Find the consecutive years for each customer

We can use the YEAR() function to extract the year from the order_date column. We can then use DISTINCT to get the unique years for each customer. The resulting table will have the following columns: customer_id, order_year.

The query for the above step is as follows:

SELECT DISTINCT customer_id, YEAR(order_date) as order_year
FROM Orders;

3. Find the products that were ordered for at least three times by the same customer in two consecutive years.

We can use the above two queries to join the Orders table with itself to get the products that were ordered for at least three times by the same customer in two consecutive years. We can use GROUP BY clause to group the products by product_id. The HAVING clause is used to filter the products that were ordered for at least three times by the same customer in two consecutive years.

The final query for the above step is as follows:

SELECT o1.product_id
FROM Orders o1
JOIN Orders o2 ON o1.product_id = o2.product_id AND o1.customer_id = o2.customer_id
JOIN (
    SELECT DISTINCT customer_id, YEAR(order_date) as order_year
    FROM Orders
) t1 ON t1.customer_id = o1.customer_id
WHERE YEAR(o1.order_date) = t1.order_year AND YEAR(o2.order_date) = t1.order_year + 1
GROUP BY o1.product_id, o1.customer_id
HAVING COUNT(*) >= 3;

Explanation:

In the above query, we join the Orders table with itself using the product_id and customer_id columns. We then join the resulting table with the table that we created in step 2 to get the orders made by the same customer in two consecutive years. We use WHERE clause to filter the orders made in two consecutive years. We then use GROUP BY clause to group the products by product_id and customer_id. Finally, we use the HAVING clause to filter the products that were ordered for at least three times by the same customer.

Conclusion:

In this problem, we used SQL queries to find the products that were ordered for at least three times by the same customer in two consecutive years. We broke the problem down into smaller parts and solved them one by one. The final query is complex, but it should be easy to understand if you follow the steps.

1