目录
一、力扣原题链接
二、题目描述
表:
Subscriptions+-------------+------+ | Column Name | Type | +-------------+------+ | account_id | int | | start_date | date | | end_date | date | +-------------+------+ account_id 是此表的主键列。 此表的每一行都表示帐户订阅的开始和结束日期。 请注意，始终开始日期 < 结束日期。表:
Streams+-------------+------+ | Column Name | Type | +-------------+------+ | session_id | int | | account_id | int | | stream_date | date | +-------------+------+ session_id是该表的主键列。 account_id是订阅表中的外键。 此表的每一行都包含与会话相关联的帐户和日期的信息。编写SQL查询以报告在
2021购买订阅但没有任何会话的帐 户数。
查询结果格式如下例所示。示例1：
输入: Subscriptions table: +------------+------------+------------+ | account_id | start_date | end_date | +------------+------------+------------+ | 9 | 2020-02-18 | 2021-10-30 | | 3 | 2021-09-21 | 2021-11-13 | | 11 | 2020-02-28 | 2020-08-18 | | 13 | 2021-04-20 | 2021-09-22 | | 4 | 2020-10-26 | 2021-05-08 | | 5 | 2020-09-11 | 2021-01-17 | +------------+------------+------------+ Streams table: +------------+------------+-------------+ | session_id | account_id | stream_date | +------------+------------+-------------+ | 14 | 9 | 2020-05-16 | | 16 | 3 | 2021-10-27 | | 18 | 11 | 2020-04-29 | | 17 | 13 | 2021-08-08 | | 19 | 4 | 2020-12-31 | | 13 | 5 | 2021-01-05 | +------------+------------+-------------+ 输出: +----------------+ | accounts_count | +----------------+ | 2 | +----------------+ 解释：用户 4 和 9 在 2021 没有会话。 用户 11 在 2021 没有订阅。
三、建表语句
drop table if exists Subscriptions;
drop table if exists Streams;
Create table If Not Exists Subscriptions (account_id int, start_date date, end_date date);
Create table If Not Exists Streams (session_id int, account_id int, stream_date date);
Truncate table Subscriptions;
insert into Subscriptions (account_id, start_date, end_date) values ('9', '2020-02-18', '2021-10-30');
insert into Subscriptions (account_id, start_date, end_date) values ('3', '2021-09-21', '2021-11-13');
insert into Subscriptions (account_id, start_date, end_date) values ('11', '2020-02-28', '2020-08-18');
insert into Subscriptions (account_id, start_date, end_date) values ('13', '2021-04-20', '2021-09-22');
insert into Subscriptions (account_id, start_date, end_date) values ('4', '2020-10-26', '2021-05-08');
insert into Subscriptions (account_id, start_date, end_date) values ('5', '2020-09-11', '2021-01-17');
Truncate table Streams;
insert into Streams (session_id, account_id, stream_date) values ('14', '9', '2020-05-16');
insert into Streams (session_id, account_id, stream_date) values ('16', '3', '2021-10-27');
insert into Streams (session_id, account_id, stream_date) values ('18', '11', '2020-04-29');
insert into Streams (session_id, account_id, stream_date) values ('17', '13', '2021-08-08');
insert into Streams (session_id, account_id, stream_date) values ('19', '4', '2020-12-31');
insert into Streams (session_id, account_id, stream_date) values ('13', '5', '2021-01-05');
四、题目分析
需求：
在 2021 购买订阅但没有任何会话的帐户数
解题：
1、在2021订阅中
购买订阅的日期是一个时间段，中间有可能跨年，先提取开始和结束的年，用递归列出所有的年份，筛选出2021年的账户
2、在2021没有任何会话
子查询not in查询会话表中在2021的用户就是在2021年没有会话的用户
3、最后通过筛选出来的数据统计即可
五、SQL解答
六、最终答案
with recursive t1 as (
select account_id,year(start_date)as `year` from Subscriptions
union all
select t1.account_id,year + 1
from t1
join Subscriptions s1
on t1.account_id = s1.account_id
and t1.year < year(s1.end_date)
)
select
count(distinct account_id) as accounts_count
from t1
-- 在2021年订阅表中
where t1.year = 2021
-- 不再在2021年消费表中
and account_id not in (select account_id from Streams where year(stream_date) = 2021)七、验证
