题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

/*
思路：连续登录问题：
1.统计连续2天及以上
2.在该店铺购物的用户
3.及其对应的次数
4.若有多个用户，按user_id升序排序
 */
题解：
select
    user_id,
    count(1) as days_count
from
    (
        select
            user_id,
            sales_date,
            dense_rank() over (partition by user_id order by sales_date) as dn
        from
            (
                select
                    user_id,
                    sales_date
                from
                    sales_tb
                group by
                    user_id,
                    sales_date
            ) a
    ) aa
group by
    user_id,
    (sales_date - dn)
having
    days_count >= 2
order by user_id