牛客466955756号

分类

/ 注册

全部文章（共29篇）

题解 | 连续签到领金币

select uid,date_format(t,'%Y%m') as month,sum(gc) as coin from (select uid, t, case row_number() over (partition by date_sub(t, interval...

2025-06-03

0 82

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

select a.d, count(a.uid) as dau, round(count(b.uid) / count(a.uid), 2) as uv_new_ratio from (select uid, date(in_time) as d from tb_user_log ...

2025-06-03

0 51

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

select user_grade, round(count(uid) / max(uc), 2) as ratio from (select uid, uc, case when t2 >= 30 then...

2025-06-03

0 57

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

select dt, round(count(t) / count(1), 2) as uv_left_rate from (select uid, min(date(in_time)) as dt from tb_user_log group by uid) a ...

2025-06-02

0 78

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

select artical_id, max(uv) as max_uv from (select artical_id, sum(v) over (partition by artical_id order by t,v desc ) as uv from (select artica...

2025-06-02

0 78

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

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

2025-06-02

0 44

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

select * from (select tag, dt, sum(sl) over (rows between 6 preceding and current row ) as sum_like_cnt_7d, max...

2025-06-01

0 61

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

select author, date_format(end_time, '%Y-%m') as month, round(sum(case when if_follow = 1 then 1...

2025-06-01

0 39

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

select tag, sum(if_retweet) as retweet_cnt, round(sum(if_retweet) / count(1), 3) as retweet_rate from tb_user_video_log tuvl join tb_video_in...

2025-06-01

0 72

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

select tag, concat(round(avg(if(vt >= duration, 1, vt / duration)) * 100, 2), '%') as avg_plag_progress from (select timestampdiff(second, start_ti...

2025-06-01

0 49