with t1 AS
(select tag, 
     (case when timestampdiff(second,start_time,end_time)<=duration 
     then timestampdiff(second,start_time,end_time)/duration else 1 end) process
from tb_user_video_log log left join tb_video_info info on log.video_id=info.video_id),
t2 as 
(select tag, avg(process) mean
from t1 
group by tag
having mean>0.6
order by mean desc)
select tag, concat(round(mean*100,2),'%') avg_play_progrss
from t2

Break down the problem

The question is a little bit complicated, if I don't use CET, it would be error-prone. Therefore, I choose CET to make my method clear.

with t1 as(select from),
t2 as(select from)
select from t2

1. calculate the avg_play_progree:

• calculate the time actually played;
• the progress rate;
• average of them with the group of tag;
• choose the ones > 60%;
• make it the format of percent and round thme up to 2 digits;
• order by avg_play_progress desc

2. Join to tables

Table 1

1. Join the two tables together to calculate the process rate of every video according to every user.

   timestampdiff(second, start, end)

Table 2

1. Use infomation in table 1, calculate the avg of process rate according to their tag. 2. Choose the ones > 0.6.
2. Order.

Table 3

1. Percent.

   concat(a,'%')

2. 2 digits.