【牛客】SQL128 未完成试卷数大于1的有效用户

描述

现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分),示例数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:012021-07-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0181
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
6100290022021-07-05 18:01:012021-07-05 18:59:0290
7100390022021-07-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100590022021-09-01 12:01:012021-09-01 12:31:0188
13100690022021-09-02 12:11:012021-09-02 12:31:0189

还有一张试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间),示例数据如下:

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002SQLeasy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

请统计2021年每个未完成试卷作答数大于1的有效用户的数据(有效用户指完成试卷作答数至少为1且未完成数小于5),输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合,按未完成试卷数量由多到少排序。示例数据的输出结果如下:

uidincomplete_cntcomplete_cntdetail
1002242021-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL

解释:2021年的作答记录中,除了1004,其他用户均满足有效用户定义,但只有1002未完成试卷数大于1,因此只输出1002,detail中是1002作答过的试卷{日期:tag}集合,日期和tag间用:连接,多元素间用;连接。

问题分解:

  • 关联作答记录和试卷信息:left join examination_info on using(exam_id);(题中exam_record中的exam_id在examination_info均存在,所以用left join和inner join效果一样)
  • 筛选2021年的记录:where year(start_time)=2021
  • 获取各用户的tag,start_time及未完成标记和已完成标记,如果该作答记录交卷了则已完成标记为1,未完成标记为0,否则相反:if(submit_time is null, 1, null) as incomplete
  • 按用户分组:group by uid
  • 统计未完成试卷作答数和已完成试卷作答数:count(incomplete) as incomplete_cnt
  • 统计作答过的tag集合:
    • 对于每条作答tag,用:连接日期和tag:concat_ws(':',date(start_time),tag)
    • 对于一个人(组内)的多条作答,用;连接去重后的作答记录:group_concat(distinct concat_ws(':',date(start_time), tag) separator ';')
  • 筛选未完成试卷作答数大于1的有效用户:having complete_cnt >= 1 and incomplete_cnt between 2 and 4
    • 完成试卷作答数至少为1:complete_cnt > 0
    • 未完成数小于5:incomplete_cnt < 5
    • 未完成试卷作答数大于1:incomplete_cnt > 1
with cte as(
    select
    uid,exam_id,tag,start_time,
    if(submit_time is null,0,1) as sub_num
    from
    exam_record left join examination_info
    using(exam_id)
    where left(start_time,4)=2021
)

select
uid,
(count(sub_num)-sum(sub_num)) as incomplete_cnt,
sum(sub_num) as complete_cnt,
group_concat(distinct concat_ws(':', date(start_time), tag) separator ';') as detail
from cte
group by uid
having incomplete_cnt between 2 and 4 and complete_cnt>0
order by incomplete_cnt desc

 

  • 20
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值