面试：留存率

象象向前冲
295 阅读10分钟 
--留存率
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_date),'-','') as statdate, 
      count(if(logon_date = date_add(create_date,0),t1.id,null)) day1uv,
      count(if(logon_date = date_add(create_date,1),t1.id,null)) day2uv,
      count(if(logon_date = date_add(create_date,2),t1.id,null)) day3uv,
      count(if(logon_date = date_add(create_date,3),t1.id,null)) day4uv,
      count(if(logon_date = date_add(create_date,4),t1.id,null)) day5uv,
      count(if(logon_date = date_add(create_date,5),t1.id,null)) day6uv,
      count(if(logon_date = date_add(create_date,6),t1.id,null)) day7uv,
      count(if(logon_date = date_add(create_date,7),t1.id,null)) day8uv,
      count(if(logon_date = date_add(create_date,8),t1.id,null)) day9uv,
      count(if(logon_date = date_add(create_date,9),t1.id,null)) day10uv,
      count(if(logon_date = date_add(create_date,10),t1.id,null)) day11uv,
      count(if(logon_date = date_add(create_date,11),t1.id,null)) day12uv,
      count(if(logon_date = date_add(create_date,12),t1.id,null)) day13uv,
      count(if(logon_date = date_add(create_date,13),t1.id,null)) day14uv,
      count(if(logon_date = date_add(create_date,14),t1.id,null)) day15uv,
      count(if(logon_date = date_add(create_date,15),t1.id,null)) day16uv,
      count(if(logon_date = date_add(create_date,16),t1.id,null)) day17uv,
      count(if(logon_date = date_add(create_date,17),t1.id,null)) day18uv,    
      count(if(logon_date = date_add(create_date,18),t1.id,null)) day19uv,
      count(if(logon_date = date_add(create_date,19),t1.id,null)) day20uv,
      count(if(logon_date = date_add(create_date,20),t1.id,null)) day21uv,
      count(if(logon_date = date_add(create_date,21),t1.id,null)) day22uv,
      count(if(logon_date = date_add(create_date,22),t1.id,null)) day23uv,
      count(if(logon_date = date_add(create_date,23),t1.id,null)) day24uv,
      count(if(logon_date = date_add(create_date,24),t1.id,null)) day25uv,
      count(if(logon_date = date_add(create_date,25),t1.id,null)) day26uv,
      count(if(logon_date = date_add(create_date,26),t1.id,null)) day27uv,
      count(if(logon_date = date_add(create_date,27),t1.id,null)) day28uv,
      count(if(logon_date = date_add(create_date,28),t1.id,null)) day29uv,
      count(if(logon_date = date_add(create_date,29),t1.id,null)) day30uv,
      count(if(logon_date = date_add(create_date,30),t1.id,null)) day31uv
  from(
    -- 用户注册表
    SELECT to_date(create_date) as create_date,
        id 
    from tmp.register
    where to_date(create_date) >= '2021-07-01' 
      and to_date(create_date)<= '2021-07-31'
  )t1 join(
    -- 用户日志表
    select DISTINCT logon_date,
        id
    from tmp.logon
    where logon_date >= '2021-07-01' and logon_date <= '2021-07-31'
  )t2 on t1.id = t2.id
  group by create_date
)result
order by statdate desc
limit 100000

图片

--用户注册数据
create table if not exists tmp.register as
select 1 as id,'2021-07-01' as create_date
UNION all
select 2 as id,'2021-07-01' as create_date
UNION all
select 3 as id,'2021-07-01' as create_date
UNION all
select 4 as id,'2021-07-01' as create_date
UNION all
select 5 as id,'2021-07-01' as create_date
UNION all
select 6 as id,'2021-07-01' as create_date
UNION all
select 7 as id,'2021-07-01' as create_date
UNION all
select 8 as id,'2021-07-01' as create_date
UNION all
select 9 as id,'2021-07-01' as create_date
UNION all
select 10 as id,'2021-07-01' as create_date
--用户登录日志
create table if not exists tmp.logon as
select 1 as id,'2021-07-01' as logon_date
UNION all
select 2 as id,'2021-07-01' as logon_date
UNION all
select 3 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 5 as id,'2021-07-01' as logon_date
UNION all
select 6 as id,'2021-07-01' as logon_date
UNION all
select 7 as id,'2021-07-01' as logon_date
UNION all
select 8 as id,'2021-07-01' as logon_date
UNION all
select 9 as id,'2021-07-01' as logon_date
UNION all
select 10 as id,'2021-07-01' as logon_date
UNION all
select 2 as id,'2021-07-01' as logon_date
UNION all
select 1 as id,'2021-07-02' as logon_date
UNION all
select 2 as id,'2021-07-02' as logon_date
UNION all
select 3 as id,'2021-07-02' as logon_date
UNION all
select 4 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 6 as id,'2021-07-02' as logon_date
UNION all
select 9 as id,'2021-07-02' as logon_date
UNION all
select 9 as id,'2021-07-03' as logon_date
UNION all
select 1 as id,'2021-07-03' as logon_date
UNION all
select 2 as id,'2021-07-03' as logon_date
UNION all
select 2 as id,'2021-07-03' as logon_date
UNION all
select 3 as id,'2021-07-03' as logon_date
UNION all
select 3 as id,'2021-07-03' as logon_date
UNION all
select 4 as id,'2021-07-03' as logon_date
UNION all
select 5 as id,'2021-07-03' as logon_date
UNION all
select 6 as id,'2021-07-03' as logon_date
UNION all
select 7 as id,'2021-07-03' as logon_date
UNION all
select 10 as id,'2021-07-03' as logon_date
UNION all
select 1 as id,'2021-07-04' as logon_date
UNION all
select 2 as id,'2021-07-04' as logon_date
UNION all
select 4 as id,'2021-07-04' as logon_date
UNION all
select 5 as id,'2021-07-04' as logon_date
UNION all
select 6 as id,'2021-07-04' as logon_date
UNION all
select 7 as id,'2021-07-04' as logon_date
UNION all
select 8 as id,'2021-07-04' as logon_date
UNION all
select 9 as id,'2021-07-04' as logon_date
UNION all
select 10 as id,'2021-07-04' as logon_date
UNION all
select 1 as id,'2021-07-05' as logon_date
UNION all
select 2 as id,'2021-07-05' as logon_date
UNION all
select 3 as id,'2021-07-05' as logon_date
UNION all
select 4 as id,'2021-07-05' as logon_date
UNION all
select 5 as id,'2021-07-05' as logon_date
UNION all
select 6 as id,'2021-07-05' as logon_date
UNION all
select 7 as id,'2021-07-05' as logon_date
UNION all
select 8 as id,'2021-07-05' as logon_date
UNION all
select 9 as id,'2021-07-05' as logon_date
UNION all
select 10 as id,'2021-07-05' as logon_date
UNION all
select 1 as id,'2021-07-06' as logon_date
UNION all
select 2 as id,'2021-07-06' as logon_date
UNION all
select 3 as id,'2021-07-06' as logon_date
UNION all
select 4 as id,'2021-07-06' as logon_date
UNION all
select 6 as id,'2021-07-06' as logon_date
UNION all
select 7 as id,'2021-07-06' as logon_date
UNION all
select 8 as id,'2021-07-06' as logon_date
UNION all
select 9 as id,'2021-07-06' as logon_date
UNION all
select 1 as id,'2021-07-07' as logon_date
UNION all
select 4 as id,'2021-07-07' as logon_date
UNION all
select 3 as id,'2021-07-07' as logon_date
UNION all
select 5 as id,'2021-07-07' as logon_date
UNION all
select 6 as id,'2021-07-07' as logon_date
UNION all
select 7 as id,'2021-07-07' as logon_date
UNION all
select 8 as id,'2021-07-07' as logon_date
UNION all
select 9 as id,'2021-07-07' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 3 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 4 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 8 as id,'2021-07-08' as logon_date
UNION all
select 6 as id,'2021-07-08' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date
UNION all
select 7 as id,'2021-07-08' as logon_date
UNION all
select 10 as id,'2021-07-08' as logon_date
UNION all
select 3 as id,'2021-07-08' as logon_date
UNION all
select 2 as id,'2021-07-08' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date



--用户登录日志
create table if not exists tmp.logon as
select 1 as id,'2021-07-01' as logon_date
UNION all
select 2 as id,'2021-07-01' as logon_date
UNION all
select 3 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 4 as id,'2021-07-01' as logon_date
UNION all
select 5 as id,'2021-07-01' as logon_date
UNION all
select 6 as id,'2021-07-01' as logon_date
UNION all
select 7 as id,'2021-07-01' as logon_date
UNION all
select 8 as id,'2021-07-01' as logon_date
UNION all
select 9 as id,'2021-07-01' as logon_date
UNION all
select 10 as id,'2021-07-01' as logon_date
UNION all
select 2 as id,'2021-07-01' as logon_date
UNION all
select 1 as id,'2021-07-02' as logon_date
UNION all
select 2 as id,'2021-07-02' as logon_date
UNION all
select 3 as id,'2021-07-02' as logon_date
UNION all
select 4 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 5 as id,'2021-07-02' as logon_date
UNION all
select 6 as id,'2021-07-02' as logon_date
UNION all
select 9 as id,'2021-07-02' as logon_date
UNION all
select 9 as id,'2021-07-03' as logon_date
UNION all
select 1 as id,'2021-07-03' as logon_date
UNION all
select 2 as id,'2021-07-03' as logon_date
UNION all
select 2 as id,'2021-07-03' as logon_date
UNION all
select 3 as id,'2021-07-03' as logon_date
UNION all
select 3 as id,'2021-07-03' as logon_date
UNION all
select 4 as id,'2021-07-03' as logon_date
UNION all
select 5 as id,'2021-07-03' as logon_date
UNION all
select 6 as id,'2021-07-03' as logon_date
UNION all
select 7 as id,'2021-07-03' as logon_date
UNION all
select 10 as id,'2021-07-03' as logon_date
UNION all
select 1 as id,'2021-07-04' as logon_date
UNION all
select 2 as id,'2021-07-04' as logon_date
UNION all
select 4 as id,'2021-07-04' as logon_date
UNION all
select 5 as id,'2021-07-04' as logon_date
UNION all
select 6 as id,'2021-07-04' as logon_date
UNION all
select 7 as id,'2021-07-04' as logon_date
UNION all
select 8 as id,'2021-07-04' as logon_date
UNION all
select 9 as id,'2021-07-04' as logon_date
UNION all
select 10 as id,'2021-07-04' as logon_date
UNION all
select 1 as id,'2021-07-05' as logon_date
UNION all
select 2 as id,'2021-07-05' as logon_date
UNION all
select 3 as id,'2021-07-05' as logon_date
UNION all
select 4 as id,'2021-07-05' as logon_date
UNION all
select 5 as id,'2021-07-05' as logon_date
UNION all
select 6 as id,'2021-07-05' as logon_date
UNION all
select 7 as id,'2021-07-05' as logon_date
UNION all
select 8 as id,'2021-07-05' as logon_date
UNION all
select 9 as id,'2021-07-05' as logon_date
UNION all
select 10 as id,'2021-07-05' as logon_date
UNION all
select 1 as id,'2021-07-06' as logon_date
UNION all
select 2 as id,'2021-07-06' as logon_date
UNION all
select 3 as id,'2021-07-06' as logon_date
UNION all
select 4 as id,'2021-07-06' as logon_date
UNION all
select 6 as id,'2021-07-06' as logon_date
UNION all
select 7 as id,'2021-07-06' as logon_date
UNION all
select 8 as id,'2021-07-06' as logon_date
UNION all
select 9 as id,'2021-07-06' as logon_date
UNION all
select 1 as id,'2021-07-07' as logon_date
UNION all
select 4 as id,'2021-07-07' as logon_date
UNION all
select 3 as id,'2021-07-07' as logon_date
UNION all
select 5 as id,'2021-07-07' as logon_date
UNION all
select 6 as id,'2021-07-07' as logon_date
UNION all
select 7 as id,'2021-07-07' as logon_date
UNION all
select 8 as id,'2021-07-07' as logon_date
UNION all
select 9 as id,'2021-07-07' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 3 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 4 as id,'2021-07-08' as logon_date
UNION all
select 9 as id,'2021-07-08' as logon_date
UNION all
select 8 as id,'2021-07-08' as logon_date
UNION all
select 6 as id,'2021-07-08' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date
UNION all
select 7 as id,'2021-07-08' as logon_date
UNION all
select 10 as id,'2021-07-08' as logon_date
UNION all
select 3 as id,'2021-07-08' as logon_date
UNION all
select 2 as id,'2021-07-08' as logon_date
UNION all
select 1 as id,'2021-07-08' as logon_date
avatar
文章
阅读
粉丝