题解 | #商品交易(网易校招笔试真题)#

WITH t AS ( SELECT CASE

	WHEN
		TIMESTAMPDIFF( SECOND, t1.start_time, t1.end_time ) >= t2.duration THEN
			100 ELSE TIMESTAMPDIFF( SECOND, t1.start_time, t1.end_time ) * 100 / t2.duration
			END AS totaltime,
		t2.tag 
	FROM
		tb_user_video_log t1
		LEFT JOIN tb_video_info t2 ON t1.video_id = t2.video_id 
	) SELECT
	t.tag,
	concat( ROUND( avg( t.totaltime ), 2 ), "%" ) AS avg_play_progress 
FROM
	t 
GROUP BY
	tag 
HAVING
  avg(t.totaltime) > "60%" 

ORDER BY avg_play_progress DESC;

解题思路：
1.以设备id为连接字段左连接两表得到一张临时表，表字段含有播放时速、类别。
2.按照类别分组，对分组后过滤掉类别均值小于60%的记录行，表字段包含类别、平均播放时长。最后对平均播放时长降序排序。