[1141] User Activity for the Past 30 Days I

User Activity for the Past 30 Days I - LeetCode

 

Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website. 
Note that each session belongs to exactly one user.

 

Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
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   |
+---------+------------+---------------+---------------+
Output: 
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
Explanation: Note that we do not care about days with zero active users.

 

 

문제 조건 

- 2019-07-27까지 포함하여 이전 30일 동안의 일일 활성 유저 수를 출력하라.

- 유저가 해당 날짜에 하나 이상의 활동을 한 경우 활성 유저이라 한다.

 

- ENUM type? 

: R의 factor와 같은 변수형 범수를 의미. 

  즉, "정해진 범주 내에서 카테고리별로 분석(범주형 자료 분석)을 하기 위해 주로 사용되는 데이터 자료형"이다. 

 

문제 풀이

- 7월 27일을 포함 한 30일 전의 데이터를 구하기 위해서 datediff 혹은 between을 이용하여 날짜를 지정함 

- 한 번이라도 활동을 한 경우 활성 유저라 정의하기 때문에 activity_type에 is not null을 추가

 

정답 쿼리

# datediff 
select activity_date as day,
        count(distinct user_id) as active_users
from activity
where (datediff('2019-07-27',activity_date) < 30 and activity_date <= '2019-07-27')-- 7월 27일도 포함한다 
and activity_type is not null -- 한 번이라도 활성 된 적이 있는 유저 
group by day 

# between 
select activity_date as day,
		count(distinct user_id) as active_users
from activity
where (activity_date between '2019-07-28' and '2017-07-27')
and activity_type is not null 
group by day