题解 | #计算每个人的平均使用周期#

with cte as(
select *,first_value(login_date)over(partition by uid order by login_date) as first_day,
         max(login_date)over() as last_date
from user_login_tb ),
cte1 as (
select uid,count(distinct login_date) as num,datediff(last_date,first_day) as days
from cte 
group by uid,days )
select uid,round(num/days*7,2) as active_period
from cte1 
order by uid;

用了两个临时表，逻辑更清楚点，应该提前给出公式的，即：周平均活跃天数=活跃天数/(最近时间-最早登陆日期)*7