.jpg)
# Top Competitors
Write a query to print the respective hacker_id and name of hackers
who achieved full scores for more than one challenge.
Order your output in descending order
by the total number of challenges in which the hacker earned a full score.
If more than one hacker received full scores in same number of challenges,
then sort them by ascending hacker_id.hard, but interesting
나의 답안
/*
Write a query to print the respective hacker_id and name of hackers
who achieved full scores for more than one challenge.
filter
1st : challenge & score & submission & name
2nd : who receives full score?
3rd : order by total num of challenges
4th : order by hacker id
*/
select h.hacker_id, h.name -- count_fullScore_per_hacker.cnt_score
from Hackers as h
right join (
select distinct s.hacker_id, count(s.score) as cnt_score-- , c_and_d.score
from Submissions as s
left join (
select diff.score, c2.challenge_id
from Challenges as c2
left join(
select d2.difficulty_level, d2.score
from Difficulty as d2
) diff
on c2.difficulty_level = diff.difficulty_level
) c_and_d
on s.challenge_id = c_and_d.challenge_id
where s.score = c_and_d.score
group by s.hacker_id
having count(s.score)>1
order by count(s.score) desc, s.hacker_id asc
) count_fullScore_per_hacker
on h.hacker_id =count_fullScore_per_hacker.hacker_id
order by count_fullScore_per_hacker.cnt_score desc, count_fullScore_per_hacker.hacker_id asc1차로 챌린지별로 최고 점수가 있는 table을 쿼리했다. ( c_and_d )
2차로 해당 table과 submission table과 조인하여, 제출 테이블에는 제출자의 점수가 있는데 해당 점수 옆에 해당 첼린지의 최고 점수가 존재하도록 했다. ( count_fullScore_per_hacker )
여기에 문제의 조건을 만족하기 위한 group by, having 등을 적용했다.
이후 문제에서 요구하는 답의 형태를 갖추기 위해서
Hacker 테이블과 조인하였다.
Symmetric Pairs
Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X.
List the rows such that X1 ≤ Y1.1차 답안
select distinct f.X, f.Y -- , mirror.Y -- , f.Y as x2, f.X as y2
from Functions as f
join (
select f2.Y, f2.X
from Functions as f2
) mirror
on f.X = mirror.Y and f.Y = mirror.X
where f.X <= f.Y
order by f.X asc-- , f.Y asc(추가)
위 쿼리에서 실패한 이유는
where f.X <= f.Y 이것 때문이다.
여기서 =이 들어가면 안되는 이유가 뭘까?
PAIRS가 되려면 = 인 경우, row가 2개 이상(혹은 2개)이여야 한다.
다시 생각해보자.
대칭 짝을 이루기 위해선 어떤 조건을 만족해야 하는가?
1. x1=y2, y1=x2 인 row가 2개 이상이여야 한다.
2. 원래 table 의 row들 에서 1번 조건을 만족하는 row 가 있는지 모든 rows 에서 확인해야 한다.
그러면 어떻게 해야하는가?
x1,y1 인 table과 y1, x1인 table이 있다면
y1 에 대해서 inner join 하면
1. rows 에 pairs 인지는 따지지 않고 y1 을 키로 보고 join 되는 케이스가 발생한다.
등호를 빼버리고 Union 으로 =이면서 rows가 2개인 결과값을 합쳐준다.
수정 정답
SELECT f1.x
, f1.y
FROM Functions AS f1
INNER JOIN Functions AS f2 ON f1.x = f2.y and f1.y=f2.x
WHERE f1.x < f1.y -- List the rows such that X1 ≤ Y1.
UNION -- 두개의 값을 합치는 UNION
SELECT *
FROM functions
WHERE X=Y
GROUP BY X, Y
HAVING COUNT(*) = 2
ORDER BY X -- ORDER BY는 아래에 적어주면 전체 값을 순서대로 정렬함참고) 2개 이상의 group by
2개 이상의 group by 는 경우의 수 별 그룹으로 세분화한다.
Print Prime Numbers
음 소수...
(다른 사람의 답안)
#select all the numbers till 1000 in the tblnums
with recursive tblnums
as (
select 2 as nums
union all
select nums+1
from tblnums
where nums<1000)
select group_concat(tt.nums order by tt.nums separator '&') as nums
from tblnums tt
where not exists
#the num should not be divisible by any number less than it
( select 1 from tblnums t2
where t2.nums <= tt.nums/2 and mod(tt.nums,t2.nums)=0) 여기서
with recursive 란?
1. 메모리 상에 가상의 테이블 저장
2. 스스로를 참조-- expression_name의 CTE를 생성한다.
WITH expression_name [ ( column_name [,...n] ) ]
AS
(
CTE_query_definition
)
-- 이후 expression_name을 마치 테이블명처럼 사용할 수 있다.
SELECT <column_list> FROM expression_name
group_concat, seperator 란?
필요에 의해 서로 다른 결과를 한줄로 합쳐서 보여줘야 할 경우가 있다.
전체 결과값을 가져와서 java 와 같은 프로그램 언어에서 for 문을 돌며 문자열을 붙여도 되긴 하지만
Select 쿼리를 던질때 결과값으로 합쳐져 있는 문자열을 받는게 더 편하다.
출처: https://fruitdev.tistory.com/16 [과일가게 개발자]
mod 란?
이 함수는 n을 m으로 나눈 나머지를 반환합니다.