面试sql系列:(四)留存率

select statdate,
    day1uv as `当日新增用户数`,
    concat(round(day2uv / day1uv *100,2),'%') as `次日留存率`,
    concat(round(day7uv / day1uv *100,2),'%') as `7日留存率`,
    concat(round(day15uv / day1uv *100,2),'%') as `15日留存率`,
    concat(round(day20uv / day1uv *100,2),'%') as `20日留存率`,
    concat(round(day30uv / day1uv *100,2),'%') as `30日留存率`
from(
  SELECT regexp_replace(to_date(create_time),'-','') as statdate, 
      count(if(date_time = date_add(create_time,0),t1.user_id,null)) day1uv,
      count(if(date_time = date_add(create_time,1),t1.user_id,null)) day2uv,
      count(if(date_time = date_add(create_time,2),t1.user_id,null)) day3uv,
      count(if(date_time = date_add(create_time,3),t1.user_id,null)) day4uv,
      count(if(date_time = date_add(create_time,4),t1.user_id,null)) day5uv,
      count(if(date_time = date_add(create_time,5),t1.user_id,null)) day6uv,
      count(if(date_time = date_add(create_time,6),t1.user_id,null)) day7uv,
      count(if(date_time = date_add(create_time,7),t1.user_id,null)) day8uv,
      count(if(date_time = date_add(create_time,8),t1.user_id,null)) day9uv,
      count(if(date_time = date_add(create_time,9),t1.user_id,null)) day10uv,
      count(if(date_time = date_add(create_time,10),t1.user_id,null)) day11uv,
      count(if(date_time = date_add(create_time,11),t1.user_id,null)) day12uv,
      count(if(date_time = date_add(create_time,12),t1.user_id,null)) day13uv,
      count(if(date_time = date_add(create_time,13),t1.user_id,null)) day14uv,
      count(if(date_time = date_add(create_time,14),t1.user_id,null)) day15uv,
      count(if(date_time = date_add(create_time,15),t1.user_id,null)) day16uv,
      count(if(date_time = date_add(create_time,16),t1.user_id,null)) day17uv,
      count(if(date_time = date_add(create_time,17),t1.user_id,null)) day18uv,    
      count(if(date_time = date_add(create_time,18),t1.user_id,null)) day19uv,
      count(if(date_time = date_add(create_time,19),t1.user_id,null)) day20uv,
      count(if(date_time = date_add(create_time,20),t1.user_id,null)) day21uv,
      count(if(date_time = date_add(create_time,21),t1.user_id,null)) day22uv,
      count(if(date_time = date_add(create_time,22),t1.user_id,null)) day23uv,
      count(if(date_time = date_add(create_time,23),t1.user_id,null)) day24uv,
      count(if(date_time = date_add(create_time,24),t1.user_id,null)) day25uv,
      count(if(date_time = date_add(create_time,25),t1.user_id,null)) day26uv,
      count(if(date_time = date_add(create_time,26),t1.user_id,null)) day27uv,
      count(if(date_time = date_add(create_time,27),t1.user_id,null)) day28uv,
      count(if(date_time = date_add(create_time,28),t1.user_id,null)) day29uv,
      count(if(date_time = date_add(create_time,29),t1.user_id,null)) day30uv,
      count(if(date_time = date_add(create_time,30),t1.user_id,null)) day31uv
  from(
    -- 用户注册表
    SELECT to_date(createTime) as create_time,
        userid as user_id
    from tmp_user_table
    where regexp_replace(to_date(createTime),'-','') >= '20210601' 
      and regexp_replace(to_date(createTime),'-','') <= '20210701'
  )t1 join(
    -- 用户日志表
    select DISTINCT from_unixtime(unix_timestamp(dt,'yyyymmdd'),'yyyy-mm-dd') as date_time,
        user_id
    from tmp_app_log_table
    where dt >= '20210601' and dt <= '20210701'
  )t2 on t1.user_id = t2.user_id
  group by create_time 
)result
order by statdate desc
limit 100000