Skip to content
Advertisement

find total unique number of hackers who made at least one submission every day and find the hacker_id who made maximum number of submissions each day

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 ;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement