[SQL] - LEETCODE SQL 50

SQL 코딩테스트를 준비하면서 12월 1일부터 매일 새벽5시~8시 3시간동안 리트코드문제를 풀었다.

 

5문제를 제외한 모든문제를 풀어봤는데 Medium, Hard 가 생각보다 어렵다.

(아직 풀지못한 쿼리문이긴한데 조만간 풀어볼 예정이다.)

하루도 안빠지고 푼 나자신 아주 칭찬해.

 

 

글씨가 조금 악필이라...감안하고 봐주시길 바랍니다.

 

저는 모든문제를 손코딩으로 적으면서

문제내용 - 테이블 - 컬럼 - 문제파악과 어떤부분을 쿼리문으로 출력해야되는지

리스트를 먼저 만들어보고 순서와 서브쿼리를 어디에 둬야될지 등등

 

문제를 풀고 난뒤에 해석들까지도 적어두었다.

블로그에 작성하려고 하는거는 풀면서 어려웠던 오답노트를 적어볼까 한다.

 

LEETCODE - 1164 특정 날짜의 제품 가격(Medium)

https://leetcode.com/problems/product-price-at-a-given-date/description/

 

SELECT p0.product_id,
       COALESCE(p.new_price, 10) AS price
FROM (
    SELECT DISTINCT product_id
    FROM Products
) p0
LEFT JOIN Products p
  ON p.product_id = p0.product_id
 AND p.change_date = (
        SELECT MAX(change_date)
        FROM Products
        WHERE product_id = p0.product_id
          AND change_date <= '2019-08-16'
     )
ORDER BY p0.product_id;

 

문제풀이 : 특정 기준일(2019-08-16) 시점의 상품 가격을 구하기 위해,

먼저 Products 테이블에서 모든 product_id 목록을 만들고(DISTINCT),

각 상품에 대해 기준일 이전(또는 당일)인 변경 이력 중 가장 최근 날짜(MAX(change_date))를 찾는다.

그런 다음 그 날짜의 new_price를 LEFT JOIN으로 붙여 ‘해당 시점의 최신 가격’을 가져오며,

기준일 이전 변경 이력이 전혀 없는 상품은 조인 결과가 NULL이 되므로 COALESCE(new_price, 10)로 기본 가격 10을 반환한다. 즉, “기준일 이전의 마지막 변경값을 현재값으로 간주하고, 이력이 없으면 기본값을 적용”하는 패턴을

상관 서브쿼리 + LEFT JOIN으로 구현했다.

LEETCODE - 185 부서별 최고 연봉 3위(Medium)

https://leetcode.com/problems/department-top-three-salaries

SELECT
    Department,
    Employee,
    Salary
FROM (
    SELECT
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (
            PARTITION BY d.id
            ORDER BY e.salary DESC
        ) AS rnk
    FROM Employee e
    JOIN Department d
      ON e.departmentId = d.id
) t
WHERE rnk <= 3;

 

문제풀이 : 부서별로 급여를 내림차순 정렬한 뒤, 급여 값 기준으로 순위를 매기기 위해 DENSE_RANK()를 사용한다.

동점자는 같은 순위를 부여하므로, ‘상위 3개 급여’에 해당하는 직원이 여러 명일 경우 모두 포함된다.

윈도우 함수 결과는 WHERE에서 직접 필터링할 수 없으므로 서브쿼리(또는 CTE)에서 순위를 계산한 뒤

바깥 쿼리에서 rnk <= 3 조건으로 상위 3개 급여 그룹만 선택한다.

 

LEETCODE - 550 경기분석lV(Hard)

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

SELECT ROUND(
          COUNT(DISTINCT a.player_id) /
          (SELECT COUNT(DISTINCT player_id) FROM Activity) , 2)
          AS fraction
FROM Activity a
JOIN (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
) f
ON a.player_id = f.player_id
AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);

 

문제풀이 : “전체 유저 중 ‘첫 접속 다음 날에도 다시 접속한 유저’의 비율을 구하기 위해,

먼저 서브쿼리에서 player_id별 최초 접속일(MIN(event_date))을 구해 first_date로 만든다.

그 다음 원본 Activity와 조인할 때 event_date = DATE_ADD(first_date, INTERVAL 1 DAY) 조건을 걸어,

각 유저의 ‘최초 접속 다음 날’ 기록이 실제로 존재하는지만 확인한다.

이 조건을 만족하는 유저 수를 COUNT(DISTINCT a.player_id)로 세면 재방문(다음 날 접속) 유저 수가 되고,

이를 전체 유저 수 COUNT(DISTINCT player_id)로 나눠 비율을 계산한다.

마지막으로 ROUND(..., 2)로 소수 둘째 자리까지 반올림해 문제에서 요구하는 fraction 값을 출력한다.”

LEETCODE - 1193 월별 거래 내역 l(medium) 

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
    FROM Transactions
    GROUP BY month, country

 

문제풀이 : 월별·국가별 거래 집계를 만들기 위해, 먼저 DATE_FORMAT(trans_date, '%Y-%m')로

거래일을 ‘YYYY-MM’ 형태의 month로 변환해 같은 달 기준으로 묶을 수 있게 한다.

이후 GROUP BY month, country로 월/국가 단위의 그룹을 만들고, COUNT(*)로 전체 거래 건수(trans_count)를 집계한다.

승인된 거래만 따로 세기 위해 SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END)로

승인 건수(approved_count)를 계산하며, 전체 거래 금액 합은 SUM(amount)로 trans_total_amount를 구한다.

마지막으로 승인된 거래 금액만 합산하도록 SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END)를 사용해 approved_total_amount를 산출함으로써, “전체 vs 승인”을 같은 쿼리에서 조건부 집계로 한 번에 비교할 수 있게 만들었다.

 

2025년 12월 마지막날까지 SQL50을 끝내볼 예정이다.