[LeetCode] 550. Game Play Analysis IV - SQL

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

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

---

문제풀이

목표: 처음 로그인한 날 바로 다음 날 연이어 로그인한 플레이어의 비율을 소수점 이하 두 자리로 반올림하라.

• 첫 번째 로그인한 날짜부터 시작하여 최소 2일 연속 로그인한 날을 세서, 총 플레이어의 수로 나눠야 한다.

최종코드

SELECT 
	ROUND(SUM(login)/COUNT(DISTINCT player_id), 2) AS fraction
FROM (
  SELECT
    player_id,
    DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 AS login
  FROM Activity
) AS t

설명

서브쿼리

SELECT
    player_id,
    DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 'login'
FROM Activity

• Activity 테이블에서
• player_id 와
• 윈도우 함수를 사용하여 현재 날짜와 첫 번째 로그인 날짜와의 차이가 1인 것을 구한다.
  • 차이가 1이면 1을 반환
  • 1이 아니라면 0을 반환

메인쿼리 SELECT 절

SELECT 
    ROUND(SUM(login)/COUNT(DISTINCT player_id), 2) AS fraction

• 서브쿼리에서 구한 ‘login’ 의 합을 구하고, player_id의 개수로 나눠 소수점 둘째 자리까지 나타내면 끝!