[코테 연습] Leetcode 코딩테스트 연습 MySQL Med.

Leetcode 코딩테스트 연습 MySQL Med.

---

1341. Movie Rating

 

: Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name. Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

 

풀이 : ROWNUM

- where절에서 rownum을 사용하기 위해 서브쿼리로 묶기

- 절대적인 행 번호가 아닌 가상의 번호이므로 '<' 연산만 가능

(select name as results
from (select name, count(*) as rated_num
    from Users u join MovieRating r using(user_id)
    group by user_id, name
    order by 2 desc, 1)
where rownum < 2)
union all
(select title as results
from (select title, avg(rating) as agv_rating
    from Movies m join MovieRating r using(movie_id)
    where to_char(created_at, 'YYYY-MM')= '2020-02'
    group by movie_id, title
    order by 2 desc, 1)
where rownum < 2)

 

 

 

 

176. Second Highest Salary

 

: Find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

 

풀이 1

- WINDOW 함수 이용 : DENSE_RANK에서 2등인 salary 추출

- 이때 where rank=2를 만족하는 record가 없으면 빈 테이블이 출력되므로
  NULL 값이 나오게 하기 위해서는 집계함수를 사용하는 전략 이용

# 집계함수를 사용함으로써 빈 테이블일 경우 NULL로 출력
select max(salary) as SecondHighestSalary
from (select salary, dense_rank() over (order by salary desc) as rank
    from Employee) e
where rank = 2

# 집계함수를 쓰지 않으면 빈 테이블이 출력
select *
from (select salary, dense_rank() over (order by salary desc) as rank
    from Employee) e
where rank = 2

 

풀이 2 

- 2등 = max보다 작은 것 record들 중 max값 

select max(salary) as SecondHighestSalary
from Employee
where salary < (select max(salary) from Employee)

 

풀이 3

- ALL/ANY 연산자 + 다중행 서브쿼리 사용

- 2등 = 자신보다 큰 값이 적어도 하나 존재하는 record들 중 max값 

select max(salary) as SecondHighestSalary
from Employee
where salary < any(select salary from Employee)

 

 

 

 

1070. Product Sales Analysis III

 

: Find all sales that occurred in the first year each product was sold. For each product_id, identify the earliest year it appears in the Sales table. Return all sales entries for that product in that year.

 

풀이 1

- IN, NOT IN 연산자 + 다중컬럼 서브쿼리 사용 

select product_id, year as first_year, quantity, price
from Sales
where (product_id, year) in (select product_id, min(year)
                            from Sales group by product_id)

 

풀이 2

- EXISTS, NOT EXISTS 연산자 + 연관 서브쿼리 사용

- 더 작은 year 값을 가지는 record가 없는 경우 = 가장 작은 year인 경우, not exists 조건 만족하도록 

select product_id, year as first_year, quantity, price
from Sales s1
where not exists (select 1
                from Sales s2
                where s1.product_id = s2.product_id and s1.year > s2.year)

 

풀이 3 

- ALL/ANY 연산자 + 다중행 서브쿼리 사용

- 모든 같은 product_id에 속하는 record의 year보다 작은 경우, ALL 조건 만족하도록

select product_id, year as first_year, quantity, price
from Sales s1
where s1.year <= all (select s2.year
                from Sales s2
                where s1.product_id = s2.product_id)

 

풀이 4

- 조건을 만족하는 value만 가지고 있는 table과 inner join

- ON절에 AND를 바로 연결해서 JOIN 과정에서 불필요한 record 제거

select s1.product_id, s1.year as first_year, s1.quantity, s1.price
from Sales s1 join (select product_id, min(year) as year
                    from Sales
                    group by product_id) s2 on s1.product_id = s2.product_id and s1.year = s2.year

 

 

 

 

 

 

✅ WITH문
- 쿼리의 결과에 이름 붙여 저장한 후 이후 쿼리에서 재사용하도록 만든 임시 쿼리(CTE)
- 복잡한 서브쿼리를 여러 번 재사용하거나 쿼리 가독성을 높이기 위해 사용 

- View와 달리 DB에 영구적으로 저장되지 않고 일시적으로 정의
  해당 쿼리가 실행될 때에만 일시적으로 존재하며, 쿼리 내에서만 유효하게 사용 가능 

- CTE(Common Table Expression) : WITH문에서 사용하는 재사용 가능한 이름 있는 서브쿼리

WITH 임시쿼리이름 AS (
    SELECT ..
    FROM ...
    WHERE ...
)

# 여러 임시 쿼리를 생성하는 경우 ,로 연결
WITH 임시쿼리이름1 AS (
    SELECT ..
    FROM ...
    WHERE ...
), 임시쿼리이름2 AS (
    SELECT ..
    FROM ...
    WHERE ...
), ....

 

 

550. Game Play Analysis IV

 

: Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

 

풀이 1

- 조건을 만족하는 value만 가지고 있는 table과 inner join

  a 테이블에다가 연속해서 접근한 경우일때만 f 테이블 record를 join 

  a = 모든 player_id가 존재, f = 연속해서 접근한 player_id만 존재 

- ON절에 AND를 바로 연결해서 JOIN 과정에서 불필요한 record 제거

select round(count(distinct f.player_id) / count(distinct a.player_id), 2) as fraction
from Activity a left join
        (select player_id, min(event_date) as first_date
        from Activity group by player_id) f
    on a.player_id = f.player_id and a.event_date = f.first_date + 1

 

풀이 2

- WITH문을 사용해 from절에 사용할 서브쿼리 f를 사전에 정의 

with f as (select player_id, min(event_date) as first_date
        from Activity
        group by player_id)

select round(count(distinct f.player_id) / count(distinct a.player_id), 2) as fraction
from Activity a left join f
    on a.player_id = f.player_id and a.event_date = f.first_date + 1

 

 

 

 

3421. Find Students Who Improved

 

: Find the students who have shown improvement. A student is considered to have shown improvement if they meet both of these conditions : Have taken exams in the same subject on at least two different dates. Their latest score in that subject is higher than their first score. Return the result table ordered by student_id, subject in ascending order.

 

풀이 : FIRST_VALUE, LAST_VALUE

- 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력

- 이때 범위의 default가 처음 ~ 현재행 

  exam_date를 기준으로 오름차순 정렬했기 때문에 first_value는 범위를 그대로 사용해도 되지만

  last_value는 범위를 지정하지 않을 경우 최대 score값을 가지는 행이 아니라, 항상 현재행이 출력됨

 

→ last_value의 범위를 처음(UNBOUNDED PRECEDING) ~ 끝(UNBOUNDED FOLLOWING)으로 변경

→ first_value + 내림차순 정렬 조건으로 바꾸기 

## from절에 들어갈 서브쿼리가 김 → WITH문 사용
# last_value의 범위를 변경
with tmp as (
    select distinct student_id, subject,
            first_value(score) over (partition by student_id, subject order by exam_date) as first_score, 
            last_value(score) over (partition by student_id, subject order by exam_date
                rows between unbounded preceding and unbounded following) as latest_score,
            count(*) over (partition by student_id, subject) as tries
    from Scores
)

# first_value 사용하고 정렬 순서를 바꾸기
with tmp as (
    select distinct student_id, subject,
            first_value(score) over (partition by student_id, subject order by exam_date) as first_score, 
            first_value(score) over (partition by student_id, subject order by exam_date desc) as latest_score,
            count(*) over (partition by student_id, subject) as tries
    from Scores
)


select student_id, subject, first_score, latest_score
from tmp
where tries >= 2 and first_score < latest_score
order by student_id, subject