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:
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 ;