Hard

<HARD>

department top three salaries

SELECT ms.department
    ,ms.employee
    ,ms.salary
FROM(
    SELECT Employee.name AS employee
        ,Department.name AS department
        ,Employee.salary
        ,DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rank
    FROM Employee
    INNER JOIN Department ON Employee.departmentId = Department.id
    ) ms
WHERE ms.rank <= 3;
SQL
복사

내 풀이

select b.name as Department
    ,a.name as Employee
    ,a.salary as Salary
from (
    select *
        ,dense_rank() over(partition by departmentId order by salary desc) as salary_rnk
    from employee
) a
inner join department b on a.departmentId = b.id
where a.salary_rnk <= 3;
SQL
복사

trips and users → 다시!!

•

cancel 된 비율 → status가 ‘cancelled_’로 시작하면서 users의 banned 가 ‘No’ 인 요청 수  /  그날 users의 banned가 ‘No’인 전체 요청 수

•

2013-10-01 날짜부터 ‘2013-10-03 날짜까지 cancel 비율 구하기(소수점 두자리 숫자까지)

내 풀이 → 비효율적이며 너무 복잡하다. 버리자

select e.request_at as Day
    ,round(count(distinct if(e.status like 'cancelled%',e.id,null)) / count(distinct e.id),2) as "Cancellation Rate"
from (
    select a.*
        ,b.banned
        ,b.role
    from trips a
    inner join users b on a.client_id = b.users_id
    union all
    select c.*
        ,d.banned
        ,d.role
    from trips c
    inner join users d on c.driver_id = d.users_id
) e
where e.banned = 'No' 
group by e.request_at
having e.request_at between '2013-10-01' and '2013-10-03'
order by Day;
SQL
복사

다른 풀이

select request_at as "Day"
	,round(sum(case when status like 'cancelled%' then 1 else 0 end) / count(id),2) as "Cancellation Rate"
from trips
where 
	client_id not in (select users_id from users where role = 'client' and banned = 'Yes')
and
	driver_id not in (select users_id from users where role = 'driver' and banned = 'Yes')
and
	request_at between '2013-10-01' and '2013-10-03'
group by request_at;
SQL
복사

human traffic of stadium - 연속적으로 값이 나오는 행들을 출력

크게 두 유형으로 나뉜다.

→ n번 이상 연속으로 나오는 동일한 수

→ n번 이상 연속하는 수 전체

n번 이상 연속으로 나오는 동일한 수 : leetcode 180 ⇒ 3,3,3,3 같이 같은 수가 n번 이상 연속해서 나옴 n번 이상 연속하는 수 전체: leetcode 601 ⇒ 3,4,5,6 같이 연속한 수를 n개 이상 나옴

mysql 의 경우, 자주 사용하는 방법은 self-join , window-function 이다.

sol 1) lag(), lead()

문제 : id가 연속하면서, people의 값이 ≥ 100 인 행들이 3번이상나오는 행들을 출력

→ 먼저 lead와 lag 를 최대 2번씩 한 변수들을 확인해보면 다음과 같이 나온다

WITH A AS (
    SELECT id
     , LEAD(id,1) OVER () n_id1
     , LEAD(id,2) OVER () n_id2
     , LAG(id,1) OVER () p_id1
     , LAG(id,2) OVER () p_id2 
     , visit_date
     , people
FROM stadium
WHERE people >= 100) 
SQL
복사

id	n_id1	n_id2	p_id1	p_id2	people	visit_date
2	3	5	null	null	109	2017-01-02
3	5	6	2	null	150	2017-01-03
5	6	7	3	2	145	2017-01-05
6	7	8	5	3	1455	2017-01-06
7	8	null	6	5	199	2017-01-07
8	null	null	7	6	188	2017-01-09

case when 을 이용해서 연속하는 수 정의하기

WITH A AS (
    SELECT id
     , LEAD(id,1) OVER () n_id1
     , LEAD(id,2) OVER () n_id2
     , LAG(id,1) OVER () p_id1
     , LAG(id,2) OVER () p_id2 
     , visit_date
     , people
FROM stadium
WHERE people >= 100) 

SELECT id
     , visit_date
     , people
FROM (
SELECT CASE WHEN id + 1 = n_id1 AND id + 2 = n_id2 THEN 'Y'
            WHEN id - 1 = p_id1 AND id - 2 = p_id2 THEN 'Y'
            WHEN id + 1 = n_id1 AND id - 1 = p_id1 THEN 'Y'
            ELSE 'N' END csc
     , id
     , visit_date
     , people
FROM a) b
WHERE csc = 'Y'
SQL
복사

sol2) 연속하는 수에 연속하는 수를 빼면 같은 수가 나온다

WITH a AS (SELECT id -- 기존의 연속하는 수
     , people
     , visit_date
     , ROW_NUMBER() OVER (ORDER BY visit_date) rn -- 다른 연속하는 수 만들기
     , id - ROW_NUMBER() OVER (ORDER BY visit_date) diff 
FROM stadium
WHERE people >= 100)

SELECT id
     , visit_date
     , people
FROM a
WHERE diff IN (
SELECT diff
FROM a 
GROUP BY diff
HAVING COUNT(diff) >= 3 )
SQL
복사