题解 | 被重复观看次数最多的3个视频

WITH t AS (
    SELECT uid,cid,COUNT(*) AS pv
    FROM play_record_tb
    GROUP BY uid,cid
    HAVING COUNT(*)>=2
),
o AS (
SELECT t.cid,SUM(pv) AS pv,t1.release_date,
       ROW_NUMBER() OVER( ORDER BY SUM(pv) DESC,t1.release_date DESC) AS rk
FROM course_info_tb t1
LEFT JOIN t ON t1.cid=t.cid 
GROUP BY t.cid,t1.release_date
)
SELECT o.cid,o.pv,o.rk
FROM o 
WHERE rk<=3   AND cid IS NOT NULL  -- 过滤NULL的cid
  AND pv IS NOT NULL   -- 过滤NULL的pv