[SQL] Hacker rank 문제들

The blunder

select ceil(avg(salary) - avg(replace(salary, '0', '')))
from employees;

Top Earners

SELECT * 
from (
    SELECT months * salary as total, COUNT(*) FROM employee
    GROUP BY 1
    ORDER BY 1 desc
) as foo limit 1;

---

SELECT salary*months, count(*)
from employee
where salary*months = (select max(salary*months) from employee)
group by 1;

Weather Observation Station 15

SELECT round(LONG_W,4) 
FROM station
WHERE LAT_N < 137.2345
ORDER BY LAT_N desc
limit 1;

---

SELECT round(LONG_W,4)
from STATION
where LAT_N = (select max(LAT_N)
				from STATION
                where LAT_N < 137.2345);

Weather Observation station 19

select round(sqrt(pow(min_lat - max_lat, 2) + pow(min_long - max_long, 2)) , 4)
from 
    (select Min(LAT_N) as min_lat, MAX(LAT_N) as max_lat
     , MIN(LONG_W) as min_long, MAX(LONG_W) as max_long
    from station) as foo;
    
----

select round(sqrt(pow(min(LAT_N) - max(LAT_N),2) + pow(min(LONG_W) - max(LONG_W),2)), 4)
from station

Weather Observation Station 20

with ordering as(
    select lat_n , row_number() over(order by lat_n asc) as lat_order
        from station
)

select round(avg(lat_n), 4)
from ordering
where lat_order = (select floor(avg(lat_order)) from ordering)
    or lat_order = (select ceil(avg(lat_order)) from ordering)

Type of Triangle

select 
    case when a=b and b = c then 'Equilateral'
        when a+b <= c then 'Not A Triangle'
        when a != b and b != c and c != a then 'Scalene'
        else 'Isosceles'
    END 
from TRIANGLES;

select
	case when a=b and b=c and c=a then 'Equilateral'
    	when a+b <= c or a+c <= b or b+c <= a then 'Not A Triangle'
        when(a=b or b=c or c=a) then 'Isosceles'
        else 'Scalene'
        end
from Triangles

The PADS

SELECT concat(name, '(', LEFT(occupation,1), ')')
FROM OCCUPATIONS
order by name, LEFT(occupation,1);

SELECT concat('There are a total of ', count(occupation),' ', LOWER(occupation), 's.')
FROM Occupations
group by occupation
order by count(occupation) asc, occupation asc;

Occupations

SELECT 
    MAX(CASE WHEN occupation = 'Doctor' THEN name END),
    MAX(CASE WHEN occupation = 'Professor' THEN name END),
    MAX(CASE WHEN occupation = 'Singer' THEN name END),
    MAX(CASE WHEN occupation = 'Actor' THEN name END)
    
FROM (SELECT *, ROW_NUMBER() OVER(Partition by Occupation ORDER BY name ) 
      as row_rank FROM Occupations) as foo1
GROUP BY row_rank

---

SELECT min(case when A.occupation = 'Doctor' then name else null end) as doctor
    , min(case when A.occupation = 'Professor' then name else null end) as Professor
    , min(case when A.occupation = 'Singer' then name else null end) as Singer
    , min(case when A.occupation = 'Actor' then name else null end) as Actor
FROM 
(
    select name, occupation, rank() over(partition by occupation order by name asc) as name_order
    from Occupations
) as A

group by A.name_order;

Binary Tree Nodes

SELECT N, (
        case when P is null then 'Root'
            when N not in (select distinct P from BST where P is not null) then 'Leaf'
        else 'INNER' end
        )as nodeType 
FROM BST
ORDER BY N;

---

select case when P is null then concat(N, ' Root')
            when N in(select distinct P from BST) then concat(N, ' Inner')
            else concat(N, ' Leaf')
        end
FROM BST
Order By N;

New Companies

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 as 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;

===

select A.company_code
		,A.founder
        , (select count(distinct lead_manager_code) from lead_manager
        where company_code = A.company_code) as num_lead
        , (select count(distinct senior_manager_code) from Senior_manager
        where company_code = A.company_code) as num_senior
        , (select count(distinct manager_code) from Manager
        where company_code = A.company_code) as num_manager
        , (select count(distinct employee_code) from Employee
        where company_code = A.company_code) as num_employee
from company A

Average Population of Each Continent

select COUNTRY.continent,floor(avg(CITY.population))
from COUNTRY
INNER JOIN CITY
ON COUNTRY.code = CITY.COUNTRYCODE
GROUP BY COUNTRY.continent;

The Report

SELECT IF(grade >= 8, S.name, NULL), G.grade, S.Marks
FROM Students as S
INNER JOIN Grades as G
ON S.Marks BETWEEN Min_Mark and Max_Mark
Order by 2 desc, 1 asc, 3 asc;

Contest Leaderboard

SELECT H.hacker_id, H.name, sub2.total_score 
FROM (SELECT sub.hacker_id, 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) as sub
        GROUP BY sub.hacker_id
        HAVING total_score > 0) as sub2
INNER JOIN Hackers as H ON sub2.hacker_id = H.hacker_id
ORDER BY sub2.total_score DESC, H.hacker_id;


---
select A.hacker_id, A.name, sum(B.score) as total_score
from Hackers A
inner join (select hacker_id, challenge_id, max(score) as score
	from Submissions
    group by 1,2) B
on A.hacker_id = B.hacker_id
group by 1,2
having sum(B.score) != B

order by total_score desc, hacker_id asc

Challenges

SELECT Hackers.hacker_id, Hackers.name, COUNT(*) AS challenges_created
FROM Hackers
INNER JOIN Challenges ON Hackers.hacker_id = Challenges.hacker_id
GROUP BY Hackers.hacker_id, Hackers.name
HAVING challenges_created IN (SELECT sub2.challenges_created
                             FROM (SELECT hacker_id, COUNT(*) AS challenges_created
                                  FROM Challenges
                                  GROUP BY Challenges.hacker_id) sub2
                             GROUP BY sub2.challenges_created
                              HAVING COUNT(*) = 1 )
        OR challenges_created = (SELECT MAX(sub1.challenges_created)
                                FROM (SELECT COUNT(*) AS challenges_created
                                     FROM Challenges
                                     GROUP BY Challenges.hacker_id) sub1)
ORDER BY challenges_created DESC, Hackers.hacker_id

---
select main.hacker_id, main.name, main.num

from(select A.hacker_id, A.name,count(B.challenge_id) as num
		from Hackers as A
        INNER JOIN Challenges B
        on A.hacker_id = B.hcakcer_id
        GROUP BY A.hacker_ID, A.name) main

where main.num = select max(ct1.nt_challenges)
					from ( select Hacker_id, count(challenge_id ) as cnt_challenge
					from Challenges
        			GROUP BY hacker_id) t1) 
or main.num in (
		select t2.cnt_challenges
		from(select hacker_id, count(challenge_id) as cnt_challenges
			from Challenges
			group by hacker_id) t2
	group by cnt_challenges
	having count(*) < 2)
order by main.num desc, main.hacker_id asc;