SQL<2021年11月每天新用户的次日留存率>的两种解法

#问题
题目：统计2021年11月每天新用户的次日留存率（保留2位小数）
https://www.nowcoder.com/share/jump/7965046151741018052948
特殊限制：
- 2021年11月 A
- 每天新用户 B
- 保留两位小数 C
- 跨天标记活跃 D
- 结果日期升序 E
- 无新用户不输出 F

#解法A
1. 每天新增的用户首次和下次的日期记录。输出首次、下次用户记录。**sub1** *A*
	取最小范围数，可以用rank、group by[?链接](https://bluish.net/archives/2150/#menu_index_42)
	通过排序窗口函数，筛选num<=2。
2. 判断当天新增用户的次日是否留存。输出首日、是否留存。**sub2** *B、D（在上个步骤也输出out_time，比较in_time和out_time判断跨日）*
	通过date_add判断，标记if_stay
3. 计算每日新增用户的留存率。*C、E、F*
	按date group by，计算留存率。

<pre>
select
date_in_time dt,
round(sum(if_stay)/count(*),2) v_left_rate
from(
    select
    uid,
    date_in_time,
    case when
    lead(date_in_time)over(partition by uid order by date_in_time)=date_add(date_in_time,interval 1 day)
    or date_out_time=date_add(date_in_time,interval 1 day)
    then 1
    else 0
    end if_stay,
    num
    from (
        select
        distinct
        uid,
        date(in_time) date_in_time,
        date(out_time) date_out_time,
        dense_rank()over(partition by uid order by date(in_time)) num
        from tb_user_log
    )sub1
    where year(date_in_time)=2021 and month(date_in_time)=11 and num<=2
    order by date_in_time
    ) sub2
where num=1
group by date_in_time
having count(*)<>0
order by date_in_time
</pre>

#解法B

1. UNION联合活跃日（包括in_time日与out_time跨日）*D*
2. 找出新用户首日。
3. Left Join连接表，and条件筛选留存。得到首日、活跃日的联合数据，将首日除以活跃日，得到次日活跃度。*A、B、C、E、F*

<pre>
select
earliest_date,
round(count(active_date)/count(earliest_date),2) uv_left_rate
from (
	select 
	uid,
	min(date(in_time)) earliest_date
	from tb_user_log
	group by uid
	) earliest
left join(
    select
    uid,
    date(in_time) active_date
    from tb_user_log
    union
    select
    uid,
    date(out_time)
    from tb_user_log active_date
    ) active
on earliest.uid=active.uid
and active.active_date=date_add(earliest.earliest_date,interval 1 day)
where month(earliest.earliest_date)=11
group by earliest_date
</pre>

#区别
解法A的视角是**「用户的下一次行为是否满足次日留存」**，属于「从用户行为序列中找规律」。

解法B的视角是**「用户的活跃日期是否包含次日的记录」**，通过 LEFT JOIN 将用户首日与次日活跃日期关联，属于「从日期维度反向匹配」。

SQL<2021年11月每天新用户的次日留存率>的两种解法

添加新评论

最新文章

最近回复

分类

归档

其它