力扣-查询近30天活跃用户数
大家好,我是空空star,本篇带大家了解一道简单的力扣sql练习题。
文章目录
前言
一、题目:1141. 查询近30天活跃用户数
活动记录表:Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
该表是用户在社交网站的活动记录。
该表没有主键,可能包含重复数据。
activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
每个 session_id 只属于一个用户。
请写SQL查询出截至 2019-07-27(包含2019-07-27),近 30 天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。
以 任意顺序 返回结果表。
查询结果示例如下。
示例 1:
输入:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
输出:
+------------+--------------+
| day | active_users |
+------------+--------------+
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
+------------+--------------+
解释:注意非活跃用户的记录不需要展示。
二、解题
1.正确示范①
提交SQL
select activity_date day,count(distinct user_id) active_users
from Activity
where activity_date>'2019-06-27' and activity_date<='2019-07-27'
# activity_date>='2019-06-28' and activity_date<='2019-07-27'
group by activity_date
或者
select activity_date day,count(distinct user_id) active_users
from Activity
where activity_date>DATE_SUB('2019-07-27',INTERVAL 30 DAY)
and activity_date<='2019-07-27'
group by activity_date
或者
select activity_date day,count(distinct user_id) active_users
from Activity
where DATEDIFF('2019-07-27',activity_date)>=0
and DATEDIFF('2019-07-27',activity_date) <30
group by activity_date
运行结果
2.正确示范②
提交SQL
select activity_date day,count(distinct user_id) active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
# activity_date between DATE_SUB('2019-07-27',INTERVAL 29 DAY) and '2019-07-27'
group by activity_date
运行结果
3.正确示范③
提交SQL
select activity_date day,count(1) active_users
from(
select distinct activity_date,user_id
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
) u
group by activity_date
运行结果
4.其他
总结
正确示范①思路:
先按活跃日期筛选
activity_date>‘2019-06-27’ and activity_date<=‘2019-07-27’
或者 activity_date>DATE_SUB(‘2019-07-27’,INTERVAL 30 DAY) and activity_date<=‘2019-07-27’
或者 DATEDIFF(‘2019-07-27’,activity_date)>=0 and DATEDIFF(‘2019-07-27’,activity_date) <30 ,
再按活跃日期分组 group by activity_date,
再取去重用户数 count(distinct user_id);
正确示范②思路:
将正确示范①中活跃日期筛选改为
activity_date between ‘2019-06-28’ and ‘2019-07-27’ ,其他不变;
正确示范③思路:
先按活跃日期筛选,将数据按distinct activity_date,user_id去重后,
再按活跃日期分组 group by activity_date,
再取用户数 count(1)。