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

-- 链接 LINK: https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8?tpId=82&tqId=35088&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=undefined&tags=&title=
-- 标题 TITLE: SQL264 牛客每个人最近的登录日期(五)
-- 日期 DATE: 2023-02-25 08:37:34

/* 设计思路

**/

-- CASE 1
SELECT T1.date, 
        ROUND(IFNULL(count_1*1.0/count_0, 0), 3) AS p
FROM (
    SELECT DISTINCT date
    FROM login
) T1
LEFT JOIN (
    SELECT DISTINCT MIN(date) AS date,
        COUNT(user_id) OVER(PARTITION BY MIN(date)) AS count_0
    FROM login
    GROUP BY user_id
) T2 ON TIMESTAMPDIFF(day, T1.date, T2.date) = 0
LEFT JOIN (
    SELECT date, COUNT(user_id) AS count_1
    FROM (
        SELECT user_id, date,
                TIMESTAMPDIFF(day, MIN(date) OVER(PARTITION BY user_id), date) AS tag
        FROM login
        GROUP BY user_id, date
    ) T
    WHERE tag = 1
    GROUP BY date
) T3 ON TIMESTAMPDIFF(day, T1.date, T3.date) = 1
ORDER BY T1.date
;

-- CASE 2
SELECT T1.date,
        ROUND(IFNULL(SUM(IF(1 = tag, 1, 0)) / SUM(IF(0 = tag, 1, 0)), 0), 3) AS p
FROM (
    SELECT DISTINCT date
    FROM login
) T1
LEFT JOIN (
    -- 每名用户首次注册后，后续再次登录的情况, tag 表示距离首次登录的天数
    SELECT user_id,
        MIN(date) OVER(PARTITION BY user_id) AS date,
        TIMESTAMPDIFF(day, MIN(date) OVER(PARTITION BY user_id), date) AS tag
    FROM login
    GROUP BY user_id, date
) T2 USING(date)
GROUP BY T1.date
;


-- 数据 DATA ===================================================
drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');