SQL 70) 查询每个日期新用户的次日留存率

发现其他大神的一个很好懂的解法！

• 求出新用户第一天登录日期

  SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id

• 算出有新用户登录的日期的新用户留存率

  SELECT a.date,ROUND(COUNT(DISTINCT login.user_id)/ COUNT(a.user_id),3) AS p
  FROM (SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id) AS a
  LEFT JOIN login
  ON login.user_id=a.user_id
  AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
  GROUP BY a.date

• 用UNION补上没有新用户登录的日期

  SELECT a.date,ROUND(COUNT(DISTINCT login.user_id)/ COUNT(a.user_id),3) AS p
  FROM (SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id) AS a
  LEFT JOIN login
  ON login.user_id=a.user_id
  AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
  GROUP BY a.date
  UNION
  SELECT date,0.000 AS p
  FROM login
  WHERE date NOT IN(
  SELECT MIN(date) FROM login GROUP BY user_id)
  ORDER BY date;

补充其他大神的CASE WHEN 解法

select date
        ,ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day) 
             from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;