爱吃菜叶的猫

题解

评论(2)

/ 注册

全部文章 / 题解（共57篇）

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

SELECT * FROM ( SELECT tag, dt, SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS sum_lik...

Mysql

2022-05-02

0 263

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

SELECT author, month, ROUND(fans / play_cnt, 3) AS fans_growth_rate, SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans FROM ( ...

Mysql

2022-05-02

0 276

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

-- 有用户互动的最近时间：max(start_time) -- 最近一个月：当天-29天 SELECT tag, SUM(if_retweet) AS retweet_cut, ROUND(SUM(if_retweet) / COUNT(start_time), 3) AS r...

Mysql

2022-05-01

0 216

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

-- 计算各类视频的平均播放进度 SELECT tag, CONCAT(ROUND(AVG(IF(play_time >= duration, 100, play_time / duration * 100)), 2), '%') AS avg_play_progress FROM ...

Mysql

2022-05-01

0 262

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

/* * 完播率：完整播放的次数 / 总播放次数 */ SELECT video_id, ROUND(COUNT(IF(play_time >= duration, video_id, NULL)) / COUNT(video_id), 3) AS avg_comp_play_ra...

Mysql

2022-05-01

0 253

题解 | #注册当天就完成了试卷的名单第三页#

SELECT uid, level, register_time, MAX(score) OVER(PARTITION BY uid) AS max_score FROM user_info JOIN exam_record USING(uid) J...

Mysql

2022-04-29

0 224

题解 | #注册时间最早的三个人#

SELECT uid, nick_name, register_time FROM user_info ORDER BY register_time LIMIT 3

Mysql

2022-04-29

0 244

题解 | #各用户等级的不同得分表现占比#

-- 统计不同用户等级的人完成的试卷数 -- 统计不同用户等级的人各得分等级试卷数 SELECT level, score_grade, ROUND( COUNT(uid) / total_cnt, 3 ) AS ratio FROM ( SELE...

Mysql

2022-04-29

0 300

题解 | #根据指定记录是否存在输出不同情况#

/* * 建立新表，记录用户试卷未完成数和未完成率 */ WITH incomplete_t AS ( SELECT uid, level, COUNT(IF(submit_time IS NULL, exam_id, NULL)) AS incomplete_cnt, ...

Mysql

2022-04-29

0 291

题解 | #筛选昵称规则和试卷规则的作答记录#

WITH valid_user AS (SELECT uid FROM user_info WHERE nick_name REGEXP '^牛客\\d+号$' OR nick_name REGEXP '^\\d+$'), valid_exam AS (SELECT exam_id FROM e...

Mysql

2022-04-29

0 236