[SQL] MySQL DATE_SUB, DATE_ADD

DATE_SUB/DATE_ADD

• 주어진 날짜에 지정된 날짜만큼 더하거나 빼는 함수

DATE_ADD(기준 날짜, INTERVAL ____)
DATE_SUB(기준 날짜, INTERVAL ____)

• INTERVAL 뒤에 들어갈 수 있는 단위들
  • 1 SECOND
  • 1 MINUTE
  • 1 HOUR
  • 1 DAY
  • 1 MONTH
  • 1 YEAR

DATE_SUB 이용한 문제 풀기

코드카타 98번 | LeetCode - Game Play Analysis IV

문제

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.

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

접근

이 문제의 관건은 (1) 처음 접속한 날의 (2) 바로 다음날 또 접속한 사람의 수를 구해야 한다는 것이다.

풀이

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
  )

• SELECT문의 내부에도 서브쿼리를 사용할 수 있다.
• 서브쿼리를 적재적소에 적절히 사용하는 것이 중요하다.