CARLJOSEPHLEE

分类

题解(26)

/ 注册

全部文章（共209篇）

题解 | 统计各个部门的工资记录数

select d.dept_no,d.dept_name,sum(1) sum from departments d join dept_emp de using(dept_no) join salaries s using(emp_no) group by d.dept_no order by d...

2025-07-25

1 16

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

SELECT device_id, university, gpa FROM ( SELECT device_id, university, gpa, ROW_NUMBER() OVER ( PAR...

2025-07-21

1 29

题解 | 截取出年龄

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,",",-2),',',1) age,COUNT(*) number FROM user_submit GROUP BY age;

2025-07-21

0 18

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

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

2025-07-21

0 16

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

不会写，抄答案了qwq@烤点老白薯(637174235)这题好难不会写[牛泪] SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret from question_prac...

2025-07-21

1 50

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

select day(date) as day,count(1) question_cnt from question_practice_detail where date >= '2021-08-01' and date < '2021-09-01' group by day

2025-07-21

0 13

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

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

2025-07-21

0 14

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

select (case when age is null then '25岁以下' when age < 25 then '25岁以下' else '25岁及以上' end) age_cut,count(*) number from user_profile group by age_cut

2025-07-21

0 14

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

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

2025-07-21

0 15

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

select up.university,qd.difficult_level, round(count(qd.question_id)/count(distinct qpd.device_id),4)avg_answer_cnt from (select * from user_profile w...

2025-07-21

0 17