--求出每日新增用户 with new_user as( select uid,date(min(in_time)) dt from tb_user_log group by uid ), -- 求出全部用户信息 all_user_log as( select uid,date(in_time) dt from tb_user_log union select uid,date(out_time) from tb_user_log ) select all_user_log.dt dt, count(all_user...

with temp as( select date(max(in_time)) first_login_time from tb_user_log ) select user_grade, round(count(1)/(select count(distinct uid) from tb_user_log),2) ratio from( select uid, case when datediff((select * from temp),date(max(in_time)))<7 and datediff((select * fro...

--统计2021年11月每天新用户的次日留存率（保留2位小数） --注： --次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。 --如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。 -- 分析： -- 1、求出每日的新增用户 -- 2、求出用户全量表（如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过） select first_login_time dt, round(count(distinct t2.uid)/count(t1.uid),2) from ( 求...

select artical_id, max(sum_uv) max_uv from( select artical_id, sum(flag)over(partition by artical_id order by td,flag desc) sum_uv from( select artical_id,in_time td,1 flag from tb_user_log where artical_id<>0 union all select artic...

--热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度； --新鲜度=1/(最近无播放天数+1)； --当前配置的参数a,b,c,d分别为100、5、3、2。 --最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。 --结果中热度保留为整数，并按热度降序排序。 -- 需求：找出近一个月发布的视频中热度最高的top3视频 -- 分析： ①近一个月=> datediff((select max(date(end_time)) from tb_user_video_log),date(release_time))<3...

-- 需求： 统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序 -- 统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量 select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d from( -- 1、统计2021年9月25号到10月3号这七天每类视频每天的总点赞量和一周内最大单天转发量 select tag,dt, sum(like_tag_d)over(partition by tag order by dt rows between 6 precedi...

-- ③计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 select author,month,fans_growth_rate, sum(total_fans)over(partition by author order by month) total_fans from( -- ②计算2021年里每个创作者每月的涨粉率及当月的总粉丝量 select author,month, round(sum(total_fans)/sum(cnt),3) fans_growth_rate, sum(total_fans...

-- 题目是：统计每天的日活数及新用户占比 -- 思路：求新用户/日活数 => 就要分别求出新用户和每日活跃用户 -- 新用户 => 首次登入的用户 => 按用户分组求出最小的登入时间 => select uid,min(date(in_time)) new_time from tb_user_log group by uid -- 每日活跃用户（如果in_time-进入时间和ou...