Juicelabxx

分类

/ 注册

全部文章（共38篇）

这题的过滤去重逻辑真的绝了 | #每个6/7级用户活跃情况#

难点1:act_month_total 要算所有年份不止是2021年所以不能和后面几列一起用where过滤难点2:act_month_total分别求试卷和练习的月份的时候如果只是用两个count月份的count相加会出现重复月份的情况，即使你分别用了两个distinct，一种方法就是再分别建立...

2024-09-17

1 194

难点在：如果不用子查询直接过滤，会过滤掉条件以外其他答题情况

SELECT uid, COUNT(DISTINCT r.id) as exam_cnt, COUNT(DISTINCT p.id) as question_cnt FROM exam_record r LEFT JOIN practice_record p ...

2024-09-17

1 116

这道题简单在从始至终不用group by

WITH a AS( SELECT exam_id, timestampdiff(second,start_time, submit_time) as finish, DENSE_RANK() OVER (PARTITION BY exam_i...

2024-09-17

1 109

CTE比子查询逻辑更清晰 | #每类试卷得分前3名#

因为窗口函数和聚合函数不能在同一个子查询里叠加使用，并且窗口函数的排序结果不能在同一个子查询底部进行where过滤，所以递进的CTE表达式是最方便的： WITH info AS ( SELECT tid, uid, MAX(score) as maxs, M...

2024-09-17

1 104

清爽的解法 | #试卷发布当天作答人数和平均分#

SELECT exam_id, COUNT(DISTINCT uid) as uv, ROUND(SUM(score)/COUNT(DISTINCT r.id),1) as avg_score FROM user_info JOIN exam_record r...

2024-09-17

1 144

这道题你难得无敌了 | #未完成试卷数大于1的有效用户#

SELECT uid, count(distinct case when start_time is not null and submit_time is null then r.id end) AS incomplete_cnt, count(distinct case ...

2024-09-16

1 108

不会出题就别出。。 | #字符函数正则匹配1#

那么大个网站那么大个公司出个sql专栏练习连个题目、用例都出不明白真是无语。。写这个栏目的你本科毕业了吗？ SELECT id, comment FROM comment_detail WHERE comment REGEXP '是' or comment RE...

2024-09-14

1 102

连个表就行 | #查询用户刷题日期和下一次刷题日期#

WITH RANKED AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn FROM questions_pass_record ), ...

2024-09-14

1 101

这题通过率怎么这么低？感觉没订单七难| #网易云音乐推荐#

SELECT m.music_name FROM follow f JOIN music_likes ml ON f.follower_id = ml.user_id JOIN music m ON ml.music_id = m.id WHERE...

2024-09-14

1 161

确实难啊写这个区间 | #最差是第几名(二)#

WITH ranked AS( SELECT grade, number, SUM(number) OVER () AS total, SUM(number) OVER (ORDER BY grade) AS roll ...

2024-09-14

2 109