题解 | #零食类商品中复购率top3高的商品#

1. select video_id,
2. round(sum(comp_play)/count(comp_play),3) avg_comp_play_rate
3. from (
4. select video_id,
5. if(timestampdiff(second, start_time, end_time) >=duration,1,0) comp_play
6. from tb_user_video_log
7. inner join tb_video_info using(video_id)
8. where year(start_time) = 2021
9. ) a

group by video_id order by avg_comp_play_rate desc;

注意：

1. 首先要记得对子查询进行赋名即a
2. 其次，时间为2021需要进行限制
3. 最后，在子查询中不能用on链接两个表格，要用using