某度信息流（用户增长场景）

2021年11月每天的人均浏览文章时长

with t1 as (select uid,date(in_time) as dt,timestampdiff(second,in_time,out_time) as time
from tb_user_log
where in_time like'2021-11%' and artical_id!=0)

select dt,round(sum(time)/(count(distinct(uid))),1) as avg_viiew_len_sec 
from t1
group by dt
order by avg_viiew_len_sec

每篇文章同一时刻最大在看人数

思路：

①将用户的进入时间单独拎出来，同时记为1；离开时间单独拎出来，同时记为-1，这样就聚合这两个表，按照时间排序，意思就是：进去一个加1，离开一个减1。

②然后利用窗口函数对计数（1或者-1）求累计和，因为题目规定：同一时间有就有出的话先算进来的后算出去的，所以排序的时候就要看好了先按时间排序，再按计数排序！

③然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了！

select artical_id,max(cnt)as max_uv
from(
select artical_id,sum(num)over(partition by artical_id order by dt asc,num desc )as cnt from (
select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all
(select artical_id,out_time dt,-1 num from tb_user_log
where artical_id !=0)) a
)b
group by artical_id
order by max_uv desc

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

思路：

1. 建立临时表t1，按照用户分组找出每个用户的最早登入时间；
2. 建立临时表t2，按照用户分组找出每个用户的活跃期间，对登入和登出时间进行union all
3. 建立临时表t3，将t1和t2进行左连接，条件是用户ID相等并且t1的时间=t2的时间-1
4. 按照时间进行分组，计算留存率

   with t1 as(select uid,min(date(in_time)) as dt
   from tb_user_log 
   group by uid
   ),
   t2 as (select uid,date(in_time) as dt
   from tb_user_log
   union
   (select uid,date(out_time) as dt
   from tb_user_log
   order by uid))
   ,t3 as(select t1.uid as uid1,t1.dt as dt1, t2.uid as uid2,t2.dt as dt2
   from t1 left join t2 
   on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,interval 1 day))
   
   select * from t3
   select dt1,round(count(uid2)/count(uid1),2) as uv_left_rate
   from t3
   where dt1 like'2021-11%'
   group by dt1

   

统计活跃间隔对用户等级的结果 

思路：

1. 建立临时表t1找出每个用户的最早登入时间和最晚登出时间
2. 建立临时表t2计算每个用户最早登入时间距今天的天数val1，最晚登出时间距今天的天数val2
3. 建立临时表t3对用户进行评级
4. 计算比率

   with t1 as (select uid,date(min(in_time))as first_time ,date(max(out_time))as dt from tb_user_log
   group by uid)
   ,t2 as (select uid,
   datediff((select date(max(out_time)) from tb_user_log),first_time) as val1,datediff((select date(max(out_time)) from tb_user_log),dt) as val2 from t1)
   ,t3 as (select uid,(case when val1=val2 and val1<7 then '新晋用户'
   when val1>=7 and val2<7 then '忠实用户'
   when val2>=30 then '流失用户' else '沉睡用户' end) as user_grade from t2)
   
   select user_grade,round(count(*)/(select count(*)from t3),2) as ratio
   from t3
   group by user_grade
   order by ratio desc
   
   
   
   
   

   每天的日活数及新用户占比

思路：

1. 建立临时表t1，找出每个用户的最早登陆时间
2. 建立临时表t2，找出每个用户的活跃时间
3. 建立临时表t3，按照日期进行分组找到每天的日活数
4. 建立临时表t4，按照首次登陆时间进行分组找到每天的新用户数据
5. 计算每天的新用户占比

   with t1 as(select uid,min(date(in_time)) as first_time from tb_user_log
   group by uid)
   ,t2 as(select distinct uid,dt from(
   select uid,date(in_time) as dt from tb_user_log
   union all
   (select uid,date(out_time)as dt from tb_user_log)) a)
   ,t3 as(select dt,count(uid) as dau from t2
   group by dt)
   ,t4 as(select first_time,count(uid) as new_count from t1
   group by 1)
   
   
   select t3.dt,t3.dau,ifnull((round((t4.new_count/t3.dau),2)),0.00) as uv_new_ratio
   from t3 left join t4
   on t3.dt=t4.first_time
   order by dt

   连续签到领金币

思路：见连续问题解题思路-CSDN博客