550. Game Play Analysis IV / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

Write an SQL query 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.

 

 

제출답안(MS SQL Server)

WITH
  log_in_consec
    AS (SELECT 
          *, 
          ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS event_num,
          LEAD(event_Date) OVER (PARTITION BY player_id ORDER BY event_date) AS next_login_date
        FROM 
          activity),

  log_in_is_consec
    AS (SELECT 
          player_id,
          CASE 
            WHEN DATEDIFF(DAY, event_date, next_login_date)=1 THEN 1.0
            ELSE 0.0 END AS is_consecutive
        FROM
          log_in_consec
        WHERE
          event_num = 1)

SELECT ROUND(AVG(is_consecutive), 2) AS fraction
FROM log_in_is_consec

 

 

 

풀이(MS SQL Server)

CTE1: player_id 기준으로 각 행의 행번호와 다음 행의 값을 불러옴

WITH
  log_in_consec
    AS (SELECT 
          *, 
          ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS event_num,
          LEAD(event_Date) OVER (PARTITION BY player_id ORDER BY event_date) AS next_login_date
        FROM 
          activity),

 

CTE2: 각 아이디의 첫 로그인 데이터만 불러옴

  log_in_is_consec
    AS (SELECT 
          player_id,
          CASE 
            WHEN DATEDIFF(DAY, event_date, next_login_date)=1 THEN 1.0
            ELSE 0.0 END AS is_consecutive
        FROM
          log_in_consec
        WHERE
          event_num = 1)

>> CASE WHEN 구절의 결과값은 1(Integer) 이 아니라 1.0(Float) 형식으로 적어주었음

>> Integer 형태로 할 경우 이후 AVG를 사용할 때 결과값이 Integer로 산출됨

>> 하지만 해당 쿼리 결과는 Float로 나와야하기 때문에 1.0, 0.0을 입력하였음

 

평균 값 산출

SELECT ROUND(AVG(is_consecutive), 2) AS fraction
FROM log_in_is_consec

 

 

 

문제 바로가기(MS SQL Server)