题1

https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select uid,exam_cnt,if(question_cnt is null, 0, question_cnt)
from(select uid,count(submit_time) as exam_cntfrom exam_recordwhere YEAR(submit_time) = 2021group by uid) as tleft join(selectuid,count(submit_time) as question_cnt
from practice_record
where YEAR(submit_time) = 2021
group by uid) as t2 using(uid)where uid in(selectuidfrom exam_recordjoin examination_info using(exam_id)join user_info using(uid)where tag = 'SQL' and difficulty = 'hard' and `level` = 7group by uidhaving avg(score) >= 80)
order by exam_cnt asc, question_cnt desc

题目2

https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select u_i.uid as uid,count(distinct act_month) as act_month_total,count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021,count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam,count(distinct casewhen year(act_time) = 2021and tag = 'question'then act_dayend) as act_days_2021_question
from user_info as u_ileft join (select uid,start_time as act_time,date_format(start_time, '%Y%m') as act_month,date_format(start_time, '%Y%m%d') as act_day,'exam' as tagfrom exam_recordunion all select uid,submit_time as act_time,date_format(submit_time, '%Y%m') as act_month,date_format(submit_time, '%Y%m%d') as act_day,'question' as tagfrom  practice_record) as exam_and_practiceon exam_and_practice.uid = u_i.uidwhere u_i.level >= 6
group by uid
order by act_month_total desc, act_days_2021 desc