2292. 连续两年有 3 个及以上订单的产品（难度：中等）

题目描述

（通过次数556 | 提交次数1,253，通过率44.37%）

表: Orders
+---------------+------+
| Column Name   | Type |
+---------------+------+
| order_id      | int  |
| product_id    | int  |
| quantity      | int  |
| purchase_date | date |
+---------------+------+
order_id 是该表的主键。
该表中的每一行都包含订单 ID、购买的产品 ID、数量和购买日期。
编写一个 SQL 查询，获取连续两年订购三次或三次以上的所有产品的 id。

以任意顺序返回结果表。
查询结果格式示例如下。
示例 1:
输入: 
Orders 表:
+----------+------------+----------+---------------+
| 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    |
+----------+------------+----------+---------------+
输出: 
+------------+
| product_id |
+------------+
| 1          |
+------------+
解释: 
产品 1 在 2020 年和 2021 年都分别订购了三次。由于连续两年订购了三次，所以我们将其包含在答案中。
产品 2 在 2022 年订购了一次。我们不把它包括在答案中。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/products-with-three-or-more-orders-in-two-consecutive-years

//测试数据
Create table If Not Exists Orders (order_id int, product_id int, quantity int, purchase_date date);

insert into Orders (order_id, product_id, quantity, purchase_date) values ('1', '1', '7', '2020-03-16');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('2', '1', '4', '2020-12-02');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('3', '1', '7', '2020-05-10');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('4', '1', '6', '2021-12-23');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('5', '1', '5', '2021-05-21');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('6', '1', '6', '2021-10-11');
insert into Orders (order_id, product_id, quantity, purchase_date) values ('7', '2', '6', '2022-10-11');

解题思路

Orders表中保存了每个订单的详细信息，当然也包括下单时间（具体到年/月/日）。
题目要求：查询连续2年，每年都被下单3次及以上（注意是订单数量，不是购买数量）的产品。
那么，首先，我们需要知道每个产品每年被下单几次。因为下单时间的粒度到日，我们需要使用YEAR函数提取出年，然后再与产品ID一起汇总统计订单数。
知道了每个产品每年的订单数，再来计算连续2年都被下单3次及以上的产品。
可以使用自关联，限定今年的日期与去年的日期+1相等，这样就可以把今年及去年的产品购买数据拉平至同一行。也就可以很容易判断出今年下单3次及以上、去年也下单3次及以上的产品了。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp as (
select
        product_id,
year(purchase_date) purchase_year
from Orders
group by product_id,
year(purchase_date)
having count(1) >= 3
)
select
    a.product_id
from tmp a
inner join tmp b
on a.product_id = b.product_id
and a.purchase_year = b.purchase_year + 1
group by a.product_id;