[LeetCode] SQL 50 문제 풀이

개요
- SQL 쿼리 작성 능력 함양을 위해 LeetCode에서 SQL 문제 풀이를 진행함
- 본문의 쿼리들은 MySQL/PostgreSQL을 기준으로 작성됨
출처
- https://leetcode.com/studyplan/top-sql-50/

570. Managers with at Least 5 Direct Reports

# 1st mine
WITH dir_reports AS (
    SELECT
        managerId,
        COUNT(managerId) AS reports_cnt
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(managerId) >= 5
)

SELECT
    name
FROM
    Employee e
JOIN dir_reports dr ON e.id = dr.managerId

# 2nd mine
SELECT
    name
FROM Employee e
JOIN (
    SELECT
        managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(managerId) >= 5) dr ON dr.managerId = e.id
    
# other's
SELECT m.name FROM Employee r
JOIN Employee m ON m.id = r.managerId
GROUP BY m.id
HAVING COUNT(r.id)>4

Q2. Employees Earning More Than Their Managers

# mine
SELECT
    e2.name AS Employee
FROM Employee e1
LEFT OUTER JOIN Employee e2 ON e1.id = e2.managerId
WHERE e1.salary < e2.salary

# other's
select e2.name as Employee from employee e1 join employee e2 on e1.id = e2.managerid where e2.salary>e1.salary;

Q3. Not Boring Movies

# mine
SELECT
    *
FROM Cinema
WHERE description <> 'boring' AND id % 2 = 1
ORDER BY rating DESC

# other's
Select *
From Cinema
Where id % 2 != 0 AND description not like "boring"
Order by rating desc;

Q4. Find Customer Referee

# mine
SELECT name
FROM Customer
WHERE (referee_id IS NULL) OR (referee_id != 2)

# other's
SELECT c.name
FROM Customer c
WHERE c.referee_id != 2
    OR c.referee_id is NULL

1193. Monthly Transactions Ⅰ

# mine
SELECT
    DATE_FORMAT(trans_date,'%Y-%m') AS 'month',
    country,
    COUNT(trans_date) AS trans_count,
    COUNT(CASE WHEN state='approved' THEN 1 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 DATE_FORMAT(trans_date,'%Y-%m'), country

# other's
select left(trans_date, 7 ) as month, 
country, 
count(id) as trans_count, 
sum(state = 'approved') as approved_count,
sum(amount) as trans_total_amount,
sum((state = 'approved')*amount) as approved_total_amount
from Transactions
group by month, country;

1174. Immediate Food Delivery Ⅱ ★
- 틀려서 gpt한테 물어봤더니 customer_id 까지 JOIN 조건에 걸어야 한다고 함
- 해당 고객의 첫 번째 주문 행에 접근해야 함

# mine
WITH first_orders AS (
    SELECT
        customer_id,
        MIN(order_date) AS first_order
    FROM
        Delivery
    GROUP BY customer_id
)

SELECT
    ROUND(SUM(CASE WHEN first_order = customer_pref_delivery_date THEN 1 ELSE 0 END) / COUNT(first_order) * 100, 2) AS immediate_percentage
FROM
    first_orders fo
LEFT OUTER JOIN Delivery d ON fo.first_order = d.order_date AND fo.customer_id = d.customer_id

# other's
WITH FirstOrder AS (
    SELECT
        customer_id,
        MIN(order_date) AS first_date
    FROM Delivery
    GROUP BY customer_id
)
# Filter out the earliest order
# Count the one that is immediate and divide by total earliest order
SELECT
    ROUND(SUM(
        CASE
            WHEN d.order_date = d.customer_pref_delivery_date THEN 1
            ELSE 0
        END
    ) * 100 / COUNT(*), 2) AS immediate_percentage
FROM Delivery d
INNER JOIN FirstOrder f ON 
    d.customer_id = f.customer_id AND
    d.order_date = f.first_date

550. Game Play Analysis Ⅳ ★
- 모르겠어서 중간에 gpt 한테 물어봄
- 나는 LEAD() 로 다음날 로그인을 찾았는데 그럴 필요 없이 첫 로그인 날짜 옆에다가 다음날 로그인 날짜만 붙이면 된다 함
- [첫 로그인] 기준도 댓글 보다가 알았음. 나는 로그인하고 다음날 연속해서 로그인한 날로 이해했는데 말그대로 [첫 로그인과 그 기준 다음날 로그인] 이었음. 이제 문제를 첨부터 번역기 돌려서 확인 후에 짜야겠다.
- DATE_ADD() 도 gpt가 사용하래서 썼음. 나는 [첫 로그인 일자 + 1] 로 구분했는데 함수로 쓰는게 더 안전하다 함.

# mine
WITH dates AS (SELECT
    player_id,
    MIN(event_date) AS first_login
FROM
    Activity
GROUP BY player_id), next_login AS (
SELECT
    a.player_id,
    first_login,
    event_date
FROM dates d
LEFT OUTER JOIN Activity a ON a.player_id = d.player_id
WHERE event_date = DATE_ADD(first_login, INTERVAL 1 DAY))

SELECT
    ROUND((SELECT COuNT(player_id)
FROM next_login) / COUNT(DISTINCT player_id),2) AS fraction
FROM Activity

# other's
# Write your MySQL query statement below
WITH first AS (
    SELECT player_id, MIN(event_date) as first_day
    FROM Activity
    GROUP BY player_id
)
,
users AS (
    SELECT COUNT(a.player_id) as users_imm
    FROM Activity a
    JOIN first on first.player_id = a.player_id AND a.event_date = DATE_ADD(first_day, INTERVAL 1 DAY)
)

SELECT
    ROUND((users_imm/COUNT(DISTINCT player_id)),2) as fraction
FROM Activity, users

# postgresql로 재풀이
SELECT
    ROUND(SUM(CASE WHEN a3.event_date IS NOT NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(f.player_id),2) AS fraction
FROM (SELECT
    a1.player_id AS player_id,
    a1.event_date AS first_date
FROM Activity a1
LEFT OUTER JOIN (SELECT
    player_id,
    MIN(event_date) AS first_date
FROM Activity
GROUP BY player_id) a2 ON a1.event_date = first_date AND a1.player_id = a2.player_id
WHERE a2.player_id IS NOT NULL) f
LEFT OUTER JOIN Activity a3 ON f.first_date + 1= a3.event_date AND f.player_id = a3.player_id


# other's
-- First Solution
WITH ranked AS (
    SELECT
        *,
        DENSE_RANK() OVER (
            PARTITION BY player_id
            ORDER BY event_date ASC
        ) AS date_index
    FROM
        Activity
),

qualified AS(
    SELECT
        DISTINCT player_id
    FROM
        ranked
    WHERE
        date_index <= 2
    GROUP BY
        player_id
    HAVING
        MIN(event_date) + 1 = MAX(event_date)
)

SELECT
    ROUND(
        (
            SELECT COUNT(*) FROM qualified
        ) / COUNT(DISTINCT player_id)::NUMERIC,
        2
    ) AS fraction
FROM
    Activity

1068. Product Sales Analysis Ⅰ ★
- JOIN 연습 많이 된다.. 스트레스 많이 받을거야...

# mine
SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales s
LEFT OUTER JOIN Product p ON s.product_id = p.product_id
GROUP BY s.sale_id, year

# other's
#show only product_name, year, and price column using SELECT, FROM two table using INNER JOIN, join them by their product_id column because that the ony column in both tables
SELECT product_name,year,price FROM sales INNER JOIN product ON sales.product_id=product.product_id

1789. Primary Department for Each Employee
- 솔루션에서 처음 보는 PostgreSQL 문법 DISTINCT ON (col) 이 있어 찾아봄
  - col 열에 대해 같은 여러 행이 있을 때, 그 그룹마다 맨 앞에 오는 1행만 남기고 나머지는 제거하라는 뜻임
  - 동작 순서
    1. employee_id를 기준으로 같은 값끼리 묶음
    2. 각 묶음 안에서 ORDER BY employee_id, primary_flag DESC 순서대로 정렬
    3. 각 employee_id 그룹에서 첫 번째 행만 선택
  - 즉, 같은 직원이 여러 부서에 속해 있을 수 있을 때 primary_flag가 더 큰 행을 우선으로 해서 그 부서만 남기려는 의도라고 함
  - MySQL/SQL Server에서는 보통 ROW_NUMBER()로 대체할 수 있다고 함

# mine
SELECT 
    employee_id,
    department_id
FROM (SELECT
    employee_id,
    CASE
        WHEN primary_flag = 'Y'
            THEN department_id
            ELSE
                CASE WHEN
                    COUNT(department_id) OVER (PARTITION BY employee_id) = 1
                        THEN department_id
                        ELSE 0
                END
    END AS department_id
FROM
    Employee) a
WHERE department_id > 0

# other's
SELECT DISTINCT ON (employee_id)
    employee_id,
    department_id
FROM Employee
ORDER BY employee_id, primary_flag DESC

610. Triangle Judgement
- 우와.. 나는 가장 긴 변을 알아야 한다고만 외웠는데 모든 변에 대해서 마찬가지였음
- 그래서 AND 도 엮으면 조건 한 줄로 확인할 수 있음

SELECT
    *,
    CASE
        WHEN x < y+z AND y < x+z AND z < x+y THEN 'Yes'
        -- WHEN x >= y AND y >= z AND x < y+z THEN 'Yes'
        -- WHEN y >= x AND x >= z AND y < x+z THEN 'Yes'
        -- WHEN z >= x AND x >= y AND z < x+y THEN 'Yes'
        -- WHEN y >= z AND z >= x AND y < x+z THEN 'Yes'
        -- WHEN x >= z AND z >= y AND x < y+z THEN 'Yes'
        -- WHEN z >= y AND y >= x AND z < x+y THEN 'Yes'
        ELSE 'No' END AS triangle 
FROM Triangle

1164. Product Price at a Given Date
- other's 쿼리의 구조가 더 깔끔하다고 함.
  - 먼저 상품 목록만 추출
  - 그다음 기준일 이전의 가장 최근 날짜만 계산
  - 그 날짜에 해당하는 가격만 조인
  - 마지막에 기본값 10 처리
- "상품별 1행" 구조를 유지하려는 방향이라서 보통 더 효율적이라고 함
- 내 쿼리는 불필요한 행 증폭이 있어서 other's가 더 좋은 평가를 받을거라고 .. ㅠㅠ ...

# mine
SELECT DISTINCT
    p.product_id,
    COALESCE(ids.new_price,10) AS price
FROM Products p
LEFT OUTER JOIN 
    (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS latest_price
        FROM Products p
        WHERE change_date <= DATE('2019-08-16')
    ) ids
ON p.product_id = ids.product_id
WHERE latest_price = 1 OR latest_price IS NULL

# other's
with uniqueproducts as (
	select distinct
    	product_id
    from products
), latestchangedate as (
	select
    	product_id,
        max(change_date) as change_date
    from products
    where change_date <= date '2019-08-16'
    group by product_id
), latestchangeprice as (
	select
    	t1.product_id,
    	t1.new_price
    from products as t1
    inner join latestchangedate as t2
    on t1.product_id = t2.product_id and t1.change_date = t2.change_date
)

select
	t1.product_id,
    coalesce(t2.new_price, 10) as price
from uniqueproducts as t1
left join latestchangeprice as t2
on t1.product_id = t2.product_id

626. Exchange Seats
- 아우 .. 오래 걸렸다 ...
- sql 에서 새로운 행을 만들어서 GROUP 짓거나 인덱스를 바꾸는건 아직 헷갈린다 ...

# mine
SELECT
    CASE WHEN id % 2 = 0 THEN id - 1
        ELSE
            CASE WHEN id = (SELECT MAX(id) FROM Seat) THEN id
            ELSE id + 1 END
        END AS id,
    student
FROM
    Seat
ORDER BY id

1341. Movie Rating

# mine
WITH user1 AS (
    SELECT
        r.user_id,
        u.name,
        COUNT(r.user_id)
    FROM MovieRating r
    LEFT OUTER JOIN Users u using (user_id)
    GROUP BY r.user_id, u.name
    ORDER BY COUNT(r.user_id) DESC, u.name
    LIMIT 1
), mv1 AS (
    SELECT
        r.movie_id,
        m.title,
        AVG(rating)
    FROM MovieRating r
    LEFT OUTER JOIN Movies m using (movie_id)
    WHERE created_at BETWEEN DATE('2020-02-01') AND DATE('2020-02-29')
    GROUP BY r.movie_id, m.title
    ORDER BY AVG(rating) DESC, m.title
    LIMIT 1
)

SELECT
    name AS results
FROM user1
UNION ALL
SELECT
    title AS results
FROM mv1

# other's
(
    SELECT u.name AS results
    FROM MovieRating mr
    JOIN Users u ON mr.user_id = u.user_id
    GROUP BY u.name
    ORDER BY COUNT(mr.movie_id) DESC, u.name ASC
    LIMIT 1
)
UNION ALL
(
    SELECT m.title AS results
    FROM MovieRating mr
    JOIN Movies m ON mr.movie_id = m.movie_id
    WHERE TO_CHAR(mr.created_at, 'YYYY-MM') = '2020-02'
    GROUP BY m.title
    ORDER BY AVG(mr.rating) DESC, m.title ASC
    LIMIT 1
);

1321. Restaurant Growth
- 윈도우 함수 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 는 사용법이 기억이 안나서 구글링함
- sqld 할 때 공부했던 것들 이용해서 풀면 뭔가 신기하고 뿌듯함..

# mine
WITH init_sum AS (
    SELECT
        visited_on,
        SUM(amount) AS sum_per_date
    FROM
        Customer
    GROUP BY visited_on
)

SELECT
    visited_on,
    SUM(moving_sum) AS amount,
    ROUND(SUM(moving_sum) / 7.0, 2) AS average_amount
FROM
    (
    SELECT
        visited_on,
        LAG(visited_on, 6) OVER (ORDER BY visited_on) AS moving,
        sum_per_date,
        SUM(sum_per_date) over (ORDER BY visited_on
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_sum
    FROM
        init_sum
    )
WHERE moving IS NOT NULL
GROUP BY visited_on
ORDER BY visited_on

# other's
-- Write your PostgreSQL query statement below
select visited_on, 
sum(amount) over (order by visited_on rows between 6 preceding and current row) as amount, 
round(avg(amount) over (order by visited_on rows between 6 preceding and current row),2) as average_amount
from
(
    select visited_on, sum(amount) as amount
    from Customer
    group by visited_on
)
offset 6 rows;

602. Friend Requests II: Who Has the Most Friends
- 간단한건데 오래 걸렸다..
- 이런 linked list, graph 같은건 중심(주체)을 교차해서 조인하고 카운트..!

# mine
SELECT
    id,
    COUNT(friends) AS num
FROM
    (SELECT
        requester_id AS id,
        accepter_id AS friends
    FROM
        RequestAccepted
    UNION
    SELECT
        accepter_id AS id,
        requester_id AS friends
    FROM RequestAccepted)
GROUP BY id
ORDER BY num DESC
LIMIT 1

# other's
with m as(
select requester_id, accepter_id from RequestAccepted
union all
select accepter_id,requester_id from RequestAccepted
),
x as(
select requester_id,count(*) as co from m group by 1
)
select x.requester_id as id,x.co as num from x
order by 2 desc limit 1

585. Investments in 2016

# mine
SELECT
    ROUND(SUM(tiv_2016)::numeric, 2) AS tiv_2016
FROM (SELECT
    pid,
    tiv_2016,
    COUNT(tiv_2015) OVER (PARTITION BY tiv_2015) AS cnt_2015,
    COUNT((lat, lon)) OVER (PARTITION BY (lat, lon)) AS cnt_city
FROM
    Insurance)
WHERE cnt_2015 > 1 AND cnt_city = 1

1527. Patients With a Condition
- 처음에 틀려서 복잡하게 생각했음
- 띄어쓰기를 구별하여 첫 시작을 잡아내면 됨

# mine
SELECT
    *
FROM
    Patients
WHERE conditions ~ '^DIAB1[0-9]| DIAB1[0-9]| DIAB1 '

# other's
# conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%'

1484. Group Sold Products By The Date
- STRING_AGG() 함수 구글링해서 풀었음

SELECT
    sell_date,
    COUNT(DISTINCT product) AS num_sold,
    STRING_AGG(DISTINCT product, ',') AS products
FROM
    (SELECT *
    FROM Activities
    ORDER BY sell_date, product)
GROUP BY sell_date
ORDER BY sell_date

1517. Find Users With Valid E-Mails
- 아우 ... 정규식 잘 모르겠어서 구글링했음

# mine
SELECT
    *
FROM Users
WHERE mail ~ '^[A-Za-z]+[A-Za-z0-9.\-_]*@leetcode\.com$'

# other's
SELECT user_id, name, mail
FROM Users
WHERE mail ~ '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\.com$';

1907. Count Salary Categories
- 구글링해서 풀긴 풀었다
- 이런 문제 전에도 1번 풀어본 적 있는데 어케 풀었는지 기억이 안나는데 확실한건 전에는 unnest()로 풀지 않았음
- union으로 풀고 싶지 않았는데 이게 더 좋은 방법이었으려나 ..

# mine
WITH structure AS (SELECT
    UNNEST(ARRAY['Low Salary',
    'High Salary',
    'Average Salary']) AS category
)

SELECT
    s.category,
    COUNT(a.category) AS accounts_count
FROM structure s
LEFT OUTER JOIN (SELECT
    account_id,
    CASE WHEN income < 20000 THEN 'Low Salary'
     WHEN income > 50000 THEN 'High Salary'
     WHEN income >= 20000 AND income <= 50000 THEN  'Average Salary' END AS category
FROM Accounts) a
ON s.category = a.category
GROUP BY s.category

# 1# others
SELECT 
    v.category, 
    COUNT(a.account_id) AS accounts_count
FROM (
    VALUES ('Low Salary'), ('Average Salary'), ('High Salary')
) AS v(category)
LEFT JOIN Accounts a ON 
    CASE 
        WHEN a.income < 20000 THEN 'Low Salary'
        WHEN a.income > 50000 THEN 'High Salary'
        ELSE 'Average Salary'
    END = v.category
GROUP BY v.category;

# 2# others
with t as (
    SELECT 
        COUNT(*) FILTER (WHERE income<20000) AS "Low Salary", 
        COUNT(*) FILTER (WHERE income>=20000 and income<=50000) AS "Average Salary",
        COUNT(*) FILTER (WHERE income>50000) AS "High Salary" 
    FROM Accounts
)
SELECT 'Low Salary' AS category, "Low Salary" AS accounts_count
FROM t
UNION ALL
SELECT 'Average Salary', "Average Salary"
FROM t
UNION ALL
SELECT 'High Salary', "High Salary"
FROM t

196. Delete Duplicate Emails
- 이틀을 고민했는데 문득 ROW_NUMBER()가 떠올라서 풀었다
- 다른 사람들 풀이에서 MIN() 쓰는걸 보고 아차 싶다..

# mine
DELETE FROM Person
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS dup_rank
        FROM Person
    )
    WHERE dup_rank > 1
    
 # 1# others
DELETE FROM Person
WHERE Person.id NOT IN (
    SELECT MIN(id) FROM Person GROUP BY email
)

# 2# others
-- Write your PostgreSQL query statement below
DELETE FROM Person p1
USING Person p2
WHERE p1.email = p2.email
AND p1.id > p2.id

176. Second Highest Salary
- 아 며칠동안 고민했다
- 아무 행도 없을 때 + 한 행만 있을 때 + 여러 행 있을 때 null을 default로 정의하는게 어려웠다
- 다른 사람들의 솔루션을 보니 허무하다

# mine
WITH r1 AS (SELECT
    *,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS ranks
FROM Employee)

SELECT
    CASE WHEN salary IN ((SELECT salary FROM r1 WHERE ranks = 2)) THEN salary ELSE NULL END AS SecondHighestSalary
FROM r1
UNION ALL

SELECT NULL AS SecondHighestSalary
WHERE NOT EXISTS (SELECT
    CASE WHEN salary IN ((SELECT salary FROM r1 WHERE ranks = 2)) THEN salary ELSE NULL END AS SecondHighestSalary
FROM r1
ORDER BY SecondHighestSalary
LIMIT 1)
ORDER BY SecondHighestSalary
LIMIT 1

# 1# other's
select max(salary) as secondhighestsalary
from Employee
where salary <(select max(salary) from Employee) 

# 2# other's
SELECT (
    SELECT salary
    FROM (
        SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
        FROM Employee
    ) AS ranked
    WHERE rnk = 2
    LIMIT 1
) AS SecondHighestSalary

# 3# other's
SELECT
  COALESCE((
      SELECT DISTINCT e.salary
        FROM employee AS e
       ORDER BY e.salary DESC
       LIMIT 1 OFFSET 1
    ),
    NULL
  ) AS SecondHighestSalary;

'Study > SQL' 카테고리의 다른 글

[DataLemur] SQL 문제 풀이 - Medium 레벨 (1) (0)	2026.07.03
[HackerRank] SQL 문제 풀이 - Medium 레벨 (1) (0)	2026.07.01
[sql-practice] SQL 문제 풀이 (6) (0)	2026.04.03
[sql-practice] SQL 문제 풀이 (5) (0)	2026.04.03
[sql-practice] SQL 문제 풀이 (4) (0)	2026.01.31

#545

About

Categories

[LeetCode] SQL 50 문제 풀이

'Study > SQL' 카테고리의 다른 글

티스토리툴바

[LeetCode] SQL 50 문제 풀이

'Study > SQL' 카테고리의 다른 글

'Study/SQL' Related Articles

티스토리툴바