# Challenges — Hacker Rank — Advanced SQL Joins and Groups functions

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

Sample Input 0

Hackers Table:

Challenges Table:

Sample Output 0

`21283 Angela 688255 Patrick 596196 Lisa 1`

Sample Input 1

Hackers Table:

Challenges Table:

Sample Output 1

`12299 Rose 634856 Angela 679345 Frank 480491 Patrick 381041 Lisa 1`

Explanation

For Sample Case 0, we can get the following details:

Students 5077 and 62743 both created 4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

For Sample Case 1, we can get the following details:

Students 12299 and 34856 both created 6 challenges. Because is the maximum number of challenges created, these students are included in the result.

Solution

`SELECT h.hacker_id,        h.name,        Count(*) FROM   hackers h        INNER JOIN challenges c                ON h.hacker_id = c.hacker_id WHERE  h.hacker_id NOT IN (SELECT t1.hacker_id     FROM  (SELECT hacker_id,                  Count(*) AS cnt           FROM   challenges           GROUP  BY hacker_id) AS t1           INNER JOIN          (SELECT hacker_id,                 Count(*) AS cnt          FROM   challenges          GROUP  BY hacker_id) AS t2          ON             t1.hacker_id != t2.hacker_id             AND t1.cnt = t2.cnt             /* not the max ones*/            AND t1.cnt != (SELECT Count(*)                                    FROM   challenges                                    GROUP  BY hacker_id                                    ORDER  BY Count(*)                                   DESC                                    LIMIT  1)) GROUP  BY h.hacker_id ORDER  BY Count(*) DESC,           h.hacker_id`

Expected output:

`5120 Julia 50 18425 Anna 50 20023 Brian 50 33625 Jason 50 41805 Benjamin 50 52462 Nicholas 50 64036 Craig 50 69471 Michelle 50 77173 Mildred 50 94278 Dennis 50 96009 Russell 50 96716 Emily 50 72866 Eugene 42 37068 Patrick 41 12766 Jacqueline 40 86280 Beverly 37 19835 Joyce 36 38316 Walter 35 29483 Jeffrey 34 23428 Arthur 33 95437 George 32 46963 Barbara 31 87524 Norma 30 84085 Johnny 29 39582 Maria 28 65843 Thomas 27 5443 Paul 26 52965 Bobby 25 77105 Diana 24 33787 Susan 23 45855 Clarence 22 33177 Jane 21 7302 Victor 20 54461 Janet 19 42277 Sara 18 99388 Mary 16 31426 Carlos 15 95010 Victor 14 27071 Gerald 10 90267 Edward 9 72609 Bobby 8`

--

--

## More from Interview help, 1:1 mocks, strategies, AI, ML, DS

Mentor and prepare candidates for interviews. I write about solutions to known and upcoming problems. I educate and talk about mental health too.

## Get the Medium app

Mentor and prepare candidates for interviews. I write about solutions to known and upcoming problems. I educate and talk about mental health too.