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.
I found this pretty impossible until reading the discussions, so for anyone looking for hints, this is what they are asking for:
select
Submission_date,
Number of hackers who have submitted every day so far,
Hacker_id of the hacker with the most submits on this day,
(regardless of which other days they submitted)
Their_name
And here is my Oracle solution...
/* tree through submissions starting with first day, *//* following hacker_ids from day to day *//*returns: Submission_date, numberOfHackersStillSubmittingEveryDay */withDAYSASLEVELSas(selectSubmission_date,count(distinctHacker_id)asNumHackersfromSUBMISSIONSgroupbySubmission_datestartwithSubmission_date='2016-03-01'connectbypriorto_date(Submission_date,'yyyy-mm-dd')=to_date(Submission_date,'yyyy-mm-dd')-1andpriorHacker_id=Hacker_idandto_date(Submission_date,'yyyy-mm-dd')<=to_date('2016-03-15','yyyy-mm-dd')),/* returns: Submission_date, Hacker_id, numberOfHacks *//* for each hacker on each day */MULTIHACKERSas(selectSubmission_date,Hacker_id,count(Submission_id)asNumHacksfromSUBMISSIONSwhereto_date(Submission_date,'yyyy-mm-dd')betweento_date('2016-03-01','yyyy-mm-dd')andto_date('2016-03-15','yyyy-mm-dd')groupbySubmission_date,Hacker_idhavingcount(Submission_id)>0),/* returns: Submission_date, maxNumberOfHacks on that day */MOSTHACKSas(selectSubmission_dateasSubdate,max(NumHacks)asMaxHacksfromMULTIHACKERSgroupbySubmission_date),/* returns: date, numHackersWithSubEveryDaySoFar, *//* smallestHackerIdWithMaxhacks */SOLNBYIDas(selectMH1.Submission_dateasSdate,NumHackers,min(MH2.Hacker_id)asWinnerIdfromMULTIHACKERSMH1innerjoinMULTIHACKERSMH2onMH1.Submission_date=MH2.Submission_dateinnerjoinMOSTHACKSonMH2.NumHacks=maxHacksandMH2.Submission_date=MOSTHACKS.SubdateinnerjoinDAYSASLEVELSonMH1.Submission_date=DAYSASLEVELS.Submission_dategroupbyMH1.Submission_date,NumHackers)/* tack on the Name of the winning Hacker_id */selectSdate,NumHackers,H.Hacker_id,H.Namefrom(SOLNBYIDinnerjoinHACKERSHonH.Hacker_id=WinnerId)orderbyto_date(Sdate,'yyyy-mm-dd');
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 →
I found this pretty impossible until reading the discussions, so for anyone looking for hints, this is what they are asking for:
And here is my Oracle solution...