240908_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

문제 링크

WITH a
     AS (SELECT player_id,
                event_date,
                Sum(games_played) AS "sum_of_game"
         FROM   activity
         GROUP  BY 1,
                   2)
SELECT player_id,
       event_date,
       Sum(sum_of_game)
         OVER(
           partition BY player_id
           ORDER BY event_date ASC) AS "games_played_so_far"
FROM   a;

문제 링크
left join + where 조건(is not null)을 쓸 필요 없이
그냥 (inner) join으로 풀면 쉽게 답이 나온다.
CTE를 써서 푸는 것도 가능하지만 그냥 서브쿼리로 해결함.

SELECT Round(Count(DISTINCT f.player_id) / (SELECT Count(DISTINCT player_id)
                                            FROM   activity), 2) AS "fraction"
FROM   (SELECT player_id,
               Min(event_date) AS "first_login"
        FROM   activity
        GROUP  BY 1) f
       JOIN activity a
         ON f.player_id = a.player_id
            AND Datediff(a.event_date, f.first_login) = 1;

문제 링크
이걸 window 함수 없이 풀 수가 있나...?
내일 다시 한번 해봐야겠다.

WITH result
     AS (SELECT id,
                company,
                salary,
                Row_number()
                  OVER(
                    partition BY company
                    ORDER BY salary) AS "row_id"
         FROM   employee)
SELECT id,
       r.company,
       salary
FROM   result r
       JOIN (SELECT company,
                    Count(*) AS "cnt"
             FROM   employee
             GROUP  BY 1) b
         ON r.company = b.company
WHERE  CASE
         WHEN cnt%2 = 0 THEN row_id IN ( cnt / 2, ( cnt / 2 ) + 1 )
         WHEN cnt%2 = 1 THEN row_id IN ( Ceil(cnt / 2) )
       END;