주간 SQL Study 6차시 (SolveSQL, Leetcode 문제풀이)

1️⃣ 멘토링 짝꿍 리스트

Q1. https://solvesql.com/problems/mentor-mentee-list/

solvesql

select
  e1.employee_id as mentee_id,
  e1.name as mentee_name,
  e2.employee_id as mentor_id,
  e2.name as mentor_name
from  employees e1, employees e2
where
  e1.join_date between '2021-09-01' and '2021-12-31'
  and e2.join_date <= '2019.12.31'
  and e1.department != e2.department
order by
  mentor_id

 

2️⃣ 데이터 그룹으로 묶기

Q2. https://solvesql.com/problems/group-by/

solvesql

 

SELECT quartet
     , round(avg(x),2) AS x_mean
     , round(variance(x),2) AS x_var
     , round(avg(y),2) AS y_mean
     , round(variance(y),2) AS y_var
FROM points
GROUP BY quartet;

 

3️⃣ Exchange Seats

Q3. https://leetcode.com/problems/exchange-seats/

Exchange Seats - LeetCode

 

-- 1번 풀이
SELECT CASE
           WHEN MOD(id,2) = 0 THEN id - 1
           WHEN MOD(id,2) = 1 AND id < (SELECT COUNT(*) FROM seat) THEN id + 1
           ELSE id END AS id
     , student
FROM seat
ORDER BY id;

-- 2번 풀이
SELECT ROW_NUMBER() OVER() AS id, student
FROM seat
ORDER BY CASE WHEN MOD(id, 2) = 1 THEN id + 1 ELSE id - 1 END;

4️⃣ Customers Who Never Order

Q4. https://leetcode.com/problems/customers-who-never-order/

Customers Who Never Order - LeetCode

SELECT name AS Customers
FROM customers LEFT JOIN orders o ON customers.id = o.customerId
WHERE o.id is null

 

5️⃣ Not Boring Movies

Q5. https://leetcode.com/problems/not-boring-movies/

Not Boring Movies - LeetCode

SELECT *
FROM Cinema
WHERE id % 2 = 1
AND description NOT LIKE '%boring%'
ORDER BY rating DESC