求职小张957

分类

/ 注册

全部文章（共39篇）

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

SELECT up.device_id, up.university, COUNT( question_id) AS question_cnt, SUM(IF(result = "right", 1, 0)) AS right_qu...

2024-09-15

0 84

题解 | #截取出年龄#

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,",",-2), ",", 1) AS age, COUNT(device_id) AS number FROM user_submit GROUP ...

2024-09-15

0 72

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

SELECT CASE WHEN profile LIKE "%,male" THEN "male" WHEN profile LIKE "%,female" THEN &q...

2024-09-15

0 80

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

SELECT COUNT(date2.device_id)/COUNT(date1.device_id) AS avg_ret FROM (SELECT DISTINCT device_id,date FROM question_practice_detail) AS date1 L...

2024-09-15

0 85

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

SELECT DISTINCT device_id, gender, CASE WHEN age < 20 THEN "20岁以下" WHEN age < 25 THEN "20...

2024-09-15

0 63

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

SELECT CASE WHEN age IS NULL THEN "25岁以下" WHEN age <25 THEN "25岁以下" WHEN age >= 25 THEN "...

2024-09-15

0 89

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

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

2024-09-15

0 89

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

SELECT university, qd.difficult_level, COUNT(qpd.question_id)/COUNT(DISTINCT qpd.device_id) AS avg_answer_cnt FROM user_profile up JO...

2024-09-15

0 82

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

SELECT up.university, qd.difficult_level, ROUND(COUNT(qd.question_id)/COUNT(DISTINCT up.device_id),4) AS avg_answer_cnt FROM user_p...

2024-09-15

0 74