羌昼晦

分类

/ 注册

TA的专栏 149篇文章 34人订阅

TOP101-BM系列

64篇文章 556人学习

42篇文章 997人学习

SQL专项练习

43篇文章 818人学习

全部文章（共149篇）

SQL30题解 | #统计每种性别的人数# 来自专栏

SELECT substring_index(profile, ',', -1) AS gender, count(*) AS number FROM user_submit GROUP BY gender;

2023-10-11

0 229

SQL29题解 | #计算用户的平均次日留存率# 来自专栏

# 限定条件：第二天再来 # 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是...

2023-10-11

0 818

SQL28题解 | #计算用户8月每天的练题数量# 来自专栏

SELECT DAY(date) AS day, COUNT(question_id) AS question_cnt FROM question_practice_detail WHERE YEAR(date) = 2021 and MONTH(date) = 8 GROUP BY date;

2023-10-11

0 224

SQL27题解 | #查看不同年龄段的用户明细# 来自专栏

SELECT device_id, gender, (CASE WHEN age < 20 THEN '20岁以下' WHEN age BETWEEN 20 AND 24 THEN '20-24岁' WHEN age >= 25 T...

2023-10-11

0 265

SQL26题解 | #计算25岁以上和以下的用户数量# 来自专栏

SELECT (CASE WHEN age >= 25 THEN '25岁及以上'ELSE '25岁以下' END) AS age_cnt, count(*) AS number FROM user_profile GROUP BY age_cnt;

2023-10-11

0 259

SQL25题解 | #查找山东大学或者性别为男生的信息# 来自专栏

#不去重union all,WHERE和UNION和自动去重 SELECT device_id, gender, age, gpa FROM user_profile WHERE university = '山东大学' UNION ALL SELECT device_id, gender, ...

2023-09-15

0 333

SQL24题解 | #统计每个用户的平均刷题数# 来自专栏

#不同难度,按照难度分组:GROUP BY difficult_level #平均回答次数:round(count(question_id)/count(distinct device_id), 4) SELECT university, QD.difficult_level, ...

2023-09-15

0 307

SQL23题解 | #统计每个学校各难度的用户平均刷题数# 来自专栏

#按照学校和难度分组:GROUP BY university,difficult_level #平均答题数：count(QPDD.question_id) / count(distinct QPDD.device_id) SELECT university, difficult_level, ...

2023-09-15

0 481

SQL20题解 | #分组排序练习题# 来自专栏

SELECT university, avg(question_cnt) AS avg_question_cnt FROM user_profile GROUP BY university ORDER BY avg_question_cnt ASC;

2023-09-14

0 300

SQL19题解 | #分组过滤练习题# 来自专栏

SELECT university, avg(question_cnt) AS avg_question_cnt, avg(answer_cnt) AS avg_answer_cnt FROM user_profile GROUP BY university ...

2023-09-14

0 274