留存率、topN、最大连续登录、最大同时在线人数

以下内容参考理解与计算APP留存率：案例分析,-CSDN博客以及小红书上的学习整理

1.留存率

1.1 什么是留存率

留存用户是指用户在APP产生行为后，在固定的第N日继续访问或使用APP的用户。留存率是指用户在一段时间后或固定的间隔后产生留存用户的比例。

用户留存率有很多种：新客留存率、老客留存率、活跃用户留存率、购买留存率、或者某个功能使用用户的留存率等。

通常计算的时间间隔为次日、3日、7日、30日、60日，根据业务不同需求可以选择不同的计算方式以及时间间隔，重点是要和产品负责人以及运营人员对好指标口径。

公式：N日新客留存率=某日的新注册用户在第N日登录APP的用户数 / 某日的新注册用户总数

ps: 用户id需要去重

1.2 案例分析

方法：取出注册日期和登录日期，计算出日期差，然后用count , case when 计算留存

1.2.1 有两张表：注册表和登录表

-- 计算注册和登录的日期差
with diff_info as (
select 
    r.user_id
    ,date(r.register_time) as rd
    ,date(l.login_time) as ld
    ,datediff(l.login_time,r.register_time) as diff_num
from register_info as r
left join login_info as l
on r.user_id = l.user_id
-- 下面这个代码非必须
and date(l.login_time) between date(r.register_time) + interval 1 day and date(r.register_time) + interval 7 day
)

-- 开始计算留存率
select 
    rd
    ,count(distinct user_id) as DAU
    ,round(count(distinct case when diff_num = 1 then user_id else NULL end)/count(distinct user_id),2) as "次日留存率"
    ,round(count(distinct case when diff_num = 3 then user_id else NULL end)/count(distinct user_id),2) as "3日留存率"
    ,round(count(distinct case when diff_num = 7 then user_id else NULL end)/count(distinct user_id),2) as "7日留存率"

from diff_info
group by 1
order by 1

1.2.2 有一张表：登录表

此时的注册日期即：最小的登录日期，用min() over(partition by )取出，其他的类似2.1

-- 计算注册和登录的日期差
with diff_info as (
select 
    user_id
    ,rd
    ,ld
    ,datediff(ld,rd) as diff_num
from(
    select 
        user_id
        ,date(login_time) as ld
        ,min(date(login_time)) over(partition by user_id) as rd
    from login_info
    )a
)

-- 开始计算留存率
select 
    rd
    ,count(distinct user_id) as DAU
    ,round(count(distinct case when diff_num = 1 then user_id else NULL end)/count(distinct user_id),2) as "次日留存率"
    ,round(count(distinct case when diff_num = 3 then user_id else NULL end)/count(distinct user_id),2) as "3日留存率"
    ,round(count(distinct case when diff_num = 7 then user_id else NULL end)/count(distinct user_id),2) as "7日留存率"

from diff_info
group by 1
order by 1

2.top N问题

方法：先用with as 取出我们想要的数，然后用order by + limit   或者 窗口函数( rank() 、dense_rank() 、row_number() ) + 范围收束

窗口函数的区别

函数	排名结果	说明
rank	1,2,3,3,5,6	注意没有4和8
dense_rank	1,2,3,3,4,5	注意有4
row_number	1,2,3,4,5,6	注意对于每一个group是没有重复排名的

一些其他的窗口函数

 

2.1案例分析 

零食类商品中复购率top3高的商品_牛客题霸_牛客网

该题思路：with as +order by+limit

每个月Top3的周杰伦歌曲_牛客题霸_牛客网

该题思路:with as +row_number+范围收束

3.最大连续登录问题

方法：对用户分组，使用row_number添加序号，然后用登录日期与之相减，再对差值分组，count一下即为连续登录日期（即对于某个用户，差值相同的即为连续登录）；为了代码的可读性，可以用with as

ps：注意开始和最后要去重

3.1案例分析 

最长连续登录天数_牛客题霸_牛客网

-- 计算差值
with diff_info as(
select 
    user_id
    ,subdate(fdate,r) as diff_num
from
(select distinct *
        ,row_number() over (partition by user_id order by fdate) as r
    from tb_dau
    where fdate between "2023-01-01" and "2023-01-31"
)t
)


select distinct user_id
    ,max(consec_days) as max_consec_days
from(
    select 
    user_id
    ,diff_num
    ,count(diff_num) as consec_days  -- 计算连续登录天数
from diff_info
group by 1,2
)a
group by 1

同样的可以按照以上思路，解决下题：某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级_牛客题霸_牛客网

4.最大同时在线人数

方法：直播时，将用户进入直播间记uv为+1，退出直播间记uv为-1，此处用到union all；之后对uv用窗口函数求和得到瞬时的同时在线人数，最后求max即可

思路分析

下图来自 牛客解题大佬，清晰易懂

4.1案例分析 

牛客直播各科目同时在线人数_牛客题霸_牛客网

-- 进入+1,离开-1
with uv_info as(
    select 
        user_id
        ,course_id
        ,in_datetime as time
        ,1 as uv
    from attend_tb
    union all
    select 
        user_id
        ,course_id
        ,out_datetime as time
        ,-1 as uv
    from attend_tb
)

select 
    course_id
    ,course_name
    ,max(uv_cnt) as max_num
from(select 
    c.course_id
    ,c.course_name
    ,sum(uv) over(partition by course_id order by time) as uv_cnt  -- 瞬时 同时在线人数
from course_tb c
left join uv_info a
on a.course_id = c.course_id)t
group by 1,2