SQL 문제풀이 2)

오늘은 SQL 문제풀이 난이도 Hard 문제를 풀어봤습니다.  2시간동안 풀어봤는데 정말 어렵더군요. 결국 풀이를 보며 이해하는 것조차도 오래걸렸습니다.

 

문제의 요구사항은 다음과 같습니다.

1. 날짜별 시작일로부터 최소 하나이상의 제출을 했던 해커의 숫자 출력

2. 날짜별 제출했던 수가 많은 해커 아이디 출력, 만약 같을 경우 해커의 아이디 오름차순

3. 2번의 해커의 아이디에 대한 이름 출력

 

테이블의 구조는 다음과 같습니다.

 

Hackers

hacker_id	name
15758	Rose
20703	Angela
36396	Frank
44065	Patrick
53473	Kimberly

 

Submissions

submission_date	submission_id	hacker_di	score
2016-03-01	21542	20703	0
2016-03-02	10054	20703	15
2016-03-03	35512	15758	50
2016-03-04	54791	53473	90

 

Output

2016-03-01 4 20703 Angela
2016-03-02 2 79722 Michael
2016-03-03 2 20703 Angela
2016-03-04 2 20703 Angela
2016-03-05 1 36396 Frank
2016-03-06 1 20703 Angela

 

SELECT 
submission_date, //날짜 출력


(SELECT COUNT(DISTINCT hacker_id) 
FROM submissions s2
where s1.submission_date = s2.submission_date and
(SELECT COUNT(DISTINCT s3.submission_date) FROM submissions s3
where s3.hacker_id = s2.hacker_id and s3.submission_date < s1.submission_date) = dateDIFF(s1.submission_date, '2016-03-01')),
//시작일로부터 매일 제출했던 해커의 수 출력


(SELECT hacker_id FROM submissions s2 where s2.submission_date = s1.submission_date
group by hacker_id ORDER BY count(submission_id) desc, hacker_id LIMIT 1) AS shit, 
//해커아이디 출력


(SELECT name FROM hackers where hacker_id = shit)  //해커 이름출력
FROM (SELECT DISTINCT submission_date FROM submissions) s1

 

풀이) 

 

우선 메인 쿼리는 중복된 날짜를 제거한 날짜를 가져옵니다. (2016-03-01, 2016-03-02 등 ...)

요구사항에 만족하는 데이터를 가져오기 위해 스칼라 서브쿼리를 이용합니다.

 

1) 현재 날짜에 과제를 제출했으며, 이전 날짜에 제출했던 해커의 아이디를 가진 날짜의 수와 시작일로부터 현재 날짜까지 차이가 같은(차이가 같다는건 시작일로부터 매일 제출했다는 뜻) 중복을 제거한 해커의 수를 구합니다.

 

2) 현재 날짜에 해커 아이디를 기준으로 그룹핑 후 제출했던 개수가 가장 큰 해커의 아이디를 가져오며, 만약 제출했던 개수가 같을 경우 해커의 아이디를 오름차순해서 가져옵니다.

 

3) 2번에서 가져왔던 해커의 아이디를 통해 해커의 이름을 가져옵니다.

 

이전에도 그룹핑한 데이터의 개수를 가져온 후 가장 개수가 많은 그룹을 어떻게 가져올까라는 고민을 많이 했었는데 이 문제를 풀며 해결할 수 있었습니다.

 

Group by 그룹핑할 칼럼명 Order By Count(*) Desc Limit 1