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.
Once we have this clear we shall focus on obtaining the values according to the two constraits.
1st: selecting the unique counter values
SELECTaux_table.counterFROM(SELECThacker_id,COUNT(challenge_id)AScounterFROMchallengesGROUPBYhacker_idORDERBYcounterDESC)ASaux_tableGROUPBYaux_table.counter/*Grouping by counter scores*/HAVINGCOUNT(aux_table.counter)=1;/*We are only selecting those values whose counter score are unique.*/
Once we have these two values we can build the QUERY all together:
---/*Building the query all together*/---/*1st SELECTING THE VALUES requested to be printed*/SELECTh.hacker_id,h.name,COUNT(c.challenge_id)ASchallenge_counterFROMhackershJOINchallengescONh.hacker_id=c.hacker_idGROUPBYh.hacker_id,h.name/*2nd applying the values found before*/HAVINGchallenge_counterIN(SELECTaux_table.counterFROM(SELECThacker_id,COUNT(challenge_id)AScounterFROMchallengesGROUPBYhacker_idORDERBYcounterDESC)ASaux_tableGROUPBYaux_table.counterHAVINGCOUNT(aux_table.counter)=1)ORchallenge_counter=(SELECTMAX(aux_table.counter)FROM(SELECThacker_id,COUNT(challenge_id)AScounterFROMchallengesGROUPBYhacker_idORDERBYcounterDESC)ASaux_table)/* Finally we order as requested (by counter and hacker_id)*/ORDERBYchallenge_counterDESC,h.hacker_idASC;
`
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Challenges
You are viewing a single comment's thread. Return to all comments →
Posting my thought process (and code) in order to solve this. The problem gets way less complicated if you face it from "inside" to "outside":
We have two constraints: 1. We have to select counter values that have unique scores 2. We have to select conter values that have maximum
(I name counter to the total number of challenges created by each student)
So 1st part is to select the counter values per each hacker_id:
Once we have this clear we shall focus on obtaining the values according to the two constraits.
1st: selecting the unique counter values
2nd: selecting the max counter value
Once we have these two values we can build the QUERY all together:
`