117，SQL训练之，力扣，1107. 每日新用户统计

凡梦_leo

于 2024-10-05 19:56:48 发布

阅读量385

点赞数 15

分类专栏： SQL实战训练文章标签： leetcode pandas python 数据库 sql linux 算法

本文链接：https://blog.csdn.net/qq_55006020/article/details/142718191

版权

SQL实战训练专栏收录该内容

119 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

Traffic 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
该表可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Traffic 表：
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1       | login    | 2019-05-01    |
| 1       | homepage | 2019-05-01    |
| 1       | logout   | 2019-05-01    |
| 2       | login    | 2019-06-21    |
| 2       | logout   | 2019-06-21    |
| 3       | login    | 2019-01-01    |
| 3       | jobs     | 2019-01-01    |
| 3       | logout   | 2019-01-01    |
| 4       | login    | 2019-06-21    |
| 4       | groups   | 2019-06-21    |
| 4       | logout   | 2019-06-21    |
| 5       | login    | 2019-03-01    |
| 5       | logout   | 2019-03-01    |
| 5       | login    | 2019-06-21    |
| 5       | logout   | 2019-06-21    |
+---------+----------+---------------+
输出：
+------------+-------------+
| login_date | user_count  |
+------------+-------------+
| 2019-05-01 | 1           |
| 2019-06-21 | 2           |
+------------+-------------+
解释：
请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。

三，建表语句

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')
select * from traffic;

四，分析

表格大法：过滤+分组过滤

第一步：过滤掉非login的行数

第二步：计算每个用户首次登录的日期；

第三步：过滤掉首次登录的日期不是在 2019-06-30 前90天的行数

第四步：以日期分组聚合用户id （去重一下有可能某用户当天重复多次登录~）

第五步：改名并输出

思路

表格大法：过滤+分组过滤第一步：过滤掉非login的行数第二步：计算每个用户首次登录的日期；第三步：过滤掉首次登录的日期不是在 2019-06-30 前90天的行数第四步：以日期分组聚合用户id （去重一下有可能某用户当天重复多次登录~）第五步：改名并输出

逻辑见表格：

解题过程

第一步：过滤掉非login的行数

在mysql中

第二步：计算每个用户首次登录的日期；

在mysql中

第三步：过滤掉首次登录的日期不是在 2019-06-30 前90天的行数

在mysql中

第四步：以日期分组聚合用户id （去重一下有可能某用户当天重复多次登录~）

在mysql中

第五步：改名并输出

五，SQL解答


with t1 as (
    select user_id, activity, activity_date,
           min(activity_date) over(partition by user_id order by activity_date) mo
    from traffic where activity='login'
)
#    select * from t1;
   ,t2 as (
    select
        user_id, activity, activity_date, mo
    from t1 where mo >= date_add('2019-06-30',interval -90 day) and mo<='2019-06-30'
)
# select * from t2;
,t3 as (
    select distinct user_id,mo from t2
)
select
    mo as login_date,count(user_id) as user_count
from t3 group by mo;