牛客30426190号

分类

/ 注册

全部文章（共28篇）

题解 | #统计复旦用户8月练题情况#

SELECT device_id, university, COUNT(question_id) AS question_cnt, SUM(right_question) AS right_question_cnt FROM ( SELECT ...

2023-10-31

1 249

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

SELECT author, month, ROUND(fans_growth_rate, 3) AS fans_growth_rate, SUM(sum_fans) OVER(PARTITION BY author ORDER BY month) AS total_...

2023-10-30

1 241

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

SELECT tag, SUM(IF(if_retweet=1, 1, 0)) AS retweet_cut, ROUND(AVG(IF(if_retweet=1, 1, 0)), 3) AS retweet_rate FROM tb_user_video_log t1 JO...

2023-10-29

1 225

题解 | #计算用户的平均次日留存率#

SELECT AVG(IF(second_day IS NULL, 0, 1)) AS avg_ret FROM ( SELECT DISTINCT t1.device_id AS device_id, t1.date AS first_day, ...

2023-10-29

2 246

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

SELECT tag, CONCAT(avg_play_progress, '%') AS avg_progress FROM ( SELECT tag, ROUND(AVG( IF( TIMES...

2023-10-28

1 262

题解 | #满足条件的用户的试卷完成数和题目练习数#

SELECT t1.uid, t1.exam_cnt AS exam_cnt, IFNULL(t2.question_cnt, 0) AS question_cnt FROM ( SELECT uid, COUNT(submit_t...

2023-10-22

1 200

题解 | #未完成试卷数大于1的有效用户#

SELECT er.uid, SUM(IF (er.submit_time IS NULL, 1, 0)) AS incomplete_cnt, COUNT(er.submit_time) AS complete_cnt, GROUP_CONCAT(DISTINCT ...

2023-10-20

1 229

题解 | #网易云音乐推荐(网易校招笔试真题)#

SELECT music_name FROM ( SELECT DISTINCT m.id, m.music_name FROM follow f JOIN...

2023-10-16

1 229

子查询嵌套

SELECT u.id, u.name, grade_num FROM( SELECT user_id, SUM(t1.real_grade) AS grade_num, DENSE_RANK() OVER(ORDER ...

2023-10-15

1 255

连接2025年和2026年两个子表

SELECT t1.job, t1.year_mon AS first_year_mon, t1.cnt AS first_year_cnt, t2.year_mon AS second_year_mon, t2.cnt AS second_year_cnt ...

2023-10-13

1 256