HackerRank) 15 Days of Learning SQL 풀이 2일차

24.07.10

 

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.

 

매일 제출(대회 첫날부터 시작)을 하고, 매일 가장 많은 제출을 한 해커의 hacker_id 와 이름 을 출력하는 SQL 문을 제출하는 문제

핵심은 매일 빠지지 않고 출석한 사람을 구하는 것과 동시에 당일 제출한 과제 수를 체크하여 과제를 가장 많이 제출한 사람의 hacker_id와 이름을 출력한다

또한 해당 일에 빠지지 않고 과제를 제출 한 사람의 수도 동시에 구해야 한다

 

어제 풀었던 부분을 이어서 진행하여 위의 문제를 풀기 시작

 

3. 2번에서 작성한 sql문을 기반으로 hacker_id의 낮은 순번의 첫번째 아이디 출력하는 sql문 작성

select S.submission_date, count(S.hacker_id), min(S.hacker_id), (select h.name from Hackers as h where h.hacker_id = min(S.hacker_id))
from Submissions as S
where S.hacker_id in (select a.hacker_id
    from Submissions as a
    where a.submission_date <= S.submission_date
    group by a.hacker_id
    having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
)
group by S.submission_date

 

위 방법은 min을 줘서 검색된 데이터 중의 hacker_id를 가장 낮은 값을 가져오는 걸로 문제가 있어 보여 일단 먼저 해당 일자의 연속 출석 인을 구한 뒤 그 날 제출한 갯수를 세서 가장 많이 낸 사람들을 구하는 sql문으로 변경

select sub.submission_date, max_sub.total_count, sub.hacker_id, h.name
from (
    select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
    from Submissions as S
    where S.hacker_id in (select a.hacker_id
        from Submissions as a
        where a.submission_date <= S.submission_date
        group by a.hacker_id
        having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
    )
    group by S.submission_date, S.hacker_id
) as sub inner join (
    select submission_date, max(cnt_submission) "max_cnt_submission", count(hacker_id) "total_count"
    from(
        select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
        from Submissions as S
        where S.hacker_id in (select a.hacker_id
            from Submissions as a
            where a.submission_date <= S.submission_date
            group by a.hacker_id
            having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
        )
        group by S.submission_date, S.hacker_id
    ) t
    group by submission_date
)as max_sub on sub.submission_date = max_sub.submission_date and sub.cnt_submission = max_sub.max_cnt_submission
join Hackers as h on sub.hacker_id = h.hacker_id
order by sub.submission_date, sub.hacker_id

 

4. 3번 수정본을 가지고 매일 빠지지 않고 출석한 사람을 구하는 것과 동시에 당일 제출한 과제 수를 체크하여 과제를 가장 많이 제출한 사람의 hacker_id와 이름을 출력 하는 sql 작성

select last_table.*, h.name from (
    select submission_date, total_count, min(hacker_id) "min_hacker_id"
    from (
        select sub.submission_date, max_sub.total_count, sub.hacker_id
        from (
            select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
            from Submissions as S
            where S.hacker_id in (select a.hacker_id
                from Submissions as a
                where a.submission_date <= S.submission_date
                group by a.hacker_id
                having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
            )
            group by S.submission_date, S.hacker_id
        ) as sub inner join (
            select submission_date, max(cnt_submission) "max_cnt_submission", count(hacker_id) "total_count"
            from(
                select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
                from Submissions as S
                where S.hacker_id in (select a.hacker_id
                    from Submissions as a
                    where a.submission_date <= S.submission_date
                    group by a.hacker_id
                    having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
                )
                group by S.submission_date, S.hacker_id
            ) t
            group by submission_date
        )as max_sub on sub.submission_date = max_sub.submission_date and sub.cnt_submission = max_sub.max_cnt_submission
    ) as tt
    group by submission_date, total_count
) as last_table inner join Hackers h on last_table.min_hacker_id = h.hacker_id
order by last_table.submission_date

 

 

해결 방안

해당 문제를 제출 한 곳에서 예시로 출력해 준 부분을 온라인으로 Mysql를 사용할 수 있는 곳에 가서 해당 테이블들을 생성하고 값을 입력해 보았다.

https://www.mycompiler.io/ko/new/mysql

새 MySQL 프로그램 만들기 - 마이컴파일러 - myCompiler

-- create a table
CREATE TABLE Submissions (
	submission_date DATETIME NOT NULL,
	submission_id INT(11),
	hacker_id INT(11),
	score INT(11)
);

CREATE TABLE Hackers (
	hacker_id INT(11) NOT NULL,
	name VARCHAR(20)
);
-- insert some values
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,25);
INSERT INTO Submissions VALUES ('2016-03-02',44364,79722,70);

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',72853,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);



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');
-- fetch some values
select last_table.*, h.name from (
    select submission_date, total_count, min(hacker_id) "min_hacker_id"
    from (
        select sub.submission_date, max_sub.total_count, sub.hacker_id
        from (
            select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
            from Submissions as S
            where S.hacker_id in (select a.hacker_id
                from Submissions as a
                where a.submission_date <= S.submission_date
                group by a.hacker_id
                having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
            )
            group by S.submission_date, S.hacker_id
        ) as sub inner join (
            select submission_date, max(cnt_submission) "max_cnt_submission", count(hacker_id) "total_count"
            from(
                select S.submission_date, S.hacker_id, count(S.submission_date) "cnt_submission"
                from Submissions as S
                where S.hacker_id in (select a.hacker_id
                    from Submissions as a
                    where a.submission_date <= S.submission_date
                    group by a.hacker_id
                    having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
                )
                group by S.submission_date, S.hacker_id
            ) t
            group by submission_date
        )as max_sub on sub.submission_date = max_sub.submission_date and sub.cnt_submission = max_sub.max_cnt_submission
    ) as tt
    group by submission_date, total_count
) as last_table inner join Hackers h on last_table.min_hacker_id = h.hacker_id
order by last_table.submission_date

 

결과 

 

 

예시 결과

 

내가 생각했던 부분과 달리 예시에서는 연속으로 출석한 사람 수만 체크하고 과제는 당일 제출 한 수로 체크를 하는거 같았다

이를 적용하여 sql 코드 수정

select first_s.*, last_s.min_hacker_id, last_s.name
from (
    select S.submission_date,count(DISTINCT hacker_id)
    from Submissions as S
    where S.hacker_id in (select a.hacker_id
        from Submissions as a
        where a.submission_date <= S.submission_date
        group by a.hacker_id
        having count(DISTINCT a.submission_date ) >= (DATEDIFF(S.submission_date,'2016-03-01') +1  )
    )
    group by S.submission_date
)as first_s inner join (
    select serch_s.submission_date, serch_s.min_hacker_id, h.name
    from(
        select total_cnt_s.submission_date, min(total_cnt_s.hacker_id) "min_hacker_id"
        from 
        (
            select submission_date, hacker_id, count(submission_date) "cnt_submission"
            from Submissions
            group by submission_date, hacker_id
        ) as total_cnt_s inner join (
            select submission_date, max(cnt_submission) "max_cnt_submission"
            from(
                select submission_date, hacker_id, count(submission_date) "cnt_submission"
                from Submissions
                group by submission_date, hacker_id
            ) t
            group by submission_date
        ) as max_cnt_s on total_cnt_s.submission_date = max_cnt_s.submission_date and total_cnt_s.cnt_submission = max_cnt_s.max_cnt_submission
        group by total_cnt_s.submission_date
    ) as serch_s inner join Hackers as h on serch_s.min_hacker_id = h.hacker_id
) last_s on first_s.submission_date = last_s.submission_date
order by first_s.submission_date

 

정상적으로 통과가 되었다...