[HackerRank] 문제 풀이

Symmetric Pairs

https://www.hackerrank.com/challenges/symmetric-pairs/problem?isFullScreen=true

select X,Y
from functions
where X = Y
GROUP BY X, Y
HAVING count(*) = 2
UNION
select f1.x, f1.y
from functions f1 inner join functions f2 on f1.x = f2.y AND f1.y = f2.x
where f1.X < f1.y
ORDER BY X

New Companies

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

select c.company_code, c.founder, count(distinct lm.lead_manager_code), 
count(distinct sm.senior_manager_code), count(distinct m.manager_code),
count(distinct e.employee_code)
from company c 
left join lead_manager lm on c.company_code = lm.company_code
left join senior_manager sm on lm.lead_manager_code = sm.lead_manager_code
left join manager m on sm.senior_manager_code = m.senior_manager_code
left join employee e on m.manager_code = e.manager_code
group by c.company_code, c.founder
order by c.company_code

Top Competitors

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true

select s.hacker_id, h.name
from challenges c 
left join difficulty d on c.difficulty_level = d.difficulty_level
left join submissions s on c.challenge_id = s.challenge_id
left join hackers h on s.hacker_id = h.hacker_id
where d.score = s.score
group by s.hacker_id, h.name
having count(s.hacker_id) >= 2
order by count(s.hacker_id) desc, s.hacker_id asc

Placements

https://www.hackerrank.com/challenges/placements/problem

select s.name
from students s
left join friends f on s.id = f.id
inner join packages p1 on s.id = p1.id
inner join packages p2 on f.friend_id = p2.id
where p2.salary > p1.salary
order by p2.salary asc

Binary Tree Nodes

https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true

select N,"Root"
from BST
where P is null
UNION
select N, "Leaf"
from BST B
where NOT EXISTS (
        SELECT 1
        FROM BST
        WHERE P = B.N
)
UNION
select N, "Inner"
from BST B
where P is not null
AND EXISTS (
    select 1
    from BST
    WHERE P = B.N
)
ORDER BY N ASC

-- case 문 사용

select distinct BST.N
		, case 
        		when BST.P is null "Root"
                when BST2.N is null then "Leaf"
                else "Inner"
          end
from BST
		left join BST as BST2 on BST.N = BST2.P
order by BST.N ASC 
        

Contest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

select h.hacker_id, h.name, sum(max_score) as total_score
from (select hacker_id, challenge_id, max(score) as max_score
from Submissions
group by hacker_id, challenge_id )
t inner join hackers h on t.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having total_score != 0
order by total_score desc, h.hacker_id asc

Occupations

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

select  min(case when occupation = 'Doctor' then name else null end) doctor
       ,min(case when occupation = 'Professor' then name else null end) doctor
       ,min(case when occupation = 'Singer' then name else null end) doctor
       ,min(case when occupation = 'Actor' then name else null end) doctor
from (
select 
occupation, 
name, 
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) rn
from OCCUPATIONS
) t
group by rn

SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

WITH start_dates AS (
    SELECT 
        start_date, 
        ROW_NUMBER() OVER (ORDER BY start_date) AS rn
    FROM projects
    WHERE start_date NOT IN (
        SELECT DISTINCT end_date 
        FROM projects
    )
),
end_dates AS (
    SELECT 
        end_date, 
        ROW_NUMBER() OVER (ORDER BY end_date) AS rn
    FROM projects
    WHERE end_date NOT IN (
        SELECT DISTINCT start_date 
        FROM projects
    )
)
SELECT 
    s.start_date,
    e.end_date
FROM start_dates s
INNER JOIN end_dates e
    ON s.rn = e.rn
order by DATEDIFF(e.end_date,s.start_date), s.start_date