HackerRank 공부 5

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem

15 Days of Learning SQL | HackerRank

 

3일만에 풀었다.

정말 오랜만에 끝까지 스스로 풀고 싶다는 생각을 놓지 않고, 풀어냈다.

들인 시간보다 얻은 것이 더많다.

 

Write a query to print total number of unique hackers who made at least  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.

 

문제 핵심

---

1. unique hackers who made at least  submission each day

2.  who made maximum number of submissions each day

3. more than one such hacker has a maximum number of submissions, print the lowest hacker_id

4. sorted by the date.

 

내 접근 방식

---

with temp as
(
    select hacker_id, submission_date, submission_id
    from submissions
    where submission_date = '2016-03-01'
    union all
    select y.hacker_id, y.submission_date, y.submission_id
    from temp x
    join submissions y
        on x.hacker_id = y.hacker_id
        and dateadd(day,1,x.submission_date) = y.submission_date
),
temp2  as
(
    select submission_date, hacker_id, count(submission_id) as dd
    from submissions
    group by submission_date, hacker_id
)
, temp3 as
(
select submission_date, count(distinct hacker_id) as ee
from temp
group by submission_date
)
, temp4 as
(
    select submission_date, max(dd) as max_sub
    from temp2
    group by submission_date
)
, temp5 as
(
    select t1.submission_date, min(t2.hacker_id) as hck
    from temp4 t1
    inner join temp2 t2
    on t1.submission_date = t2.submission_date and t1.max_sub = t2.dd
    group by t1.submission_date
)

select t1.submission_date, t1.ee, t2.hck, t3.name
from temp3 t1
inner join temp5 t2
on t1.submission_date = t2.submission_date
inner join Hackers t3
on t2.hck = t3.hacker_id
order by t1.submission_date
;

 

• Recursive 테이블로 전 날 기준 해당일에도 문제를 푼 사람들만 남기며 테이블 생성했음
• 일자-해커별 서브미션 갯수를 구한뒤, 그 중 가장 많이 푼 사람을 join 형태로 구함

 

다른 사람 풀이

---

WITH submissions_grpd AS
(
    SELECT submission_date, hacker_id, COUNT(1) AS tot_count 
    FROM Submissions 
    GROUP BY submission_date, hacker_id
),
unique_hackers AS
(
    SELECT submission_date, COUNT(hacker_id) AS no_of_hackers FROM
    (
        SELECT submission_date, hacker_id, 
        ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS hacker_row_Num,
        DENSE_RANK() OVER (ORDER BY submission_date) AS Date_rank
        FROM submissions_grpd
    ) B
    WHERE hacker_row_Num = Date_rank
    GROUP BY submission_date
)
SELECT uniq.submission_date, uniq.no_of_hackers, hack.hacker_id, Hackers.name
FROM unique_hackers uniq
INNER JOIN
(
    SELECT submission_date, hacker_id, tot_count max_count FROM 
    (
        SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY tot_count DESC, hacker_id) Row_num
        FROM submissions_grpd
    ) A
    WHERE Row_num = 1
) hack
ON
hack.submission_date = uniq.submission_date
INNER JOIN
Hackers
ON
hack.hacker_id = Hackers.hacker_id

 

 

row number와 dense_Rank의 차이를 활용해서 하루라도 뺴먹은 사람을 걸러냄(오졌다)

ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS hacker_row_Num,
        DENSE_RANK() OVER (ORDER BY submission_date) AS Date_rank

 

row number()에 order by를 tot_count와 hacker_id로 해서 row_num =1로 max값 찾아냄

(
    SELECT submission_date, hacker_id, tot_count max_count FROM 
    (
        SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY tot_count DESC, hacker_id) Row_num
        FROM submissions_grpd
    ) A
    WHERE Row_num = 1