MYSQL练习---每个人最近的登录日期

一、题目

查询每个日期登录新用户个数，并且查询结果按照日期升序排序

二、源数据

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');

三、结果

四、解题代码

SELECT date,
SUM(CASE WHEN r=1 THEN 1 ELSE 0 END )AS new
FROM (
    SELECT user_id,date,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY DATE) AS r
    FROM login) AS l
GROUP BY date
ORDER BY date