SQL 코테 준비 (3월 1주)(해커랭크)

2.28

1) Weather Observation Station 14

select truncate(max(LAT_N),4)
from station
where LAT_N < 137.2345

2) Weather Observation Station 15

select round(long_w, 4)
from station 
where lat_n < 137.2345 
order by lat_n DESC
limit 1

3) Weather Observation Station 16

select round(min(LAT_N),4)
from station
where LAT_N > 38.7780

3.1

1) Weather Observation Station 17

SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N ASC  
LIMIT 1;

2) Weather Observation Station 18

SELECT 
    ROUND(
        ABS(c - a) + 
        ABS(d - b), 
    4) AS manhattan_distance
FROM (
    select min(LAT_N) as a,
           min(LONG_W) as b,
           max(LAT_N) as c,
           max(LONG_W) as d
    from station
) as md

3) Weather Observation Station 19

select round(sqrt(power(max(lat_n)-min(lat_n),2)+power(max(long_w)-min(long_w),2)),4)
from station;

3.2

1) Population Census

SELECT SUM(CITY.POPULATION)
FROM CITY
JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia';

2) African Cities

select city.name
from city
join country on city.countrycode = country.code
where country.continent = 'Africa'

3) Average Population of Each Continent

SELECT 
    COUNTRY.Continent, 
    FLOOR(AVG(CITY.Population)) 
FROM CITY
JOIN COUNTRY ON CITY.CountryCode = COUNTRY.Code
GROUP BY COUNTRY.Continent

3.3

1) Weather Observation Station 5

(select city, length(city)
from station
order by length(city) asc, city
limit 1 )
UNION
(select city, length(city)
from station
order by length(city) desc, city
limit 1)

2) 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 as lm on lm.company_code = c.company_code
left join Senior_Manager as sm on sm.lead_manager_code = lm.lead_manager_code
left join Manager as m on m.senior_manager_code = sm.senior_manager_code
left join employee as e on e.manager_code = m.manager_code
group by c.company_code, c.founder
order by c.company_code

3) The Report

SELECT case when g.grade < 8 then null
            when g.grade >= 8 then s.name
       end as name,
       g.grade,
       s.marks
from students as s
join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, name asc

3.4

1) Top Competitors

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

2) Ollivander's Inventory

select w.id,
       wp.age,
       w.coins_needed,
       w.power
from wands as w
join wands_property as wp on w.code = wp.code
where wp.is_evil = 0
and w.coins_needed = (
    select min(coins_needed)
    from wands as w2
    join wands_property as wp2 on w2.code = wp2.code
    where w2.power = w.power and wp.age = wp2.age
)
order by power desc, age desc

3) Contest Leaderboard

SELECT 
    hacker_id,
    name,
    SUM(max_score) AS total_score
FROM (
    SELECT 
        h.hacker_id,
        h.name,
        s.challenge_id,
        MAX(s.score) AS max_score
    FROM hackers AS h
    JOIN submissions AS s ON h.hacker_id = s.hacker_id
    GROUP BY h.hacker_id, h.name, s.challenge_id
) AS base
GROUP BY hacker_id, name
HAVING total_score > 0
ORDER BY total_score DESC, hacker_id ASC;

3.5

1) SQL project planning

SELECT start_date 
     , MIN(end_date)  
  FROM (SELECT start_date
          FROM projects
         WHERE start_date NOT IN (SELECT end_date FROM projects)) AS s
     , (SELECT end_date
          FROM projects
         WHERE end_date NOT IN (SELECT start_date FROM projects)) AS e
 WHERE start_date < end_date 
 GROUP BY start_date  
 ORDER BY DATEDIFF(MIN(end_date), start_date), start_date;  

• 로직은 이해 but 구현하는 방법에 접근하지 못함.

2) Placements

select s.name
from students as s
join friends as f on s.ID = f.ID
join packages as p on s.ID = p.ID
join packages as p2 on f.friend_ID = p2.ID
where p.salary < p2.salary
order by p2.salary

3) Weather Observation Station 20

select round(LAT_N, 4)
from (
    select LAT_N, 
           percent_rank() over (order by LAT_N asc) as pr
    from station
) as p
where pr=0.5

• PERCENT_RANK() : 임의의 행의 백분율 순위를 계산 0 ~ 1 로 계산