lahm66

分类

/ 注册

全部文章（共116篇）

题解 | 找出每个学校GPA最低的同学

select device_id,a.university,a.gpa from user_profile a right join ( select university,min(gpa) gpa from user_profile group by university ...

2025-10-21

1 10

题解 | 截取出年龄

select substring_index(substring_index(profile,',',-2),',',1) age,count(*) number from user_submit group by age;

2025-10-20

1 9

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

select device_id,substring_index(blog_url,'/',-1) user_name from user_submit;

2025-10-20

1 11

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

select substring_index(profile,',',-1) gender,count(device_id) number from user_submit group by gender;

2025-10-19

1 11

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

select count(q2.device_id)/count(q1.device_id) avg_ret from ( select distinct device_id,date from question_practice_detail ) as q1 left join ...

2025-10-19

1 14

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

select day(date) day,count(*) question_cnt from question_practice_detail where month(date)=8 and year(date)=2021 group by date

2025-10-18

1 11

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

select device_id,gender, case when age<20 then '20岁以下' when age>=20 and age<=24 then '20-24岁' when age>24 then '25岁及以上' else '其他' end ag...

2025-10-18

1 11

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

select if(age>=25,'25岁及以上','25岁以下') age_cut,count(*) number from user_profile group by age_cut;

2025-10-18

1 13

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

select device_id,gender,age,gpa from user_profile where university='山东大学' union all select device_id,gender,age,gpa from user_profile where gender='ma...

2025-10-18

1 13

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

select university,difficult_level,round(count(qpd.question_id)/count(distinct qpd.device_id),4) avg_answer_cnt from user_profile up join question_prac...

2025-10-18

1 15