[MSSQL/Hackerrank] 15 Days of Learning SQL

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

 

Input Format

The following tables hold contest data:

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.

 

Sample Input

For the following sample input, assume that the end date of the contest was March 06, 2016.

Hackers Table:

Submissions Table:

Sample 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

 

샘플데이터 생성

원활한 문제풀이를 위해 샘플데이터를 생성한다.

CREATE TABLE submissions (submission_date date, submission_id int, hacker_id int, score int);
INSERT INTO submissions VALUES('2016-03-01',8494,20703,0);
INSERT INTO submissions VALUES('2016-03-01',22403,53473,15);
INSERT INTO submissions VALUES('2016-03-01',23965,79722,60);
INSERT INTO submissions VALUES('2016-03-01',30173,36396,70);
INSERT INTO submissions VALUES('2016-03-02',34928,20703,0);
INSERT INTO submissions VALUES('2016-03-02',38740,15758,60);
INSERT INTO submissions VALUES('2016-03-02',42769,79722,60);
INSERT INTO submissions VALUES('2016-03-02',44364,79722,60);
INSERT INTO submissions VALUES('2016-03-03',45440,20703,0);
INSERT INTO submissions VALUES('2016-03-03',49050,36396,70);
INSERT INTO submissions VALUES('2016-03-03',50273,79722,5);
INSERT INTO submissions VALUES('2016-03-04',50344,20703,0);
INSERT INTO submissions VALUES('2016-03-04',51360,44065,90);
INSERT INTO submissions VALUES('2016-03-04',54404,53473,65);
INSERT INTO submissions VALUES('2016-03-04',61533,79722,45);
INSERT INTO submissions VALUES('2016-03-05',72852,20703,0);
INSERT INTO submissions VALUES('2016-03-05',74546,38289,0);
INSERT INTO submissions VALUES('2016-03-05',76487,62529,0);
INSERT INTO submissions VALUES('2016-03-05',82439,36396,10);
INSERT INTO submissions VALUES('2016-03-05',90006,36396,40);
INSERT INTO submissions VALUES('2016-03-06',90404,20703,0);

CREATE TABLE hackers (hacker_id integer, name varchar(100))
INSERT INTO hackers VALUES(15758,'Rose');
INSERT INTO hackers VALUES(20703,'Angela');
INSERT INTO hackers VALUES(36396,'Frank');
INSERT INTO hackers VALUES(38289,'Patrick');
INSERT INTO hackers VALUES(44065,'Lisa');
INSERT INTO hackers VALUES(53473,'Kimberly');
INSERT INTO hackers VALUES(62529,'Bonnie');
INSERT INTO hackers VALUES(79722,'Michael');

 

문제풀이

인터넷 상에서 정답으로 나오는 코드는 대부분은 inner에 있는 테이블이 outer에 있는 테이블을 참조하는 식으로 되어 있는데 이런 코드를 개인적으로 좋아하지도 않고 이해하기도 쉽지 않다고 생각한다. 정직하게 join하는 것을 좋아한다...;;

아무튼 아래에 작성한 코드가 깔끔하지 않을 수 있고 물론 최선의 방법도 아닐 수 있지만 초보자도 이해하기는 쉬울거라고 생각한다. (MS-SQL로 작성하였다.)

 

이 문제의 결과는 아래처럼 4개의 컬럼이 조회되어야 한다.

- submission_date : 날짜

- cnt : 당일까지 매일 제출한 사람(hacker)의 수 

         3/1일 - 시작일이므로 이 날에 제출한 사람을 모두 카운트

         3/2일 - 3/1~3/2일까지 매일 제출한 사람 카운트

         3/3일 - 3/1~3/3일까지 매일 제출한 사람 카운트

- min_hacker_id : 해당일에 제출한 사람 중 가장 많이 제출한 사람의 hacker_id

         (만약 제출횟수가 동일하다면 hacker_id의 min값으로 선택)

- name : 위의 min_hacker_id에 해당되는 사람의 이름

 

결과값 중에서 "당일까지 매일 제출한 사람의 수"와 "해당일에 제출한 사람중 가장 많이 제출한 사람"은 나눠서 코드를 작성하였다.

우선 "당일까지 매일 제출한 사람의 수"를 구해보자. 시작일인 3/1일까지 매일 제출한 사람은 1번 제출한 사람일 것이고, 3/2일까지 매일 제출한 사람은 3/1~3/2일 이렇게 2번 제출한 사람이다. 이런 식으로 한다면 3/6일까지 매일 제출한 사람은 3/1~3/6일까지 6번 제출한 사람이 된다. 이렇게 매일 제출해야 하는 과제의 수는 아래와 같이 일자별로 1씩 증가되는 값이 될 것이다. distinct(submission_date)를 기준으로 rank를 매기면 일자별로 1씩 증가되는 값을 얻을 수 있다.

/*
	PART A
*/

--일자별 제출해야 하는 과제 count
select submission_date
	, dense_rank() over(order by submission_date) [rank_date]
from (
  select distinct submission_date
  from submissions
	) a

이제 매일 제출한 학생의 수를 카운트해보자. 위와 마찬가지로 일자별로 dense_rank() 함수를 사용하는데 이번에는 partition by hacker_id 라는 구문을 추가할 것이다. 즉, hacker_id가 동일한 경우에만 일자별로 rank값이 증가하게 된다.

--매일 제출한 학생
select submission_date
	, hacker_id
	, dense_rank() over(partition by hacker_id order by submission_date) [rank_hacker]
from submissions
order by submission_date, hacker_id

hacker_id만 표시가 될 경우 가독성이 좋지 않았기 때문에 아래처럼 hackers 테이블과 join 하여 name값을 추가해 주었다. 최종 결과에는 불필요한 과정이지만 중간 과정의 결과를 확인하기에 좋다. 3/1일에는 모두 한 번씩 제출하였으므로 rank_hacker의 값이 모두 1이다. 3/2일에는 1일에 제출한 사람인 Angela와 Michael의 rank_hacker의 값이 2로 증가하였다. 반면 3/2일 처음 제출한 Rose의 경우에는 1로 카운트된 것을 확인할 수 있다. 

/*
	PART B
*/
select submission_date
	, a.hacker_id
	, b.name
	, dense_rank() over(partition by a.hacker_id order by submission_date) [rank_hacker]
from submissions a
	join hackers b
	on a.hacker_id = b.hacker_id
order by submission_date, a.hacker_id

PART A에서 일자별로 최대로 제출해야 하는 과제의 수를 구했고 PART B에서 일자별로 실제로 제출한 학생의 수를 구하였다. 이제 PART A, PART B 코드를 join하여 일자별로 최대로 제출해야 하는 과제를 실제로 제출한 학생의 수를 구할 수 있다. count(distinct ob.hacker_id)로 매일 제출한 학생의 수를 구하는데 distinct를 하는 이유는 3/2일의 Michael처럼 2번 제출한 사람은 1번으로 계산해야 하기 때문이다. 

/*
	PART C
*/
select oa.submission_date, count(distinct ob.hacker_id) [cnt]
from (
	/* PART A */
	select submission_date
		, dense_rank() over(order by submission_date) [rank_date]
	from (
		select distinct submission_date
		from submissions
		) a
	) oa
	join (
   	 /* PART B */
	select submission_date
		, a.hacker_id
		, b.name
		, dense_rank() over(partition by a.hacker_id order by submission_date) [rank_hacker]
	from submissions a
		join hackers b
		on a.hacker_id = b.hacker_id
	) ob
	on oa.submission_date =  ob.submission_date
	and oa.rank_date = ob.rank_hacker
group by oa.submission_date
order by oa.submission_date

 

이제 "해당일에 제출한 사람중 가장 많이 제출한 사람"를 구해보자. 제출한 사람의 수가 동일한 경우에는 min(hacker_id)를 출력해야 한다. 우선 일자별로 학생이 제출한 과제수를 구한다.

/*
	PART D
*/
select submission_date, hacker_id, count(*) [cnt]
from submissions
group by submission_date, hacker_id
order by submission_date, hacker_id

최종적으로 원하는 결과는 위에서 나온 결과 중에서 가장 많이 제출한 사람만을 찾는 것인데 이것을 위해 "일자별 최대 제출수"를 구한다.

/*
	PART E
*/
select submission_date, max(cnt) [max_cnt]
from (
	/* PART D */
	select submission_date, hacker_id, count(*) [cnt]
	from submissions
	group by submission_date, hacker_id
	) a
group by submission_date

PART D에서 일자별로 학생이 제출한 과제수를 구했고 PART E에서는 일자별로 가장 많이 제출한 과제수를 구하였다. 두 결과를 JOIN하여 일자별로 가장 많이 제출한 학생을 구할 수 있다. 출력된 데이터를 보면 먼가 결과가 안나온것 같기도 하지만 잘 나온 결과이다!! 아까 말하였듯이 2번 제출한 학생이 있는 3/2일과 3/5일의 경우 79722, 36396 학생 1명만 정확이 나왔다. 다른 일자는 모든 학생이 과제를 동일한 횟수로 제출한 날인 것이다. 

/*
	PART F
*/
select a.submission_date, a.hacker_id
from (
	select submission_date, hacker_id, count(*) [cnt]
	from submissions
	group by submission_date, hacker_id
	) a
	join (
	select submission_date, max(cnt) [max_cnt]
	from (
		select submission_date, hacker_id, count(*) [cnt]
		from submissions
		group by submission_date, hacker_id
		) a
	group by submission_date
	) b
	on a.submission_date = b.submission_date
	and a.cnt = b.max_cnt	
order by a.submission_date

이렇게 모두 동일하게 제출한 경우에는 문제에서 min(hacker_id) 값을 구하라고 하였다. PART F에서 min값만 추가해주면 된다. 

/*
	PART G
*/
select a.submission_date, min(a.hacker_id) [min_hacker_id]
from (
	select submission_date, hacker_id, count(*) [cnt]
	from submissions
	group by submission_date, hacker_id
	) a
	join (
	select submission_date, max(cnt) [max_cnt]
	from (
		select submission_date, hacker_id, count(*) [cnt]
		from submissions
		group by submission_date, hacker_id
		) a
	group by submission_date
	) b
	on a.submission_date = b.submission_date
	and a.cnt = b.max_cnt	
group by a.submission_date
order by a.submission_date

마지막으로 문제에서 원하는 결과는 min_hacker_id와 함께 name을 출력해 주는 것이므로 PART G를 hackers 테이블과 join하여 name 값을 가져온다. 

/*
	PART H
*/
select oa.submission_date, oa.min_hacker_id, ob.name
from (
	select a.submission_date, min(a.hacker_id) [min_hacker_id]
	from (
		select submission_date, hacker_id, count(*) [cnt]
		from submissions
		group by submission_date, hacker_id
		) a
		join (
		select submission_date, max(cnt) [max_cnt]
		from (
			select submission_date, hacker_id, count(*) [cnt]
			from submissions
			group by submission_date, hacker_id
			) a
		group by submission_date
		) b
		on a.submission_date = b.submission_date
		and a.cnt = b.max_cnt	
	group by a.submission_date
	) oa
	join hackers ob
	on oa.min_hacker_id = ob.hacker_id	
order by oa.submission_date

위에서 구한 PART C, PART H 최종 결과 값을 서로 join하여 문제를 풀 수 있다. 이미 일자별로 결과가 다 구해진 상태이므로 submission_date로만 join하면 된다. 

/*
	PART FINAL
*/
select ooa.submission_date, ooa.cnt, oob.min_hacker_id, oob.name
from (	
	select oa.submission_date, count(distinct ob.hacker_id) [cnt]
	from (
		select submission_date
			, dense_rank() over(order by submission_date) [rank_date]
		from (
			select distinct submission_date
			from submissions
			) a
		) oa
		join (
		select submission_date
			, a.hacker_id
			, b.name
			, dense_rank() over(partition by a.hacker_id order by submission_date) [rank_hacker]
		from submissions a
			join hackers b
			on a.hacker_id = b.hacker_id
		) ob
		on oa.submission_date =  ob.submission_date
		and oa.rank_date = ob.rank_hacker
	group by oa.submission_date
	) ooa
	join (
	select oa.submission_date, oa.min_hacker_id, ob.name
	from (
		select a.submission_date, min(a.hacker_id) [min_hacker_id]
		from (
			select submission_date, hacker_id, count(*) [cnt]
			from submissions
			group by submission_date, hacker_id
			) a
			join (
			select submission_date, max(cnt) [max_cnt]
			from (
				select submission_date, hacker_id, count(*) [cnt]
				from submissions
				group by submission_date, hacker_id
				) a
			group by submission_date
			) b
			on a.submission_date = b.submission_date
			and a.cnt = b.max_cnt	
		group by a.submission_date
	) oa
	join hackers ob
	on oa.min_hacker_id = ob.hacker_id	
	) oob
	on ooa.submission_date = oob.submission_date
order by ooa.submission_date

 

Congratulations

 

Comment

HACKERRANK의 경우 버전이 낮은 것인지 DENSE_RANK() 함수 지원이 안되는 듯하다. 이 부분만 지원된다면 ALIAS 부분만 수정하여 MYSQL로도 사용이 가능할 듯하다.