SQL案例 - 最长连续登录天数

描述

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”

登陆表 tb_dau：

fdate	user_id
2023-01-01	10000
2023-01-02	10000
2023-01-04	10000

输出：

user_id	max_consec_days
10000	2

示例1

输入：

drop table if exists tb_dau;
create table `tb_dau` (
    `fdate` date,
    `user_id` int
);
insert into tb_dau(fdate, user_id)
values 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-04', 10000);

输出：

user_id|max_consec_days
10000|2

说明：

id为10000的用户在1月1日及1月2日连续登录2日，1月4日登录1日，故最长连续登录天数为2日

答案

解法1：

with
    tb1 as (
        select
            user_id,
            to_days (fdate) - row_number() over (partition by user_id order by fdate) grp
        from
            tb_dau
        where
            fdate between '2023-1-1' and '2023-1-31'
    )
select
    user_id,
    max(nbr) max_consec_days
from
    (
        select 
            user_id,
            grp,
            count(*) nbr
        from
            tb1
        group by
            user_id,
            grp
    ) t
group by
    user_id;

*本案例来自牛客网，但答案为原创，如有雷同纯属巧合*