小红书连续登录天数指标计算sql

登录天数

连续登录天数

发布笔记数量

笔记被观看次数

最受欢迎的笔记

在小红书的搜索次数

user_online_log

user_id  day

user1  20200101

user1  20200101

user1  20200101

user2  20200101

user2  20200102

--登录天数

select user_id

      ,count(*) as cnt--数据条数

from(

      select distinct user_id,--登录天数

            day

        from user_online_log

        where day>='20200101'

        and day<='20201231'

) a group by user_id;

--连续登录天数

select user_id

      --取出连续登录的最大次数

      ,max(continue_day) as continue_day

  from (select user_id --取出连续登录的次数

              ,day

              ,count(*) as continue_day

          from (select

                user_id

                --登录时间减去排序值，连续登录天数的结果会一样

                ,day - rank_num as day

                from (select --根据用户id分区、登录时间排序

                        row_number() over(partition by user_id order by day desc) as rank_num

                        ,day

                        ,user_id

                          from (

                                --登录天数去重

                                select distinct user_id, day

                                  from user_online_log

                                ) a

                        ) a

                ) a

        group by user_id, day

        ) a

group by user_id