题解 | #找出待召回的流失用户#

1. 关键知识点：count(distinct)，having，max，timestampdiff，join，order by xxx desc
2. 思路解析：
   ①按用户分组，统计每个用户登录天数和登录次数，并筛选满足条件的分组
   ②统计每个用户最后一次登录日期
   ③统计全表最大日期作为当前日期
   ④关联同一个用户的各个字段值
   ⑤筛选最后一次登录日期距今30天及以上的用户
   ⑥按要求排序输出

select
  uid,
  days,
  times
from
  (
    select
      uid,
      days,
      times,
      last_login_dt,
      cur_dt
    from
      (
        select
          uid,
          count(distinct login_date) as days,
          count(1) as times
        from
          user_login_tb
        group by
          uid
        having
          count(distinct login_date) >= 3
          or count(1) >= 4
      ) t_active_days
      join (
        select
          uid,
          max(login_date) as last_login_dt
        from
          user_login_tb
        group by
          uid
      ) t_last_login using (uid)
      join (
        select
          max(login_date) as cur_dt
        from
          user_login_tb
      ) t_cur_dt on 1 = 1
  ) t_all_info
where
  TIMESTAMPDIFF(DAY, last_login_dt, cur_dt) >= 30
order by
  days desc,
  times desc;