新用户的次日留存率
表结构
|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
网友评论