🧑🏻💻 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 |
+---------------+---------+
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년 7월 27일에서 activity_date를 뺀 날짜가 30일 미만인 날짜들 중 활동한 user_id의 active_users가 몇명인지 반환하는 쿼리를 작성하면 된다.
그렇기 때문에 저번에 언급했던 날짜 관련 쿼리를 활용하여 구현해주면 된다.
여기서는 날짜 쿼리를 쓰지 않은 내 방법과 날짜 쿼리를 쓴 방법을 알아보도록 하겠다.
날짜 쿼리 사용 X
SELECT activity_date AS day, COUNT(DISTINCT(user_id)) AS active_users
FROM Activity
# WHERE절에서 날짜 사이의 간격으로 차이를 표시했다. (BETWEEN을 사용해도 좋다.)
WHERE (activity_date > '2019-06-27' AND activity_date < '2019-07-28')
GROUP BY activity_date;날짜 쿼리 사용 O
SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
# 19년 7월 27일에서 activity_date를 DATEDIFF로 빼주어 표현하였다.
WHERE DATEDIFF("2019-07-27", activity_date) < 30
GROUP BY activity_date;이외 다양한 날짜 함수를 사용하여 이를 구현할 수 있다.
📊 Daily Leads and Partners
Table: DailySales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| date_id | date |
| make_name | varchar |
| lead_id | int |
| partner_id | int |
+-------------+---------+
This table does not have a primary key.
This table contains the date and the name of the product sold and the IDs of the lead and partner it was sold to.
The name consists of only lowercase English letters.
Write an SQL query that will, for each date_id and make_name, return the number of distinct lead_id's and distinct partner_id's.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
DailySales table:
+-----------+-----------+---------+------------+
| date_id | make_name | lead_id | partner_id |
+-----------+-----------+---------+------------+
| 2020-12-8 | toyota | 0 | 1 |
| 2020-12-8 | toyota | 1 | 0 |
| 2020-12-8 | toyota | 1 | 2 |
| 2020-12-7 | toyota | 0 | 2 |
| 2020-12-7 | toyota | 0 | 1 |
| 2020-12-8 | honda | 1 | 2 |
| 2020-12-8 | honda | 2 | 1 |
| 2020-12-7 | honda | 0 | 1 |
| 2020-12-7 | honda | 1 | 2 |
| 2020-12-7 | honda | 2 | 1 |
+-----------+-----------+---------+------------+
Output:
+-----------+-----------+--------------+-----------------+
| date_id | make_name | unique_leads | unique_partners |
+-----------+-----------+--------------+-----------------+
| 2020-12-8 | toyota | 2 | 3 |
| 2020-12-7 | toyota | 1 | 2 |
| 2020-12-8 | honda | 2 | 2 |
| 2020-12-7 | honda | 3 | 2 |
+-----------+-----------+--------------+-----------------+
Explanation:
For 2020-12-8, toyota gets leads = [0, 1] and partners = [0, 1, 2] while honda gets leads = [1, 2] and partners = [1, 2].
For 2020-12-7, toyota gets leads = [0] and partners = [1, 2] while honda gets leads = [0, 1, 2] and partners = [1, 2].✅ 해답
여기서는
date_id & make_name 기준으로 lead_id와 partner_id의 갯수를 반환하는 쿼리를 작성 하면 되는 간단한 문제이다.
(사실 이번 문제는 문제 구문보다는 Output 테이블을 잘 봐야한다...)
alias만 잘 붙혀주고 GROUP BY에 신경쓰도록 하자!
SELECT date_id, make_name,
COUNT(DISTINCT(lead_id)) AS unique_leads,
COUNT(DISTINCT(partner_id)) AS unique_partners
FROM DailySales
GROUP BY date_id, make_name;🕵🏻 Find Followers Count
Table: Followers
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| follower_id | int |
+-------------+------+
(user_id, follower_id) is the primary key for this table.
This table contains the IDs of a user and a follower in a social media app where the follower follows the user.Write an SQL query that will, for each user, return the number of followers.
Return the result table ordered by user_id.
The query result format is in the following example.
Example 1:
Input:
Followers table:
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0 | 1 |
| 1 | 0 |
| 2 | 0 |
| 2 | 1 |
+---------+-------------+
Output:
+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0 | 1 |
| 1 | 1 |
| 2 | 2 |
+---------+----------------+
Explanation:
The followers of 0 are {1}
The followers of 1 are {0}
The followers of 2 are {0,1}✅ 해답
여기서는
user_id별로 follower_id의 카운트를 반환하는 쿼리를 작성하면 된다.
SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;이번 Day 7는 첫번째 문제빼고는 쉬어가는 시간이라 생각해도 좋을 것 같다.
그러면 다음에는 Day 8에 대하여 알아보도록 하자.
