后街小行家

分类

题解(10)

/ 注册

全部文章（共41篇）

题解 | #截至当月的练题情况#

with cte as ( select device_id,left(event_date,7) as ym ,count(*) as cnt from question_practice_detail group by device_id,ym ) select device_id,ym, ...

2024-03-25

0 199

题解 | #任意两个连续自然月练题次数大于1的用户#

with cte as( select device_id,date_format(event_date,'%Y%m') as event_month,count(*) as num from question_practice_detail group by device_id,event_mon...

2024-03-25

0 271

题解 | #用户名规范化#

with cte as ( select device_id,substring_index(blog_url,'/',-1) as name from user_submit ) select device_id,concat(upper(left(name,1)),lower(right(...

2024-03-25

0 159

题解 | #用户昵称超长的用户#

select device_id,nick_name,char_length(nick_name) as nick_len from user_submit where char_length(nick_name)>4 order by device_id desc

2024-03-25

0 222

题解 | #用户每月最后一周的练题数#

select device_id,date_format(event_date,'%Y年%m月') as ym, count(*) as cnt from question_practice_detail where datediff(last_day(event_date),event_date)...

2024-03-25

0 186

题解 | #每月各旬有多少人练题#

with cte as ( select left(event_date,7) as dt_month, case when right(event_date,2)<=9 then '上旬' when right(event_date,2)<=...

2024-03-25

0 175

题解 | #每个用户的第一有效信息#

select device_id , case when answer_cnt then concat('答题数:',answer_cnt) when gpa then concat('gpa:',gpa) ...

2024-03-25

0 148

题解 | #各年级学生的平均绩点#

with cte as ( select graduate_year,round(avg(gpa),2) as avg_gpa from user_profile where graduate_year >=2022 group by graduate_year ) select case ...

2024-03-25

0 198

题解 | #北京大学或练题数大于2的学生ID#

select device_id from user_profile left join question_practice_detail using(device_id) group by device_id having group_concat(distinct university)='北...

2024-03-25

0 376

题解 | #所有用户对高难度题目的刷题数#

with cte as( select device_id,count(*) as cnt from question_practice_detail a left join question_detail b using(question_id) where difficult_level ='...

2024-03-25

0 177