Occupations - 매우 중요!! (어려움)
행에 있는 데이터들을 변수 값으로 변환
SELECT a.Occupation,
a.Name,
(SELECT COUNT(*)
FROM Occupations AS b
WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS rank
FROM Occupations AS a
SQL
복사
SELECT
rank,
CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END AS Doctor,
CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END AS Professor,
CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END AS Singer,
CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END AS Actor
FROM (
SELECT a.Occupation,
a.Name,
(SELECT COUNT(*)
FROM Occupations AS b
WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS rank
FROM Occupations AS a
) AS c
SQL
복사
# sol1)
SELECT
rank,
MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
FROM (
SELECT a.Occupation,
a.Name,
(SELECT COUNT(*)
FROM Occupations AS b
WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS rank
FROM Occupations AS a
) AS c
GROUP BY c.rank;
SQL
복사
# sol2) row_number() 사용한 풀이
select max(case when Occupation = 'Doctor' then Name else NULL end) as 'Doctor'
,max(case when Occupation = 'Professor' then Name else NULL end) as 'Professor'
,max(case when Occupation = 'Singer' then Name else NULL end) as 'Singer'
,max(case when Occupation = 'Actor' then Name else NULL end) as 'Actor'
from (
select *
,row_number() over(partition by Occupation order by Name) rn
from occupations
) t
group by rn;
SQL
복사
Ollivander's Inventory - 다시!
select A.id
,B.age
,A.coins_needed
,A.power
from Wands A
inner join Wands_Property B on A.code = B.code
where is_evil = 0
and coins_needed = (select min(coins_needed)
from Wands A1
inner join Wands_Property B1
on A1.code = B1.code
where is_evil = 0
and A.power = A1.power
and B.age = B1.age)
order by A.power desc, B.age desc;
SQL
복사
Challenges - 어려움
•
print hacker_id, name and the total number of challenges created by each student.
•
Sort your results by the total number of challenges in descending order. (If more than one student created the same number of challenges, then sort the result by hacker_id)
•
If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.
select H.hacker_id
,H.name
,count(distinct C.challenge_id) as challenges_cnt
from Hackers H
inner join Challenges C on H.hacker_id = C.hacker_id
group by H.hacker_id, H.name
having challenges_cnt in (select sub2.challenges_created
from (select hacker_id
,count(*) as challenges_created
from Challenges
group by hacker_id) sub2
group by sub2.challenges_created
having count(*) = 1)
or challenges_cnt = (select max(sub.challenges_cnt)
from (select count(*) as challenges_cnt
from Challenges
group by hacker_id) sub)
order by 3 desc, 1;
SQL
복사