[MySQL] 해커랭크, 리트코드 문제 풀이 (2)

 

SQL 쿼리 연습을 위해 다양한 문제를 풀어보고 있다.

다만, 대부분의 코딩테스트 플랫폼에서는 BigQuery 선택지가 없어서 MySQL 기준으로 연습하는 중이다.

(대부분의 문법이 비슷하지만 간혹 문법이나 정책이 다른 경우가 있는 듯하다)

 

아래와 같은 문제들을 기록해두고 복기하려고 한다.

• 어려워서 풀이에 실패한 문제
• 풀긴 했으나 좀 더 좋은 쿼리가 있었던 문제
• 몰랐던 문법이나 함수를 알게 된 문제
• 그 외 배울 점이 있었던 문제

---

1. Employees Earning More Than Their Managers (리트코드 181번)

📌문제

• Write a solution to find the employees who earn more than their managers.
  Return the result table in any order. The result format is in the following example.

📌풀이

• e2 별칭을 manager로 하면 좀 더 직관적임

---- 1) 처음에 JOIN만 해서 테이블 확인 ----
SELECT
    *
FROM employee AS e1
LEFT JOIN employee AS e2
ON e1.managerId = e2.id;

---- 2) WHERE로 조건 추가 ----
SELECT
    *
FROM employee AS e1
LEFT JOIN employee AS e2
ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;

---- 3) 필요한 컬럼만 출력 ----
SELECT
    e1.name AS Employee
FROM employee AS e1
LEFT JOIN employee AS e2
ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;

💡배운 점

• 1) 자기 자신을 조인할 수도 있다!! = Self JOIN이라고 부름!

 

 

2. Rising Temperature (리트코드 197번)

📌문제

• Write a solution to find all dates' `id` with higher temperatures compared to its previous dates (yesterday).

📌풀이

• 셀프 조인을 하되, 하루 전 날짜와 비교할 수 있도록 recordDdate 컬럼을 연산해서 조인!

---- 1) 셀프 조인 후 확인
SELECT
    *
FROM weather AS today
LEFT JOIN weather AS yesterday
ON today.recordDate = yesterday.recordDate + INTERVAL 1 DAY; --⭐이게 핵심

---- 2) WHERE 조건 추가 & 필요 컬럼만 출력
SELECT
    today.id AS Id
FROM weather AS today
LEFT JOIN weather AS yesterday
ON today.recordDate = yesterday.recordDate + INTERVAL 1 DAY
WHERE
    today.temperature > yesterday.temperature;

---- (참고) 꼼수 풀이도 가능..!
SELECT 
    *
FROM weather AS today
LEFT JOIN weather AS yesterday
ON today.id = yesterday.id+1 -- id가 순서대로 되어있으니까 그냥 그걸 쓰면 됨

/* 처음에 WITH문으로 풀어봤지만 실패
WITH weather_yesterday AS(
    SELECT 
        *,
        recordDate - INTERVAL 1 DAY AS yesterday
    FROM weather)

SELECT 
    *
FROM weather AS w
LEFT JOIN weather_yesterday AS wy
ON w.recordDate = wy.yesterday
-- WHERE w.temperature > wy.temperature
*/

💡배운 점

• 1) Date 타입의 연산 함수
  • DATE_ADD(기준 날짜, INTERVAL 1 DAY): 기준 날짜에서 1일 더하기
    • INTERVAL에는 DAY 외에도 SECOND, MINUTE, HOUR, MONTH, YEAR 다 가능!
  • DATE_SUB(기준 날짜, INTERVAL 1 DAY): 기준 날짜에서 1일 빼기
    
    • DATE_ADD(기준 날짜, INTERVAL -1 DAY)로도 써도 결과 동일 !
• 2) ON에서 연산도 가능함
  • 그냥 JOIN Key 두 개 연결하는 것만 되는 줄 알았는데, 아예 연산까지 할 수 있구나!

 

3. Symmetric Pairs (해커랭크)

📌문제

• You are given a table, `Functions`, containing two columns: X and Y.
• Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
  Write a query to output all such symmetric pairs in ascending order by the value of X.
  List the rows such that X1 ≤ Y1.

 

📌풀이

• 1차 시도 (실패)

--- 1) 일단 셀프조인해서 출력해봄
SELECT
    f1.x AS x1,
    f1.y AS y1,
    f2.x AS x2,
    f2.y AS y2
FROM functions AS f1
    LEFT JOIN functions AS f2
    ON f1.x = f2.y -- x1과 y2가 같은 조건으로 일단 연결
    
--- 2) WHERE 조건 추가해서, 일단 대칭쌍만 가져오는 데 성공
SELECT
    f1.x AS x1,
    f1.y AS y1,
    f2.x AS x2,
    f2.y AS y2
FROM functions AS f1
    LEFT JOIN functions AS f2
    ON f1.x = f2.y 
WHERE f1.y = f2.x -- x2와 y1이 같은 조건을 추가!

--- 3) x1 <= y1 조건을 추가하고, 답 형식에 맞게 정렬해서 출력
SELECT
    f1.x AS x1,
    f1.y AS y1 -- 1,2 컬럼만 출력
FROM functions AS f1
    LEFT JOIN functions AS f2
    ON f1.x = f2.y 
WHERE f1.y = f2.x AND f1.x <= f1.y -- x가 더 작은 행만 출력
ORDER BY x1 -- x1 기준 오름차순 정렬

• 2차 시도 (실패)
  • 자꾸 NULL 생겨서 (혹시나) INNER JOIN으로 변경하고,
  • 대칭 조건을 ON에다 한 번에 집어넣음 (gpt가 알려줘서 알게 됨)

SELECT
    f1.x AS x1,
    f1.y AS y1
FROM functions AS f1
    INNER JOIN functions AS f2
    ON f1.x = f2.y AND f1.y = f2.x -- 여기서 한 번에 대칭 조건 완성!
WHERE f1.x <= f1.y
ORDER BY x1

• 3차 시도 (성공)
  • 문제점 발견: x=y인 행들은 혼자라도 자기자신끼리 join 돼서 출력되어버림 (예를 들어 (20,20)도 두 쌍이 있어야 되는데, 그냥 한 쌍만 있어도 대칭 조건을 충족해버리는 오류가 발생)
  • ➡️ x=y인 경우는 행이 2개(중복) 존재하는지 체크해서 별도로 구하고, x≠y인 경우는 별도로 구해서 UNION으로 합치자!

--- 1) x=y인 경우: GROUP BY 활용해서 개수 체크 되는지 확인
SELECT
    x,
    y
FROM functions -- 이 경우는 JOIN도 필요 없음!
WHERE x=y
GROUP BY x
HAVING COUNT(y)=2;

--- 2) x≠y인 경우: ON에 x≠y 조건 추가해서 구함 → 7건만 나오네
SELECT
    f1.x AS x1,
    f1.y AS y1
FROM functions AS f1
INNER JOIN functions AS f2
ON f1.x = f2.y AND f1.y = f2.x AND f1.x != f1.y
WHERE f1.x <= f1.y -- 참고로, 그냥 여기서 등호 빼면 위에 != 조건 필요 없음
ORDER BY x1;

--- 3) 위 두 결과를 UNION으로 연결
SELECT
    x,
    y
FROM functions
WHERE x=y
GROUP BY x
HAVING COUNT(y)=2

UNION

SELECT
    f1.x AS x1,
    f1.y AS y1
FROM functions AS f1
INNER JOIN functions AS f2
ON f1.x = f2.y AND f1.y = f2.x
WHERE f1.x < f1.y
ORDER BY 1 -- 정렬은 마지막에만 사용 가능!

• 최종 정답 쿼리

SELECT
    x,
    y
FROM functions
WHERE x=y
GROUP BY x
HAVING COUNT(y)=2 -- 다시 풀 때 count(*)>1로 해봤는데 됐음

UNION

SELECT
    f1.x AS x1,
    f1.y AS y1
FROM functions AS f1
INNER JOIN functions AS f2
ON f1.x = f2.y AND f1.y = f2.x
WHERE f1.x < f1.y
ORDER BY 1 -- 또는 ORDER BY x
-- 단, f1.x는 안 됨. 컬럼 이름은 첫 번째 SELECT 구문의 것을 따르기 때문!

💡배운 점

• 1) ON에서 '비교' 연산도 가능함
• 2) GROUP BY 쓸 때 (SELECT에 패스하고) HAVING에만 집계함수 넣어도 작동함!
• 3) UNION 사용 시 주의사항
  •  ORDER BY는 마지막에만 사용 가능 (개별 쿼리마다 ORDER BY를 쓸 수는 없음)
  • 컬럼명은 첫 번째 SELECT 구문의 것을 따름

---

💡배운 점 모아두기

1. 자기 자신을 조인할 수도 있다 = Self JOIN
2. Date 타입의 연산 함수: DATE_ADD, DATE_SUB
3. ON에서 연산 가능 (사칙연산, 비교연산 다!)
4. GROUP BY 쓸 때 (SELECT에 패스하고) HAVING에만 집계함수 넣어도 작동함
5. UNION 사용 시 주의사항
   • ORDER BY는 마지막에만 사용 가능
   • 컬럼명은 첫 번째 SELECT 구문을 따라감