Find 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.
Here is the sample data: Hackers table:
x
15758 Rose
20703 Angela
36396 Frank
38289 Patrick
44065 Lisa
53473 Kimberly
62529 Bonnie
79722 Michael
Submissions table:
Submission_date submission_id hacker_id score
3/1/2016 8494 20703 0
3/1/2016 22403 53473 15
3/1/2016 23965 79722 60
3/1/2016 30173 36396 70
3/2/2016 34928 20703 0
3/2/2016 38740 15758 60
3/2/2016 42769 79722 25
3/2/2016 44364 79722 60
3/3/2016 45440 20703 0
3/3/2016 49050 36396 70
3/3/2016 50273 79722 5
3/4/2016 50344 20703 0
3/4/2016 51360 44065 90
3/4/2016 54404 53473 65
3/4/2016 61533 79722 45
3/5/2016 72852 20703 0
3/5/2016 74546 38289 0
3/5/2016 76487 62529 0
3/5/2016 82439 36396 10
3/5/2016 90006 36396 40
3/6/2016 90404 20703 0
for the above data, expected results is:
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
My below query doesnt give me unique hacker_ids
select submission_date, cnt, hacker_id, name from
(select s.submission_date
, count(s.hacker_id) over(partition by s.submission_date) cnt
, row_number() over(partition by s.submission_date order by s.hacker_id asc) rn
, s.hacker_id, h.name from submissions s
inner join hackers h on h.hacker_id = s.hacker_id) as tble
where tble.rn = 1;
How do I get the unique hacker_ids in the above results ?
Advertisement
Answer
select big_1.submission_date, big_1.hkr_cnt, big_2.hacker_id, h.name
from
(select submission_date, count(distinct hacker_id) as hkr_cnt
from
(select s.*
, dense_rank() over(order by submission_date) as date_rank
--, row_number() over(order by submission_date) as rn_date_rank
,dense_rank() over(partition by hacker_id order by submission_date) as hacker_rank
--,row_number() over(partition by hacker_id order by submission_date) as rn_hacker_rank
from submissions s ) a
where a.date_rank = a.hacker_rank
group by submission_date) big_1
join
(select submission_date,hacker_id,
rank() over(partition by submission_date order by sub_cnt desc, hacker_id) as max_rank
from (select submission_date, hacker_id, count(*) as sub_cnt
from submissions
group by submission_date, hacker_id) b ) big_2
on big_1.submission_date = big_2.submission_date and big_2.max_rank = 1
join hackers h on h.hacker_id = big_2.hacker_id
order by 1 ;