牛客681091348号

分类

/ 注册

全部文章（共8篇）

题解 | #牛客直播各科目同时在线人数#

SELECT tmp2.course_id,course_name,max(cnt) as max_num FROM ( SELECT course_id, user_id, tms, status, SUM(status) over(partition BY course_id O...

2023-02-21

0 277

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

SELECT artical_id, max(cnt) as max_uv FROM ( SELECT artical_id, uid, tms, status, SUM(status) over(partition BY artical_id ORDER BY tms ASC,st...

2023-02-21

0 277

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

SELECT author, DATE_FORMAT(start_time,'%Y-%m') as month, FORMAT(SUM(CASE WHEN if_follow = 2 then -1 else if_follow end)/count(*),3) as fans_growth_rat...

2023-02-20

0 266

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

SELECT tag, sum(if_retweet) as retweet_cut, FORMAT(sum(if_retweet)/count(*),3) AS retweet_rate FROM tb_user_video_log a join tb_video_info b ON a.vide...

2023-02-20

0 224

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

SELECT tag, concat(FORMAT(avg(case when timestampdiff(second,start_time,end_time) >=duration then 100 else (timestampdiff(second,start_time,end_tim...

2023-02-20

0 233

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

Select b.video_id, FORMAT(sum(case when timestampdiff(second,start_time,end_time) >=duration then 1 else 0 end)/count(*),3) as avg_comp_play_rate ...

2023-02-20

0 240

#统计2021年10月每个退货率不大于0.5的商品各项指标#

SELECT product_id, FORMAT(IFNULL(SUM(if_click)/NULLIF(COUNT(*),0),0),3) as ctr, FORMAT(IFNULL(SUM(if_cart)/NULLIF(SUM(if_click),0),0),3) as cart_rate,...

2023-02-10

2 982

题解 | #计算商城中2021年每月的GMV#

SELECT DATE_FORMAT(event_time,"%Y-%m") as month, sum(case status when '0' THEN total_amount when '1' THEN total_amount ELSE 0 END) as GMV FROM tb_orde...

2023-02-10

13 925