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

1. 关键知识点：join，max/min/count(distinct)，DATEDIFF，round
2. 思路解析：先统计每个人最早登录日期和登录天数，关联表中最大日期作为当天，计算每个人从第一次登录到当天内平均活跃天数，乘以7就是一周平均活跃天数了。

select
  uid,
  round(7 * active_days / DATEDIFF(cur_dt, first_dt), 2) as active_period
from
  (
    select
      uid,
      min(login_date) as first_dt,
      count(distinct login_date) as active_days
    from
      user_login_tb
    group by
      uid
  ) as t_uid_active
  join (
    select
      max(login_date) as cur_dt
    from
      user_login_tb
  ) as t_cur_dt on 1
order by
  uid;

遇到问题记得私聊哦