tujin

分类

/ 注册

全部文章（共25篇）

题解 | #每天的日活数及新用户占比#

select t1.dt,user_cnt as dau,cast(if(new_user_cnt is null,0,new_user_cnt)/user_cnt as decimal(10,2)) as uv_new_ratio from ( select dt,count(uid) user...

2023-10-05

0 290

题解 | #2021年11月每天新用户的次日留存率#

# 知道第二天得到的数据后，对其进行计算次日留存率，根据日期分组 SELECT t1.dt,ROUND(COUNT(t2.uid)/COUNT(t1.uid),2) uv_rate FROM # 这道题要的是新用户的留存，所以首先要查出新用户,查新用户的方法，查找最小登录时间 (SELECT ui...

2023-10-04

0 236

题解 | #每篇文章同一时刻最大在看人数#

WITH temp AS ( # 记录进入，记为1 SELECT uid,artical_id,in_time line_time,1 uv FROM tb_user_log WHERE artical_id<>0 UNION ALL # 记录退出，记为-...

2023-10-04

0 219

题解 | #2021年11月每天的人均浏览文章时长#

WITH temp AS ( SELECT uid,substr(in_time,1,10) dt,artical_id,in_time,out_time FROM tb_user_log WHERE artical_id<>0 and year(in_time)...

2023-10-04

0 209

题解 | #近一个月发布的视频中热度最高的top3视频#

# SELECT # video_id, # ROUND((100 * finished_rate # + 5 * like_cnt # + 3 * comment_count # + 2 * retweet_cnt) / (unfinished_day_cnt + ...

2023-10-04

0 279

题解 | #国庆期间每类视频点赞量和转发量#

with temp as ( select tag,substr(end_time,1,10) as dt,start_time,if_like,if_retweet from tb_user_video_log tuvl left join tb_video_info tv...

2023-10-04

0 383

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

with temp as ( select author,substr(end_time,1,7) as month_id,if_follow from tb_user_video_log tuvl left join tb_video_info tvi on tuvl....

2023-10-04

0 249

题解 | #每类视频近一个月的转发量/率#

select tag,sum(if(if_retweet=1,1,0)) as retweet_cut, cast(sum(if(if_retweet=1,1,0))/count(*) as decimal(10,3)) as retweet_rate from tb_user_video...

2023-10-04

0 290

题解 | #平均播放进度大于60%的视频类别#

select tag,concat(cast(avg_play_progress*100 as decimal(5,2)),'%') as avg_play_progress from ( select tag, cast(avg(if(timestampdiff(second,start_time...

2023-10-04

0 276

题解 | #各个视频的平均完播率#

select tuvl.video_id, cast(avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as decimal(10,3)) as avg_comp_play_rate from tb_user_v...

2023-10-04

0 276