[SQL] 첫구매일 다음날 다시 방문한 고객 수 구하기 (Game Play Analysis IV)

문제

문제 링크 : https://leetcode.com/problems/game-play-analysis-iv/description/

 

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      |
+-----------+
Explanation: 
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

 

요약하면, 첫 이벤트 발생일 바로 다음날에도 이벤트를 발생시킨 유저 수를 구하여 전체 유저 수 대비 비율을 구하는 문제.

 

나의 정답 쿼리

SELECT ROUND
        (COUNT(CASE 
                     WHEN A.event_date = DATE_ADD(F.first_date, INTERVAL 1 day)
                     THEN A.event_date END) 
        / COUNT(DISTINCT A.player_id), 2) AS fraction
FROM Activity A
    JOIN
        (SELECT player_id, 
                MIN(event_date) AS first_date
        FROM Activity  
        GROUP BY player_id) AS F
    ON A.player_id = F.player_id

 

나는 JOIN문 안에서 유저별 첫 이벤트 발생일을 구해서 'first_date'로 지정했다. 

이를 메인쿼리 안에서 '첫 이벤트 발생일 + 1일' = '이벤트 발생일' 조건을 만족한 횟수를 구해서 비율을 구함! 

다른 사람의 쿼리

 

SELECT
  ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM
  Activity
WHERE
  (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (
    SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id
  )'

 

이 분은 JOIN 문을 쓰지 않고,

(1) WHERE절에서 다중컬럼 서브쿼리를,

(2) SELECT절 서브쿼리

사용해서 좀 더 간결한 쿼리를 완성했다.

 

이렇게도 생각해 볼 수 있구나 싶어서 정리해 두기!