题解 | 最受欢迎的top3课程
最受欢迎的top3课程
https://www.nowcoder.com/practice/b9b33659559c46099aa3257da0374a48
select cid,
pv,
time_len
from (
select cid,
pv,
time_len,
row_number() over (order by pv desc, time_len desc) rk
from (
select c.cid,
count(*) pv,
sum(timestampdiff(minute, start_time, end_time)) time_len
from course_info_tb c
join play_record_tb p using (cid)
where date_add(release_date,interval 1 week)>date(start_time)
group by c.cid
having avg(score)>=3
)t1
) t2
where rk<=3
order by rk
