sql子查询超经典例题：--连续3天登录的用户筛选

create table user_login(
    user_id number(8),
    login_time varchar2(50)
);

insert into user_login values (1,'2022-06-01 11:00:00');
insert into user_login values (1,'2022-06-01 12:00:00');
insert into user_login values (1,'2022-06-01 12:00:00');
insert into user_login values (1,'2022-06-02 11:00:00');
insert into user_login values (1,'2022-06-03 11:00:00');
insert into user_login values (2,'2022-06-01 11:00:00');
insert into user_login values (2,'2022-06-02 11:00:00');
insert into user_login values (2,'2022-06-04 11:00:00');
insert into user_login values (3,'2022-06-01 11:00:00');
insert into user_login values (3,'2022-06-02 11:00:00');
insert into user_login values (3,'2022-06-04 11:00:00');
insert into user_login values (3,'2022-06-05 11:00:00');
insert into user_login values (3,'2022-06-06 11:00:00');
insert into user_login values (3,'2022-06-07 11:00:00');
insert into user_login values (3,'2022-06-08 11:00:00');
select * from user_login;

思路:
1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。
2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登录日期进行排序。
3、计算登录日期减去第二步骤得到的结果值，用户连续登录情况下，相减的结果都相同。
4、按照id和日期分组并计数，筛选大于等于3的即为连续3天登录的用户。

select t2.userid from
(select t1.user_id userid,t1.ymd,t1.ymd-t1.rn plus from 
(select user_id,
       trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd') ymd,
       row_number()over(partition by user_id order by trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) rn
from user_login group by user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) t1) t2
group by t2.userid,t2.plus
having count(1)>=3

1.按照用户和年月日进行分组，保证每个用户，每天的记录只有一条:
select user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd') ymd       
from user_login group by user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')
order by user_id;

2.上一步的结果，按照用户分组，并在组内给每一条记录编号
select user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd') ymd,
    row_number()over(partition by user_id order by trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) rn
from user_login group by user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')

3.ymd-rn
select t1.user_id,t1.ymd,t1.rn,t1.ymd-t1.rn plus_date from 
(select user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd') ymd,
    row_number()over(partition by user_id order by trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) rn
from user_login group by user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) t1

4.按照user_id和减法之后的日期plus_date两项分组
select t2.user_id,t2.plus_date,count(1)    from 
(select t1.user_id,t1.ymd,t1.rn,t1.ymd-t1.rn plus_date from 
(select user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd') ymd,
    row_number()over(partition by user_id order by trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) rn
from user_login group by user_id,trunc(to_date(login_time,'yyyy-mm-dd hh:mi:ss'),'dd')) t1) t2
group by t2.user_id,t2.plus_date
having count(1)>=3