题解 | #最近7天每天的人均停留时长和次均有效时长#

#题不难，让牛客表述的很难了就2点，1.求近7天的人均时长=总时长/总人数 2.次均有效时长=符合开始和结束时间大于等于3的总时长/
#符合开始和结束时间大于等于3的总时长
总人数
select 
dt,
round(
sum(diff)/count(distinct uid),1) as avg_stay_time_len_user,
ifnull(
round(
sum(case when t.diff>=3 then diff else 0 end)/count(case when t.diff>=3 then dt end),1),0) as avg_stay_time_len_once
from(
select 
uid,
date(end_time) as dt,
timestampdiff(second,start_time,end_time) as diff
from user_play_log_tb
where
datediff((select max(date(end_time)) from user_play_log_tb),date(end_time))<7
) as t 
group by dt