问题

题目：统计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?链接
   通过排序窗口函数，筛选num<=2。
2. 判断当天新增用户的次日是否留存。输出首日、是否留存。sub2 B、D（在上个步骤也输出out_time，比较in_time和out_time判断跨日）
   通过date_add判断，标记if_stay
3. 计算每日新增用户的留存率。C、E、F
   按date group by，计算留存率。

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

解法B

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

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

区别

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

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