【牛客】SQL132 每个题目和每份试卷被作答的人数和次数

描述

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：

解释：“试卷”有3人共练习3次试卷9001，1人作答3次9002；“刷题”有3人刷5次8001，有2人刷2次8002

#在union之前单独排序，需要对表重新命名
select * from
(select
exam_id as tid,count(distinct uid) as uv,count(start_time) as pv
from
exam_record
group by exam_id
order by pv desc,uv desc)t1
 
union
 
select * from
(select
question_id as tid,count(distinct uid) as uv,count(submit_time) as pv
from
practice_record
where submit_time is not null
group by question_id
order by pv desc,uv desc)t2