[LeetCode] 1141. User Activity for the Past 30 Days I - SQL

링크: https://leetcode.com/problems/user-activity-for-the-past-30-days-i/

Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
This table may have duplicate rows.
The activity_type column is an ENUM (category) 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 a solution 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 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일 동안의 active_user를 찾아라.

• 해당하는 날에 하나의 activity 라도 했다면 그 유저는 active

최종코드

SELECT 
    activity_date as day, 
    COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY activity_date

설명

FROM, WHERE, GROUP BY

FROM Activity
WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY activity_date

• Activity 테이블에서
• DATE_SUB 함수를 사용해 active_date 가 ‘2019-06-28’ 에서 ‘2019-07-2’ 사이에 있는 행만 선택.
• activity_date 로 GROUP BY 해서 동일한 날짜끼리 하나의 그룹으로 묶는다.

SELECT 절

SELECT 
    activity_date as day, 
    COUNT(DISTINCT user_id) as active_users

• 그룹화된 activty_date 와
• 고유한 user_id 의 개수를 세면 끝!