SQL | Employees Whose Manager Left the Company, Exchange Seats, Movie Rating, Restaurant Growth, Friend Requests II: Who Has the Most Friends

https://leetcode.com/problems/employees-whose-manager-left-the-company/description/

---

Employees Whose Manager Left the Company

내가 쓴 답 (777ms): 

# 급여 30000 미만
# 관리자 없는 직원 (관리자 id는 있지만 직원 id에는 없는)

SELECT
    employee_id
FROM
    Employees
WHERE 
    manager_id NOT IN (SELECT employee_id FROM Employees) 
    AND salary < 30000
ORDER BY 1;

 

---

다른 사람들이 쓴 답 (279ms): 

# Write your MySQL query statement below
SELECT employee_id
FROM Employees
WHERE salary < 30000
AND manager_id IS NOT NULL
AND manager_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id

- 띄어쓰기 차이인가..?

---

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

 

---

Exchange Seats

내가 쓴 답 (515ms) : 

# 연속된 두 명 좌석 아이디 교환, 수가 홀수면 마지막은 교환 안 함
# id 기준 오름차순
SELECT
    CASE WHEN id % 2 = 0 THEN id-1	# 짝수일 경우 한 칸 위로
         WHEN id % 2 != 0 AND (SELECT COUNT(*) FROM Seat) = id THEN id	# 수가 홀수인 경우 유지
         ELSE id+1 END AS id,		# 홀수일 경우 한 칸 아래로
    student
FROM
    Seat
ORDER BY 1;

---

다른 사람이 쓴 답 (294ms) : 

# Write your MySQL query statement below
SELECT 
    id,
    CASE
        WHEN id % 2 = 0 THEN LAG(student) OVER(ORDER BY id)
        ELSE COALESCE(LEAD(student) OVER(ORDER BY id), student)
    END AS student
FROM Seat

- 짝수인 경우 이전 student 값 가져옴

- 아닐 경우(홀수인 경우) 다음 student 값 가져옴 , null일 경우(마지막이 홀수 일 경우) student 값 가져옴

---

https://leetcode.com/problems/movie-rating/description/

---

Movie Rating

내가 쓴 답 (1147ms) : 

# 영화 평가 가장 많이 한 사람, 동점일 경우 사전순 
# February 2020에서 가장 높은 평균 영화 이름, 동점일 경우 사전순 
(SELECT
    u.name AS results 
FROM
    USers u JOIN MovieRating mr ON u.user_id = mr.user_id
GROUP BY 1
ORDER BY COUNT(*) DESC, u.name
LIMIT 1)

UNION ALL

(SELECT
    m.title AS results
FROM
    Movies m JOIN MovieRating mr ON m.movie_id = mr.movie_id
WHERE mr.created_at LIKE '2020-02%'
GROUP BY m.title
ORDER BY AVG(mr.rating) DESC, m.title
LIMIT 1)

📍 괄호를 빼먹지 말 것

---

다른 사람이 쓴 답 (978ms) : 

# Write your MySQL query statement below
(select name as results 
from Users u join MovieRating mr on u.user_id = mr.user_id 
group by u.name
order by count(*) desc, name limit 1)

union all

(select m.title as results 
from Movies m join MovieRating mr on m.movie_id = mr.movie_id
where year(mr.created_at) = '2020' and month(mr.created_at) = '02'
group by title
order by avg(rating) desc, title limit 1)

---

https://leetcode.com/problems/restaurant-growth/description/

---

Restaurant Growth

내가 쓴 답 (729ms) : 

1) 이동 평균 구하기

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,
    ROW_NUMBER() OVER(ORDER BY visited_on) AS rownum
FROM
    (SELECT
        visited_on,
        SUM(amount) AS amount
    FROM Customer
    GROUP BY 1) a
ORDER BY 1)

- ROW_NUMBER로 순서를 매김 (7일부터 조회하기 위해서)

- 다른 방법이 있을 것 같긴 한데.. 일단 이렇게 구함

 

2) 7일부터 조회

# 고객이 7일 동안 지불한 금액의 이동 평균, 소수점 두 자리까지 반올림
# visited_on 기준 오름차순
WITH a AS(
    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,
        ROW_NUMBER() OVER(ORDER BY visited_on) AS rownum
    FROM
        (SELECT
            visited_on,
            sum(amount) AS amount
        FROM Customer
        GROUP BY 1) a
    ORDER BY 1)
SELECT
    visited_on,
    amount,
    average_amount
FROM a
WHERE rownum >= 7 ;

 

---

다른 사람이 쓴 답 (301ms) : 

# Write your MySQL query statement below
with temp as(
select visited_on, sum(amount) as daily_amount
from Customer
group by visited_on
)

select visited_on,

       sum(daily_amount) over (order by visited_on rows between 6 preceding and current row) as amount,
       round(avg(daily_amount) over (order by visited_on rows between 6 preceding and current row), 2) as average_amount

       from temp
       limit 1000 offset 6

- 나는 서브쿼리로 집어 넣은걸 이 사람은 with로 해결

- 나는 row_number로 번호를 지정하고 잘랐는데 이 사람은 offset 사용

---

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/

---

Friend Requests II: Who Has the Most Friends

내가 쓴 답 (459ms) : 

# 친구가 가장 많은 사람 조회
WITH a AS (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL 
    SELECT accepter_id AS id FROM RequestAccepted)
SELECT 
    id,
    COUNT(*) AS num
FROM a
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

- requester랑 accepter를 합쳐서 제일 많은 사람이 친구가 많은 사람 (union all로 합치기)

 

---

다른 사람이 쓴 답 (259ms) : 

WITH friends_counts(id, friends) AS
(
    SELECT accepter_id, COUNT(*) as Friends
    FROM RequestAccepted
    GROUP BY accepter_id

    UNION ALL

    SELECT requester_id, COUNT(*) as Friends
    FROM RequestAccepted
    GROUP BY requester_id
)
SELECT id, SUM(friends) AS num
FROM friends_counts
GROUP BY id
ORDER BY num DESC
LIMIT 1;