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

WITH tmp1 AS
 (SELECT uid, cid, COUNT(*) AS pv
    FROM play_record_tb
   GROUP BY uid, cid
  HAVING COUNT(*) > 1)
SELECT cid, pv, rk
  FROM (SELECT t1.cid,
               round(t1.pv, 3) AS pv,
               row_number() over(ORDER BY t1.pv DESC, t2.release_date DESC) AS rk
          FROM (SELECT cid, SUM(pv) AS pv FROM tmp1 GROUP BY cid) t1
         INNER JOIN course_info_tb t2
            ON t1.cid = t2.cid) tmp2
 WHERE rk < 4
 ORDER BY rk ASC;