题解 | #平均播放进度大于60%的视频类别#

SELECT uid, COUNT(IF(e.submit_time IS NULL ,1,NULL)) AS incomplete_cnt, COUNT(e.submit_time) AS complete_cnt , GROUP_CONCAT( DISTINCT CONCAT_WS(':',DATE(e.start_time),i.tag) SEPARATOR';') AS detail FROM exam_record e INNER JOIN examination_info i ON e.exam_id = i.exam_id WHERE YEAR(e.start_time) = 2021 GROUP BY uid HAVING complete_cnt >=1 AND incomplete_cnt between 2 and 4 ORDER BY incomplete_cnt DESC;