题解 | #2021年11月每天新用户的次日留存率#

2021年11月每天新用户的次日留存率

https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450












with a1 as(
    select uid, date(min(in_time)) as dt1
    from tb_user_log
    group by uid
),
a2 as (
    select uid, date(in_time) as dt
    from tb_user_log
),
a3 as (
    select uid, date(out_time) as dt
    from tb_user_log
),
a4 as (
    select uid, dt
    from a2
    union
    select uid, dt
    from a3
),
a5 as (
    select uid, date_sub(dt, interval 1 day) as dt, uid*2 as num
    from a4
),
a6 as (
    select a1.dt1 as dt, round(sum(case when num is not null then 1 else 0 end)/count(*),2) as uv_left_rate
    from a5 right join a1 on a1.uid = a5.uid and a1.dt1 = a5.dt
    group by a1.dt1
)

select dt, uv_left_rate
from a6
where dt>= '2021-11-01' and dt<='2021-11-30'
order by dt








    

全部评论

相关推荐

不愿透露姓名的神秘牛友
06-11 13:34
offe从四面八方来:我真的没时间陪你闹了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务