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

 

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

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

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

 

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

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

---

 

1. Higher Than 75 Marks (해커랭크)

📌문제

• Query the Name of any student in STUDENTS who scored higher than  Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

📌풀이

SELECT name  
FROM students  
WHERE marks > 75  
ORDER BY  
  RIGHT(name, 3), -- 문자열의 오른쪽 3개만 추출
  id -- 마지막 3글자가 같다면 id 기준으로 정렬

💡배운 점

• 1) 문자열 자르기 함수
  • LEFT(문자열, 추출 길이) : 문자열 왼쪽부터 길이만큼 추출
  • RIGHT(문자열, 추출 길이) : 문자열 오른쪽부터 길이만큼 추출
  • SUBSTR (문자열, 시작 위치, 추출 길이) : 길이 생략 시 시작 위치부터 끝까지 추출

SELECT LEFT('20251231', 4);
-- 결과: 2025

SELECT RIGHT('20251231', 3);
-- 결과: 231

SELECT SUBSTR('20251231', 5, 2);
-- 결과: 12

• 2) ORDER BY에 정렬 조건 여러 개 사용 가능!
  • 첫 조건에서 동일 순서라면 다음 조건으로 정렬하게 됨

 

2. Weather Observation Station 15 (해커랭크)

📌문제

• Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than `137.2345`. Round your answer to 4 decimal places.

📌풀이

SELECT  
  ROUND(long_w, 4) 
  -- ROUND(lat_n, 4) 
FROM station  
WHERE  
  lat_n < 137.2345  
ORDER BY  
  lat_n DESC  
LIMIT 1

💡배운 점

• 1) 소수점 처리 함수
  • ROUND(숫자, 자릿수) : 숫자를 자릿수에 맞게 반올림
  • CEIL(숫자) : 숫자를 올림하여 정수로 변환
  • FLOOR(숫자) : 숫자를 내림하여 정수로 변환

 

3. Top Earners (해커랭크)

📌문제

• We define an employee's total earnings to be their monthly `salary*months` worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as  space-separated integers.

📌풀이

------ 풀이 1 ------
WITH employ_earnings AS(
    SELECT
        *,
        salary * months AS earnings
    FROM employee
)

SELECT
    earnings,
    COUNT(employee_id)
FROM employ_earnings
GROUP BY earnings
ORDER BY earnings DESC

------ 풀이 2 ------ MySQL은 버전 8.0 이상에서만 WITH문 쓸 수 있다고 해서 서브쿼리로 변경
SELECT
    earnings,
    COUNT(employee_id)
FROM (
    SELECT
        *,
        salary * months AS earnings
    FROM employee
) AS employ_earnings
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

------👨🏻‍🏫더 나은 풀이 ------
SELECT
    salary * months AS earnings, -- SELECT에서 연산한 컬럼을 바로 Groupby할 수 있음
    COUNT(*) -- 그냥 earnings마다 개수 다 세줘 
FROM employ_earnings
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

💡배운 점

• 1) SELECT에서 연산한 컬럼을 바로 GROUP BY에 넣을 수 있음!
  • 연산해서 컬럼 만들 때 꼭 with문이나 서브쿼리 안 써도 되니까, 최대한 간결하게 짜는 방법을 고민해보자!
• 2) MySQL은 버전 8.0 이상에서만 WITH문 쓸 수 있음

 

4. Type of Triangle (해커랭크)

📌문제

• Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:
• Equilateral: It's a triangle with 3 sides of equal length. (정삼각형)
  Isosceles: It's a triangle with 2 sides of equal length. (이등변)
  Scalene: It's a triangle with 3 sides of differing lengths. (부등변)
  Not A Triangle: The given values of A, B, and C don't form a triangle. (삼각형 성립 불가)

📌풀이

SELECT
    CASE
        WHEN A+B<=C OR B+C<=A OR C+A<=B THEN "Not A Triangle" -- 제일 먼저 와야 함!
        WHEN A=B AND B=C THEN "Equilateral"
        WHEN A=B OR B=C OR C=A THEN "Isosceles"
        WHEN A!=B AND B!=C AND C!=A THEN "Scalene" -- ELSE로 대체 가능
    END AS triangle_type
FROM triangles

💡배운 점

• 1) [리마인드] CASE문에서 WHEN절 순서 중요!
  • 조건 여러 개 쓸 때 앞 조건에서 걸린 row는 그 다음 조건에 안 걸림.
  • 그래서 조건이 포함관계일 경우에는 더 좁은 범위의 조건을 먼저 써야 함! (ex. 정삼각형을 이등변삼각형보다 먼저 씀)

 

5. Reformat Department Table  (리트코드 1179번)

📌문제

• Reformat the table such that there is a department id column and a revenue column for each month.
  Return the result table in any order.
  The result format is in the following example.

📌풀이

• id 기준으로 GROUP BY 쓸 생각을 못 했음
• 어차피 revenue 값 하나니까 MAX, MIN, AVG 등 다른 거 해도 될 것 같은데? → 되네!
  • GPT한테 물어봤을 땐 MAX로 풀이해줌 → 같은 (id, month)에 대한 revenue가 1개일 경우 단순히 MAX를 쓸 수 있고, 같은 (id, month)에 대해 revenue가 여러 개라면 상황에 따라 MAX, SUM, AVG 사용을 추천함
  • 아마 revenue값이 갱신되면 한 달 동안 쌓여서 제일 마지막(max) 값이 최종 값이니까 MAX를 기본적으로 얘기해준 게 아닐까 싶다

-- 쿼리 작성하는 목표, 확인할 지표 : 테이블 피봇팅
-- 쿼리 계산 방법 : CASE WHEN으로 각 month 컬럼에 집어넣기
-- 데이터의 특징 : (id,month) 쌍으로 묶여야 primary key가 됨
SELECT
    id, -- ex) 각 부서의 1월 매출, 각 부서의 2월 매출, ... 12월 매출
    SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS 'Jan_Revenue',
    SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS 'Feb_Revenue',
    SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS 'Mar_Revenue',
    SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS 'Apr_Revenue',
    SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS 'May_Revenue',
    SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS 'Jun_Revenue',
    SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS 'Jul_Revenue',
    SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS 'Aug_Revenue',
    SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS 'Sep_Revenue',
    SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS 'Oct_Revenue',
    SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS 'Nov_Revenue',
    SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS 'Dec_Revenue'
FROM department
GROUP BY id -- 부서id 기준으로 그룹화

💡배운 점

• 1) 테이블 피봇팅 = CASE문 & 집계함수 활용!
  • ex) 각 month 기준으로 revenue 묶고 싶음 = CASE문으로 month의 범주들을 나누고, 각 범주에 해당하면 revenue값을 가져오고, 아니면 NULL(또는 0)으로 처리하면 됨
  • 집계함수는 MAX, SUM, AVG 중 상황에 맞는 걸로!
    • 지금은 어차피 각 부서(id)마다 한 month에 revenue값이 하나 뿐이라 뭐든 상관없음
    • 근데 만약 같은 (id, month)에 대한 revenue가 여러 개이고, 그 평균값으로 피봇팅이 필요하면 AVG 써야겠지

---

💡배운 점 모아두기

1. 문자열 자르기 함수 LEFT, RIGHT, SUBSTR
2. ORDER BY에 조건 여러 개 사용 가능
3. 소수점 처리 함수 ROUND, CEIL, FLOOR
4. SELECT에서 연산한 컬럼을 바로 GROUP BY에 입력 가능
5. MySQL은 버전 8.0 이상에서 WITH문 사용 가능
6. CASE문에서 조건이 포함관계일 땐 좁은 범위의 조건을 먼저 입력
7. CASE WHEN ~ THEN ~ ELSE NULL을 활용해서 테이블 피봇팅 가능