https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem
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
댓글