题解 | #月均完成试卷数不小于3的用户爱作答的类别#

月均完成试卷数不小于3的用户爱作答的类别

https://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845

# 子查询:选出“当月均完成试卷数” 不小于3的用户
# 按用户、月份分组,选出用户每个月的作答次数 都大于2 的用户
# 外层查询:统计类别及作答次数

select
    tag,
    count(1) as tag_cnt
from
    exam_record
    left join examination_info using (exam_id)
where
    uid in (
        select
            uid
        from
            exam_record
        where
            submit_time is not null
        group by
            uid,DATE_FORMAT (submit_time, "%Y%M")
        having
           count(submit_time) > 2
    )
group by
    tag
order by
    tag_cnt desc

全部评论

相关推荐

评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务