[SQL 코드카타] 105~109 (LeetCode)

105번 customers-who-bought-all-products

select
    customer_id
from    
    customer
group by
    customer_id
having -- 모든 제품을 산 고객을 구하는 것 > 제품의 수가 같은 고객 구하기
    count(distinct product_key) = (select count(product_key)
                                from product)

 

106번 the-number-of-employees-which-report-to-each-employee

select
    emp2.employee_id,
    emp2.name,
    count(emp2.employee_id) as reports_count,
    round(avg(emp1.age),0) as average_age
from
    employees emp1
    join employees emp2
    on emp1.reports_to = emp2.employee_id 
    -- 1) inner 조인을 통해 보고 받은 emp2.employee_id를 추출
group by
    1,2
order by
    1;

 

107번 primary-department-for-each-employee

select 
    employee_id, department_id
from
    employee
group by
    employee_id
having
    count(employee_id) = 1 
union
select
    employee_id, department_id
from
    employee
where
    primary_flag = 'Y'

> 또는

select
    employee_id, department_id
from
    employee
where
    primary_flag = 'Y'
    OR employee_id IN (select employee_id
                    from employee
                    group by 1
                    having count(employee_id) = 1)
order by 1

 

108번 triangle-judgement

> 삼각형의 조건 이해

select
    x,
    y,
    z,
    case when x+y > z and y+z > x and x+z > y then 'Yes'
        else 'No' end triangle
from
    triangle

 

109번 consecutive-numbers

with logs2 as(
select
    id,
    num,
    lead(num,1) over() as num1,
    lead(num,2) over() as num2
from
    logs
)
select
    distinct num as 'ConsecutiveNums'
from
    logs2
where
    num = num1 and num1 = num2

> lead 대신 다른 방법

select
    distinct lo1.num as 'ConsecutiveNums'
from
    logs lo1,
    logs lo2,
    logs lo3
where
    lo1.id = lo2.id-1 and
    lo2.id = lo3.id-1 and
    lo1.num = lo2.num and
    lo2.num = lo3.num