Sunshine晶晶

分类

/ 注册

全部文章（共86篇）

题解 | 提取博客URL中的用户名

SELECT device_id, SUBSTRING_INDEX(blog_url,"/",-1) AS user_name FROM user_submit;

2025-06-27

0 62

题解 | 统计每种性别的人数

SELECT SUBSTRING_INDEX(profile,",",-1) AS gender ,COUNT(device_id) AS number FROM user_submit GROUP BY gender;

2025-06-26

0 70

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

SELECT COUNT(DISTINCT t2.device_id,t2.date)/COUNT(DISTINCT t1.device_id,t1.date) AS avg_ret FROM question_practice_detail AS t1 LEFT JOIN question_pra...

2025-06-26

0 93

题解 | 计算用户8月每天的练题数量

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

2025-06-26

0 38

题解 | 查看不同年龄段的用户明细

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

2025-06-26

0 64

题解 | 计算25岁以上和以下的用户数量

SELECT (CASE WHEN age>=25 THEN "25岁及以上" ELSE "25岁以下" END) AS age_cut, COUNT(device_id) AS ...

2025-06-26

0 46

题解 | 查找山东大学或者性别为男生的信息

SELECT device_id,gender,age,gpa FROM user_profile WHERE university="山东大学" UNION ALL SELECT device_id,gender,age,gpa FROM user_profile WHERE ...

2025-06-26

0 59

题解 | 统计每个用户的平均刷题数

SELECT t1.university , t3.difficult_level, ROUND(COUNT(t2.question_id)/COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt FROM user_profile AS t...

2025-06-26

0 46

题解 | 统计每个学校各难度的用户平均刷题数

SELECT t1.university,t3.difficult_level, ROUND(COUNT(t2.question_id)/COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt FROM user_profile AS t1 ...

2025-06-26

0 47

题解 | 统计每个学校的答过题的用户的平均答题数

SELECT t1.university,COUNT(t2.question_id)/COUNT(DISTINCT (t2.device_id)) AS avg_answer_cnt FROM user_profile AS t1 JOIN question_practice_detail AS t...

2025-06-26

0 53