-- top3视频、 平均评分不低于3分、pv 倒序、播放时长(一周内)
WITH prt AS (
SELECT
id,
uid,
cid,
start_time,
end_time,
TIMESTAMPDIFF(minute, start_time, end_time) AS per_time,
score
FROM play_record_tb
)
SELECT
cid,
count(*) AS pv,
ROUND(sum(per_time), 3) AS time_len
FROM prt
JOIN course_info_tb USING (cid)
WHERE DATEDIFF(start_time, release_date) < 7
GROUP BY cid
HAVING AVG(score) >= 3
ORDER BY pv DESC,time_len DESC
LIMIT 3;



京公网安备 11010502036488号