550. Game Play Analysis IV
1. 문제
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id, event_date) is the primary key (combination of columns with unique values) of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.
Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
The result format is in the following example.
Example 1:
Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Output:
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+
2. 풀이
SELECT ROUND(count(distinct(b.player_id)) / count(distinct(a.player_id)),2) as fraction
FROM Activity a
LEFT JOIN Activity b
ON a.player_id = b.player_id
-- AND a.event_date = b.event_date
AND a.event_date = DATE_ADD(b.event_date, INTERVAL -1 DAY)
첫 풀이는 이거였다.
문제 예시에 나와있는 데이터로는 의도한 결과값이 나오지만 채점을 눌렀더니
여러 개의 테스트 케이스들 중에서 절반만 통과가 되는 거다.
원인을 한 참 고민했는데, first_login 날짜를 고려 안했던 것이다.
특정 유저가 3/1, 3/4, 3/5 에 로그인 했다면 이 유저도 결과값에 반영된다.
이 문제에서는 첫로그인 다음날에 로그인 한 유저를 찾고자 한다.
유저별 첫 로그인 날짜 정보를 담고 있는 CTE를 만들어서 수정했다.
WITH first_login AS
(SELECT player_id, MIN(event_date) as event_date
FROM Activity
GROUP BY player_id
)
SELECT ROUND(COUNT(a.event_date)/COUNT(b.event_date),2) as fraction
FROM Activity a
RIGHT JOIN first_login b
ON a.player_id = b.player_id
AND a.event_date = DATE_ADD(b.event_date, INTERVAL 1 DAY)
3. TIL
1. WHERE 절에 여러 조건을 담을 때 IN을 사용할 수 있다.
# WHERE (a,b) IN (c,d)
SELECT *
FROM table1
WHERE (column1, column2) IN (
SELECT column3, column4
FROM table2
);
# WHERE a=c AND b=d
SELECT *
FROM table1
WHERE EXISTS (
SELECT 1
FROM table2
WHERE table1.column1 = table2.column3
AND table1.column2 = table2.column4
);
2356. Number of Unique Subjects Taught by Each Teacher
1. 문제
Table: Teacher
+-------------+------+
| Column Name | Type |
+-------------+------+
| teacher_id | int |
| subject_id | int |
| dept_id | int |
+-------------+------+
(subject_id, dept_id) is the primary key (combinations of columns with unique values) of this table.
Each row in this table indicates that the teacher with teacher_id teaches the subject subject_id in the department dept_id.
Write a solution to calculate the number of unique subjects each teacher teaches in the university.
Return the result table in any order.
The result format is shown in the following example.
Example 1:
Input:
Teacher table:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 3 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
+------------+------------+---------+
Output:
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1 | 2 |
| 2 | 4 |
+------------+-----+
2. 풀이
SELECT teacher_id, COUNT(distinct subject_id) as cnt
FROM Teacher
GROUP BY teacher_id1141. User Activity for the Past 30 Days I
1. 문제
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.
2. 풀이
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
3. TIL
- BETWEEN A AND B 이면 B 날짜를 포함하므로, n개의 날짜를 가져오려면 INTERVAL n-1개 설정해주기
1070. Product Sales Analysis III
1. 문제
Table: Sales
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.
Table: Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.
Write a solution to select the product id, year, quantity, and price for the first year of every product sold.
Return the resulting table in any order.
2. 문제
WITH a as(
SELECT product_id,
year,
MIN(year) OVER (PARTITION BY product_id) as first_year, quantity, price
FROM Sales)
SELECT product_id, first_year, quantity, price
FROM a
WHERE year = first_year