刘大漂亮lmq

分类

题解(9)

/ 注册

全部文章（共9篇）

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

select count(b.device_id)/count(*) from ( select device_id,date from question_practice_detail group by device_id,date )a left join ( select device_...

2024-06-27

0 152

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

select exam_id,duration,release_time from (select a.exam_id,duration,release_time,use_time, row_number() over(partition by exam_id order by use_...

Mysql

2021-11-09

1 386

题解 | #浙大不同难度题目的正确率#

知识点：left join select difficult_level, avg(if(result='right',1,0)) as correct_rate from ( select device_id from user_profile where uni...

Mysql

2021-11-09

0 310

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

知识点：left join select a.device_id,university, count(case when result is not null then result else null end) as question_cnt, count(case...

Mysql

2021-11-09

0 371

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

# 知识点：模糊查询 *一般我们能想到的是字符串截取函数substring_index(),而模糊查询不失为另一种解题思路； SELECT IF(profile LIKE '%female','female','male') as gender, COUNT(1) as nu...

Mysql

2021-11-09

0 368

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

# 知识点：left join自关联 date_sub select count(b.device_id)/count(a.device_id) as avg_ret # avg(if(b.device_id is not null,1,0)) as avg...

Mysql

2021-11-09

0 313

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

知识点：group by substr count select SUBSTR(date,9,10) as day, count(question_id) as question_cnt from question_practice_detail where SUBSTR(date,1,7)...

Mysql

2021-11-09

6 454

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

知识点：case when select device_id,gender, case when age between 20 and 24 then '20-24岁' when age >=25 then '25岁及以上' else '其他' end as age_cut from user...

Mysql

2021-11-09

0 291

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

select '25岁以下' as age_cut, count(distinct case when age<25 or age is null then device_id end) as number from user_profile union select '25岁及以上' as ...

Mysql

2021-11-09

0 294