新用戶的次日留存率
表結(jié)構(gòu)

|uuid|is_new|   day   |platform|
|----|------|---------|--------|
| 1  | 1    | 20180714| iphone |
| 2  | 0    | 20180715| Android|
| 1  | 1    | 20180715| iphone |
| 4  | 0    | 20180715| Android|

SELECT t.platform,COUNT(nt.uuid) AS RemainUserNextDay,COUNT(t.uuid) AS NewUserCount ,COUNT(nt.uuid)*1.0/COUNT(t.uuid) AS RemainRateNextDay
FROM t AS t
LEFT JOIN t AS nt ON t.platform=nt.platform and nt.uuid=t.uuid AND DATEDIFF(d,t.[day],nt.[day])=1 AND nt.is_new=0
WHERE t.is_new=1
GROUP BY t.platform