会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

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

with a as( select date(in_time) as dt,uid,artical_id from tb_user_log union select date(out_time) as dt,uid,artical_id from tb_user_log ), b as( #新...

2025-04-19

0 33

题解 | 统计活跃间隔对用户分级结果

#新晋 with xinjin as( select uid,min(date(in_time)) as first_date from tb_user_log group by uid having first_date>=date_sub( (select max(date(in...

2025-04-18

0 29

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

select t1.first_date , round(count(distinct t2.uid)/count(distinct t1.uid),2) as uv_left_rate from ( select uid, min(date(in_time)) as first_date ...

2025-04-18

0 29

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

with a as( select artical_id, in_time dt,1 as type from tb_user_log where artical_id !=0 union all select artical_id, out_time d...

2025-04-16

0 30

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

select date(in_time) as dt, round(sum(timestampdiff(second,in_time,out_time))/count( distinct uid),1) as avg_viiew_len_sec from tb_user_log where subs...

2025-04-16

0 21

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

select video_id, cast( (100*wanbo+5*dianzan+3*pinglun+2*zhuanfa)*xinxiandu as signed) as hot_index from( select tv.video_id, avg(timestampdiff(seco...

2025-04-15

0 21

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

select * from (select tv.tag, date_format(tu.start_time,'%Y-%m-%d') as dt, sum(sum(tu.if_like)) over(partition by tv.tag order by date_format(tu.start...

2025-04-14

0 24

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

select tv.author,substr(tu.start_time,1,7) as month, round(sum(case when if_follow =1 then 1 when if_follow=2 then -1 else 0 end)/count(*),3) as fans...

2025-04-14

0 21

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

select tv.tag, count(case when tu.if_retweet =1 then 1 else null end) retweet_cnt, round(count(case when tu.if_retweet =1 then 1 else null end...

2025-04-14

0 31

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

select tag, concat(round(avg(case when timestampdiff(second,tu.start_time,tu.end_time)>tv.duration then 1 else timestampdiff(second,tu.start_time,t...

2025-04-13

0 17