牛客808484225号

分类

/ 注册

全部文章（共108篇）

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

select t1.device_id, t1.university, sum(case when t2.result is not null then 1 else 0 end), sum(case when t2.result = 'right' then 1 else 0 end) FROM ...

Mysql

2022-09-15

0 224

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

SELECT device_id,university,gpa FROM ( SELECT *,ROW_NUMBER() over (PARTITION BY university ORDER BY gpa) AS rn FROM user_p...

Mysql

2022-09-15

0 177

题解 | #截取出年龄#

SELECT age,COUNT(*) number FROM (SELECT SUBSTR(PROFILE,12,2) age FROM user_submit) ages GROUP BY age

Mysql

2022-09-15

0 174

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

select day(date) as day,count(id) as question_cnt from question_practice_detail where month(date)=08 group by day

Mysql

2022-09-14

0 181

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

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

Mysql

2022-09-14

0 221

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

select case when age<25 or age is null then "25岁以下" when age>=25 then "25岁及以上" end as age_cut, count(*)&...

Mysql

2022-09-14

0 223

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

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

Mysql

2022-09-14

0 202

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

select university ,difficult_level,count(qpd.question_id)/count(DISTINCT qpd.device_id) as avg_answer_cnt from user_profile as u inner join question_p...

Mysql

2022-09-14

0 158

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

SELECT COUNT(distinct q2.device_id,q2.date)/COUNT(distinct q1.device_id,q1.date) as avg_ret FROM question_practice_detail q1 LEFT JOIN question_...

Mysql

2022-09-14

0 268

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

SELECT substring_index(profile, ',', -1) as gender,COUNT(*) AS number FROM user_submit group by gender;

Mysql

2022-09-14

0 202