LeetCode 1107.每日新用户统计

最新推荐文章于 2024-10-31 16:16:13 发布

奋斗哼哼

最新推荐文章于 2024-10-31 16:16:13 发布

阅读量182

点赞数

文章标签： leetcode 数据库 mysql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/131649162

版权

该SQL查询旨在获取从2019-06-30开始最多90天内的每天首次登录的用户数量。通过分析Traffic表中login活动的数据，对用户_id进行分区并按日期排序，找出每天的第一个登录记录，然后计算符合条件的日期对应的用户数。

摘要由CSDN通过智能技术生成

数据准备

Create table If Not Exists Traffic (user_id int, activity ENUM('login', 'logout', 'jobs', 'groups', 'homepage'), activity_date date);
Truncate table Traffic;
insert into Traffic (user_id, activity, activity_date) values ('1', 'login', '2019-05-01');
insert into Traffic (user_id, activity, activity_date) values ('1', 'homepage', '2019-05-01');
insert into Traffic (user_id, activity, activity_date) values ('1', 'logout', '2019-05-01');
insert into Traffic (user_id, activity, activity_date) values ('2', 'login', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('2', 'logout', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('3', 'login', '2019-01-01');
insert into Traffic (user_id, activity, activity_date) values ('3', 'jobs', '2019-01-01');
insert into Traffic (user_id, activity, activity_date) values ('3', 'logout', '2019-01-01');
insert into Traffic (user_id, activity, activity_date) values ('4', 'login', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('4', 'groups', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('4', 'logout', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-03-01');
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-03-01');
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-06-21');
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-06-21');

需求

– 编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是2019-06-30.

输入

在这里插入图片描述

输出

with t1 as (
    select *,
       row_number() over (partition by user_id) rn1
    from Traffic
    where activity='login'
    order by user_id,activity_date
)
select activity_date as login_date,count(user_id) as user_count
from t1
where rn1=1 and datediff('2019-06-30',activity_date)<=90
group by activity_date;