芒芒牛

分类

/ 注册

全部文章（共16篇）

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

select device_id,university,gpa from(select device_id,university,gpa, row_number() over(partition by university order by gpa asc ) as r from us...

2023-07-17

0 313

题解 | #截取出年龄#

select age,count(*) as number from (select device_id,substring_index(substring_index(profile,',',3),',',-1) as age from user_submit) a group by age

2023-07-17

0 280

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

select device_id,substring(blog_url,11,length(blog_url)-10) as user_name from user_submit

2023-07-17

3 1111

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

select a.gender,count(a.gender) as number from(select device_id, case when substring_index(profile,',',-1)='male' then 'male' when subst...

2023-07-17

0 282

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

select num_2/num_1 as avg_res from (select count(distinct b.device_id,b.date) as num_2 from question_practice_detail a join question_practice_detail b...

2023-07-17

0 349

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

select day(date) as day, count(id) as question_cnt from question_practice_detail where date between '2021-08-01' and '2021-08-31' group by day(date)

2023-07-17

0 261

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

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

2023-07-17

0 273

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

select age_type,count(*) as age_cnt from (select id, case when age<25 then '25岁以下' when age>=25 then '25岁及以上' when age is n...

2023-07-17

0 317

题解 | #平均活跃天数和月活人数#

select date_format(submit_time,'%Y%m') as month,round(count(distinct uid,date_format(submit_time,'%Y-%m-%d'))/count(distinct uid),2) as avg_active_day...

2023-02-24

0 237

题解 | #得分不小于平均分的最低分#

select score as min_score_over_avg from exam_record join examination_info using(exam_id) where score>=(select sum(score)/count(score) as avg_scor...

2023-02-24

0 238