Skip to content

2292. Products With Three or More Orders in Two Consecutive Years πŸ”’

DifficultyMedium

Description

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.

Solutions

Solution 1

Thinking

We need products with at least three orders in two consecutive years. After grouping by product and year, we test adjacent years. A self-join is direct: mark whether a year has \(\ge 3\) orders, then join rows one year apart whose marks are both true.

The CTE aggregates \((\textit{product\_id}, \textit{YEAR})\) into \(\textit{mark}\); the join is \(p_1.y = p_2.y-1\) on the same product with both marks set, then we take distinct ids.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Write your MySQL query statement below
WITH
    P AS (
        SELECT product_id, YEAR(purchase_date) AS y, COUNT(1) >= 3 AS mark
        FROM Orders
        GROUP BY 1, 2
    )
SELECT DISTINCT p1.product_id
FROM
    P AS p1
    JOIN P AS p2 ON p1.y = p2.y - 1 AND p1.product_id = p2.product_id
WHERE p1.mark AND p2.mark;

Solution 2

Thinking

Solution 1 keeps every year and filters on \(\textit{mark}\). We can instead keep only years with \(\textit{HAVING COUNT}(1)\ge 3\), so the self-join has no extra predicate. The result is the same.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Write your MySQL query statement below
WITH
    P AS (
        SELECT product_id, YEAR(purchase_date) AS y
        FROM Orders
        GROUP BY 1, 2
        HAVING COUNT(1) >= 3
    )
SELECT DISTINCT p1.product_id
FROM
    P AS p1
    JOIN P AS p2 ON p1.y = p2.y - 1 AND p1.product_id = p2.product_id;

Comments