LeetCode_5. Advanced Select and Joins

https://leetcode.com/studyplan/top-sql-50/

 

1731. The Number of Employees Which Report to Each Employee

문제 : Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

> 1명 이상의 report를 받는 매니저의 employee_id, name, reports_count, average_age 찾기

Employees table

• join (self join)

- reports_to 열은 employee_id를 참조하고 있음

1. 숫자만 나열되어 있는 각각의 행으로는  A직원이 보고하는 매니저의 정보를 확인할 방법이 없음

2. Alice(employee_id = 6)는 reports_to 값이 9인데, 이 9가 누구인지 알려면 employee_id가 9인 행을 찾아야 함

3. Alice가 Hercy에게 보고한다는 관계를 나타내기 위해서는 두 속성을 연결해야 함

-- e1 : 직원 테이블, e2 : 매니저 테이블
select e2.employee_id, e2.name, count(e2.employee_id) as reports_count, round(avg(e1.age), 0) as average_age
from employees e1
join employees e2 on e1.reports_to = e2.employee_id -- reports_to 값과 employee_id 값 비교하여 매칭
group by e2.employee_id -- 매니저_id grouping, count() 분석
order by e2.employee_id -- 매니저_id 기준으로 오름차순 정렬
;

---

1789. Primary Department for Each Employee

문제 : Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.

> 각 직원의 메인부서 찾기

Employee table

• where 1 = 1

- 가독성, 유연성 : 항상 참, 조건 추가 시 헷갈릴 여지 없애기 (조건 추가 구조 통일)

- 없어도 되지만 쓰면 코드 관리와 확장성에서 편리해짐

-- SubQuery
select employee_table, department_id
from employee
where 1 = 1
and primary_flag = 'y' -- 1. primary_flag가 'Y'인 직원 (2, 4)
or employee_id in ( 	-- 2. employee_id 가 하나밖에 없는 사람 (1, 3)
    select 
    from employee
    group by employee_id
    having count(employee_id) = 1
    );

---

610. Triangle Judgement

문제 : Report for every three line segments whether they can form a triangle.

> 삼각형이 성립 가능한 x y z 값 구분하기

 

Triangle table

 

• 삼각형이 성립 가능한 조건 : 가장 긴 변이 나머지 두 변의 길이의 합보다 작아야 함
• case 문 (조건문)

select *,
	case
		when x >= y and x >= z and y+z > x then 'Yes' -- x가 가장 긴 경우
    		when y >= x and y >= z and z+x > y then 'Yes' -- y가 가장 긴 경우
    		when z >= x and z >= y and x+y > z then 'Yes' -- z가 가장 긴 경우
		else 'No' 
	end as triangle
from triangle
;

---

180. Consecutive Numbers

문제 : Find all numbers that appear at least three times consecutively.

> 세 번 연속으로 오는 동일한 숫자 찾기

Logs table

• CTE : with 임시 테이블명 as ( ... )

- 정의된 중간 결과를 저장한 임시 테이블 (num, num_1, num_2를 저장한 cte_logs)

1. 재사용 가능 : 해당 쿼리 내 여러번 사용 가능

2. 가독성 향상 : 복잡한 쿼리를 여러 단계로 나눠서 작성 가능

3. SubQuery 대체 - 재사용이 어려움

• Lead(), Lag() : 앞뒤 행을 참조하는 함수

-- CTE : 쿼리 시작 이전에 정의됨
WITH cte_logs AS ( -- logs table에서 현재, 다음, 다다음 값 가져옴
    SELECT 
        num,
        LEAD(num, 1) OVER () AS num_1, 	-- 현재 행의 다음 행 값
        LEAD(num, 2) OVER () AS num_2 	-- 현재 행의 다다음 행 값
    FROM logs
)
SELECT DISTINCT num AS ConsecutiveNums -- distinct로 중복 행 방지 
FROM cte_logs
WHERE num = num_1 	-- 현재 행 값 = 다음 행 값
AND num = num_2 	-- 현재 행 값 = 다다음 행 값
;

-- SubQuery : SubQuery 내에서 중간결과 생성
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT 
        num,
        LEAD(num, 1) OVER () AS num_1,
        LEAD(num, 2) OVER () AS num_2
    FROM logs
) AS sub_logs
WHERE num = num_1
AND num = num_2
;

---

1164. Product Price at a Given Date

문제 : Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

> 2019-08-16 이전 최신의 물품 가격 구하기, 이후에 바뀐 물품의 가격은 10으로 취급

Products table

- 조건에 따라 다른 값 부여 : union으로 연결

union : 중복 제거

union all : 중복 포함

-- 2019-08-16 이후로 가격이 변경된 상품 : 가격 10으로 고정 
-- (1의 최초 변경일 : 2019-08-14, X)
-- (2의 최초 변경일 : 2019-08-14, X)
-- (3의 최초 변경일 : 2019-08-18, O)
select product_id, 10 as price 
from products
group by product_id
having min(change_date) > '2019-08-16' -- 가격 변경 날짜가 '2019-08-16'보다 늦는지?

union

-- 2019-08-16 이전에 가격이 변경된 상품 : 가장 최신의 가격
-- product_id별로 데이터를 그룹화하고 최신 날짜가 위에 오도록 내림차순 > 고유 번호 부여 
-- > 각 그룹 내에서 최신 데이터 식별 (new_date = 1)
select product_id, new_price as price
from (
    select *, 
    	row_number() over (partition by product_id 
        	  	   order by change_date desc) as new_date 
    from products
    where change_date <= '2019-08-16'
) as latest_price_num
where new_date = 1
;

select distinct product_id, 10 as price
from products
group by 1
having min(change_date) > '2019-08-16'

union

select product_id, new_price as price 
from products
where (product_id, change_date) in
	(
	select product_id, max(change_date) as recent_date 
    	from products
	where change_date <= '2019-08-16'
	group by 1
	) ;

---

1204. Last Person to Fit in the Bus

문제 : 1000kg까지 버틸 수 있는 버스에 가장 마지막으로 탈 수 있는 사람은 누구?

Queue

- 타는 순서 : turn

• CTE

-- cte_queue : 사람 정보, 순서에 따른 누적합계 계산한 total_weight을 turn 기준으로 정렬
with cte_queue as (  
	select *, sum(weight) over(order by turn) as total_weight
    from queue
    order by turn
)
select person_name
from cte_queue
where total_weight <= 1000 
order by total_weight desc -- 조건 만족하는 행 중에서 total_weight 가장 큰 사람 뽑기
limit 1 		   -- 최상위 1행만 반환
;

---

1907. Count Salary Categories

문제 : income 별 카테고리화, 카테고리에 속하는 account 개수 구하기

Accounts table

select 'Low Salary' as category, count(*) as accounts_count
from accounts
where income < 20000
union 
select 'Average Salary' as category, count(*)
from accounts
where income >= 20000 and income <= 50000
union
select 'High Salary' as category, count(*)
from accounts
where income > 50000
;