8.10
1) 멸종위기의 대장균 찾기
with recursive tmp as (
select id, parent_id, 1 as generation
from ecoli_data
where parent_id is null
union all
select s.id, s.parent_id, tmp.generation + 1
from tmp join ecoli_data s
on tmp.id = s.parent_id
)
select count(*) count, generation
from tmp
where id not in (
select distinct parent_id
from tmp
where parent_id is not null)
group by generation
order by 2
8.11
1) Second Highest Salary
select max(salary) as SecondHighestSalary
from employee
where salary < (select max(salary) from employee)
2) Rank Scores
select score,
dense_rank() over(order by score desc) as 'rank'
from scores
order by 2
3) Consecutive Numbers
select distinct num as ConsecutiveNums
from (
select num,
lead(num,1) over(order by id) as lead_num,
lead(num,2) over(order by id) as lead_num2
from logs
) as t
where num = lead_num
and num = lead_num2
8.12
1) Department Highest Salary
select d.name as Department,
e.name as Employee,
e.salary as Salary
from employee e
join department d on e.departmentId = d.id
where (e.departmentId, e.salary) in (select departmentId, max(salary) from employee group by departmentId)
2) Game Play Analysis IV
with base as (
select player_id,
datediff(event_date, min(event_date) over(partition by player_id)) = 1 as con_login
from activity
)
select round(sum(con_login) / (count(distinct player_id)),2) as fraction
from base
8.13
1) Managers with at Least 5 Direct Reports
select e2.name
from employee as e1
join employee as e2 on e1.managerId = e2.id
group by e1.managerId
having count(*) >= 5
2) Investments in 2016
select round(sum(tiv_2016),2) as tiv_2016
from insurance
where tiv_2015 in (select tiv_2015 from insurance group by tiv_2015 having count(*) >= 2)
and (lat, lon) in (select lat, lon from insurance group by lat, lon having count(*) = 1)
8.14
1) Friend Requests II: Who Has the Most Friends
select id,
count(*) as num
from (
select requester_id as id
from RequestAccepted
union all
select accepter_id as id
from RequestAccepted
) as t
group by 1
order by 2 desc
limit 1
