会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

题解 | 近三个月未完成试卷数为0的用户完成情况

#3个月时间节点 with a as( select uid, m,submit_time from (select er.uid,date_format(start_time,'%Y%m') as m, dense_rank() over (partition by er.uid ord...

2025-05-31

1 27

题解 | 每类试卷得分前3名

with a as( select tag,er.uid,max(score) as da,min(score) as xiao from examination_info ei join exam_record er on ei.exam_id=er.exam_id ...

2025-05-30

0 29

题解 | 分别满足两个活动的人

select uid,'activity1' as activity from examination_info ei join exam_record er on ei.exam_id=er.exam_id where score >= 85 and year(start_time)=202...

2025-05-29

0 22

题解 | 每个题目和每份试卷被作答的人数和次数

select * from (select exam_id as tid,count(distinct uid) as uv, count(*) as pv from exam_record group by exam_id order by uv desc,pv desc) a union se...

2025-05-29

0 19

题解 | 作答试卷得分大于过80的人的用户等级分布

with a as(select level,ui.uid,score from exam_record er join examination_info ei on ei.exam_id = er.exam_id join user_info ui on ui.uid= er.uid wh...

2025-05-28

0 21

题解 | 试卷发布当天作答人数和平均分

select er.exam_id, count(distinct er.uid) as uv, round(avg(score),1) avg_score from examination_info ei join exam_record er on ei.exam_id = er.exam_...

2025-05-27

0 24

题解 | 月总刷题数和日均刷题数

select date_format(submit_time,'%Y%m') as submit_month, count(*) as month_q_cnt, round(count(*)/day(last_day(max(date(submit_time)))),3) as avg_day_q_...

2025-05-26

0 21

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

select month,round(sum(active_days)/count(uid),2) as avg_active_days, sum(au) as mau from( select date_format(submit_time,'%Y%m') as month,uid, count...

2025-05-25

0 25

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

with a as( select tag, avg(score) as avg_score from exam_record er join examination_info ei on er.exam_id =ei.exam_id group by tag) select min(sco...

2025-05-24

0 30

题解 | 各城市最大同时等车人数

with a as( select uid,city,event_time as dt,1 as flag from tb_get_car_record union select uid,city,end_time as dt,-1 as flag from tb_get_car_record w...

2025-05-23

0 27