We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
This will only work if the minimum date of dataset starts with "1" as we compare Day from count of hacker_id occurence, a more end to end version is this:
select table1.submission_date,table2.unique_count,table1.hacker_id,table1.name from (select c.submission_date,c.hacker_id,d.name from (select submission_date,hacker_id,count_hack, row_number() over (partition by submission_date order by submission_date,count_hack desc,hacker_id) R_N from (select submission_date,hacker_id,count(hacker_id) as count_hack from submissions group by submission_date,hacker_id order by submission_date,count(hacker_id))) c LEFT JOIN HACKERS d ON c.hacker_id = d.hacker_id where R_N = 1) table1 INNER JOIN (select submission_date,count(distinct hacker_id) as unique_count from (select a.submission_date,b.hacker_id,count(DIStINCt b.submission_date) as cnt from (select distinct submission_date from submissions order by submission_date) a ,submissions b where b.submission_date <= a.submission_date group by a.submission_date,b.hacker_id order by a.submission_date) where cnt = (submission_date - (select min(submission_date) from submissions))+1 group by submission_date order by submission_date) table2 on table1.submission_date = table2.submission_date ;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →
This will only work if the minimum date of dataset starts with "1" as we compare Day from count of hacker_id occurence, a more end to end version is this: select table1.submission_date,table2.unique_count,table1.hacker_id,table1.name from (select c.submission_date,c.hacker_id,d.name from (select submission_date,hacker_id,count_hack, row_number() over (partition by submission_date order by submission_date,count_hack desc,hacker_id) R_N from (select submission_date,hacker_id,count(hacker_id) as count_hack from submissions group by submission_date,hacker_id order by submission_date,count(hacker_id))) c LEFT JOIN HACKERS d ON c.hacker_id = d.hacker_id where R_N = 1) table1 INNER JOIN (select submission_date,count(distinct hacker_id) as unique_count from (select a.submission_date,b.hacker_id,count(DIStINCt b.submission_date) as cnt from (select distinct submission_date from submissions order by submission_date) a ,submissions b where b.submission_date <= a.submission_date group by a.submission_date,b.hacker_id order by a.submission_date) where cnt = (submission_date - (select min(submission_date) from submissions))+1 group by submission_date order by submission_date) table2 on table1.submission_date = table2.submission_date ;