题解 | #牛客每个人最近的登录日期(五)#

-- 存在次日登录情况的新用户数量
SELECT COUNT(tmp.user_id) co,tmp.date FROM login l1,
(SELECT t.user_id,t.`date` FROM login t WHERE NOT EXISTS (
	SELECT 1 FROM login l WHERE t.id = l.id AND l.`date` <= date_sub(t.`date`,interval 1 day)
)
GROUP BY t.user_id) tmp
WHERE l1.user_id = tmp.user_id
AND l1.`date` = DATE_ADD(tmp.`date`,interval 1 day)
GROUP BY tmp.date;

-- 每日新用户数量
SELECT l2.date,COUNT(tms.user_id) FROM login l2 LEFT join (
SELECT t.user_id,t.`date` FROM login t WHERE NOT EXISTS (
	SELECT 1 FROM login l WHERE t.id = l.id AND l.`date` <= date_sub(t.`date`,interval 1 day)
) 
GROUP BY t.user_id) tms
ON l2.user_id = tms.user_id
AND l2.date = tms.date
GROUP BY l2.date;

-- 极其复杂的算出结果

SELECT tmp1.date,
CASE 
WHEN tmp1.co != 0
THEN ROUND((IFNULL(tmp2.co,0)/tmp1.co),3)
ELSE round(0.000)
END co
FROM 
(SELECT l2.date,COUNT(tms.user_id) co FROM login l2 LEFT join (
SELECT t.user_id,t.`date` FROM login t WHERE NOT EXISTS (
	SELECT 1 FROM login l WHERE t.id = l.id AND l.`date` <= date_sub(t.`date`,interval 1 day)
) 
GROUP BY t.user_id) tms
ON l2.user_id = tms.user_id
AND l2.date = tms.date
GROUP BY l2.date) tmp1
LEFT JOIN 
(SELECT COUNT(tmp.user_id) co,tmp.date FROM login l1,
(SELECT t.user_id,t.`date` FROM login t WHERE NOT EXISTS (
	SELECT 1 FROM login l WHERE t.id = l.id AND l.`date` <= date_sub(t.`date`,interval 1 day)
)
GROUP BY t.user_id) tmp
WHERE l1.user_id = tmp.user_id
AND l1.`date` = DATE_ADD(tmp.`date`,interval 1 day)
GROUP BY tmp.date) tmp2
ON tmp1.date = tmp2.date;