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.
Thanks for sharing @fengli97 - distinct hacker id count in second field is not trivial. I renamed subqueries to learn more. Figured I'd share it.
/*submission_datehackerCount,submissionCount,dates used to get number of unique submissions each daytopHack - hacker_id with most submissions for each day dates - list unique contest dates*/SELECTsubmission_date,(SELECTCOUNT(distincthacker_id)FROMSubmissionshackerCountWHEREhackerCount.submission_date=dates.submission_dateAND(SELECTCOUNT(distinctsubmissionCount.submission_date)FROMSubmissionssubmissionCountWHEREsubmissionCount.hacker_id=hackerCount.hacker_idANDsubmissionCount.submission_date<dates.submission_date)=dateDIFF(dates.submission_date,'2016-03-01')),(SELECThacker_idFROMsubmissionshackerListWHEREhackerList.submission_date=dates.submission_dateGROUPBYhacker_idORDERBYcount(submission_id)DESC,hacker_idlimit1)astopHack,(SELECTnameFROMhackersWHEREhacker_id=topHack)FROM(SELECTdistinctsubmission_datefromsubmissions)datesGROUPBYsubmission_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 →
Thanks for sharing @fengli97 - distinct hacker id count in second field is not trivial. I renamed subqueries to learn more. Figured I'd share it.