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.
Loading...
  1. Practice
  2. SQL
  3. Basic Join
  4. Challenges
  5. Discussions

Challenges

Sort 361 Discussions, By:

votes

  • james98 + 26 comments

    Boy, I learned a lot doing this. Here is my mySQL code, with comments and similar, but not identical to other posts:

    /* these are the columns we want to output */
select c.hacker_id, h.name ,count(c.hacker_id) as c_count

/* this is the join we want to output them from */
from Hackers as h
    inner join Challenges as c on c.hacker_id = h.hacker_id

/* after they have been grouped by hacker */
group by c.hacker_id

/* but we want to be selective about which hackers we output */
/* having is required (instead of where) for filtering on groups */
having 

    /* output anyone with a count that is equal to... */
    c_count = 
        /* the max count that anyone has */
        (SELECT MAX(temp1.cnt)
        from (SELECT COUNT(hacker_id) as cnt
             from Challenges
             group by hacker_id
             order by hacker_id) temp1)

    /* or anyone who's count is in... */
    or c_count in 
        /* the set of counts... */
        (select t.cnt
         from (select count(*) as cnt 
               from challenges
               group by hacker_id) t
         /* who's group of counts... */
         group by t.cnt
         /* has only one element */
         having count(t.cnt) = 1)

/* finally, the order the rows should be output */
order by c_count DESC, c.hacker_id

/* ;) */
;
    • rahmatNazali + 0 comments

      The comments are easy to understands. Very beautiful.

    • hellosolvur + 0 comments

      Very nice. More people should comment their code in the discussion.

    • tucaoneo + 0 comments

      This doesn't work. Why people thumbed up this? Are we using different mysql's?

    • james98 + 0 comments

      Yup something has changed - can anyone tell me what needs fixing?

    • samarth128 + 1 comment

      Based on what I think (I am a newbie), for MySQL, you need to change the group_by in main Query to :

      group by c.hacker_id, h.name

      The reason for this is that when you group only c.hacker_id, SQL doesn't know that c.hacker_id and h.name are unique to eachother. Had same issue on some other questions.

      • bojanbudi44 + 0 comments

        good by adding that, my query is work

    • hartmann_stl + 1 comment

      james98, I copy/pasted and ran your script and it did not pass the code test for MySQL.

      • kumar397 + 1 comment

        change this: group by c.hacker_id to: group by c.hacker_id, h.name

        everything else is same

        • nellieliu0 + 1 comment

          it worked but how come by adding h.name this can work not the other way? thx

          • adityaoak + 0 comments

            Thats coz your group by clause should contain all columns that you used in the select clause

    • sowmithreddy + 0 comments

      we can't use order by in subquery with out top or offset

    • cosminvacaroiu + 0 comments

      I did it exactly the same, but in Oracle. Didn't took me that much time though. You need to find the max count (only 1) then a list of counts where there's only one hacker.

    • nithi_psg + 0 comments
      [deleted]
    • nithi_psg + 0 comments

      thank you..your logic is easy to understand...

      same in oracle : select h.hacker_id, h.name, count(c.challenge_id) as total from hackers h, challenges c where h.hacker_id=c.hacker_id group by h.hacker_id, h.name
      having count(c.challenge_id) in --either in max ( select max(total) from ( select count(*) as total from challenges group by hacker_id )
      ) or count(c.challenge_id) in--or should occur only once ( select total from ( select count(*) as total from challenges group by hacker_id) group by total having count(total)=1
      ) order by count(c.challenge_id) desc, h.hacker_id ;

    • debtanu97 + 0 comments

      Thank you very much, this one really helped a lot

    • kartik_chauhan + 0 comments

      Such an elegant solution. Kudos

    • Mike_Carnohan + 2 comments

      I arrived at essentially the same solution, only instead of your method of counting challenges of hackers found in the Challenges table without a join (which is certainly a clean way to do it), I reused the grouping from the original join—to compare the initially derived count to the counts that are unique—in the second portion of the HAVING/OR statement.

      Same cat differently skinned. Sharing since it is more explicit (though admittedly not as efficient) in the hopes it might help anyone stuck on how to arrive at the set of unique counts to include (after including max count achieving hackers).

      Note: The DISTINCT clause and c_unique alias are both unnecessary, but are there to keep me sane.

      SELECT h.hacker_id, 
       h.name, 
       COUNT(c.challenge_id) AS c_count
FROM Hackers h
JOIN Challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING c_count = 
    (SELECT COUNT(c2.challenge_id) AS c_max
     FROM challenges as c2 
     GROUP BY c2.hacker_id 
     ORDER BY c_max DESC limit 1)
OR c_count IN 
    (SELECT DISTINCT c_compare AS c_unique
     FROM (SELECT h2.hacker_id, 
                  h2.name, 
                  COUNT(challenge_id) AS c_compare
           FROM Hackers h2
           JOIN Challenges c ON c.hacker_id = h2.hacker_id
           GROUP BY h2.hacker_id, h2.name) counts
     GROUP BY c_compare
     HAVING COUNT(c_compare) = 1)
ORDER BY c_count DESC, h.hacker_id;

      Solution is MySQL specific, but easily portable.

      • karllchris + 2 comments

        what is the use of "counts" in GROUP BY h2.hacker_id, h2.name) counts? thanks

        • Mike_Carnohan + 0 comments

          In a literal sense, aliasing (like the use of "counts") helps you follow that the immediately prior code represents a logical "table" of values, and in many cases is required by MySQL.

          In the step above the portion involving "counts" (the first instance of a HAVING clause), we find the max number of challlenges for all hackers. In the following step (c_count IN...) we look to see (if they haven't completed the max number of challenges... hence "OR") if the count of challenges they have completed is unique (not shared by other hackers).

          The alias "counts" represents our compared to "table" of challenge values (done by grouping it by counts of challenges). We then check to see if c_count (COUNT(c.challege_id) from Hackers) is a unique number of challenges completed. In other words, the count(challeng_id) aliased as "c_compare" is unique when the count of the HAVING clause equals 1. It (the particular value of challenges completed) is unique because when compared, there is only one instance of that count of challenges completed.

          This is all done to meet this requirement in the problem: "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."

        • andtas + 0 comments

          If you don't include 'counts' (or some other alias name) then you will get an error because every "derived table" must have an alias, even if it is not referred to anywhere else.

      • eduardojacob + 0 comments

        Thank you very much for your help. I didn't understand from the text of the problem what was pretended. But looking for your query I understand it. I took the liberty to simplify your query a lot. I see that normaly people abuse of using aliases where they are not necessary. Here is my simplification:

        select H.hacker_id, H.name, count(*) as total
from Hackers H, Challenges C
where H.hacker_id = C.hacker_id
group by H.hacker_id, H.name
having total = 
    (select count(*) 
     from challenges
     group by hacker_id 
     order by count(*) desc limit 1
     )
or total in
    (select total
     from (
        select count(*) as total
        from Hackers H, Challenges C
        where H.hacker_id = C.hacker_id
        group by H.hacker_id, H.name
      ) counts
     group by total
     having count(*) = 1)
order by total desc, H.hacker_id asc
;
    • jayakrishnan_tvm + 0 comments

      Nice

    • mikemusijr + 0 comments

      thanks for taking the time to do this. Really helps the rest of us learning off your example.

    • km_yang + 0 comments

      This code works for me! (MySQL)

    • rajendrarathod + 0 comments

      Thanks man it helped..!

    • manimohamed + 0 comments

      That's Weird I tested the following query on Postgresql and it's working but it doesn't work on MySQL:

      SELECT h.hacker_id,
	h.name,
	COUNT(*) AS chal_count
FROM hackers AS h
	JOIN challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY 1, 2
HAVING COUNT(*) = (
	SELECT MAX(chal_count)
	FROM (
		SELECT COUNT(hacker_id) AS chal_count,
				hacker_id
		FROM challenges
		GROUP BY 2
	) AS t1
) OR COUNT(*) IN (
	SELECT DISTINCT chal_count
	FROM (
	SELECT chal_count,
		COUNT(chal_count) AS count_chal_count
	FROM (
		SELECT COUNT(hacker_id) AS chal_count,
				hacker_id
		FROM challenges
		GROUP BY 2
	) AS t2
	GROUP BY chal_count
	) AS t3
	WHERE count_chal_count = 1
)
ORDER BY 3 DESC, 1
    • hajar_salman35 + 0 comments

      having count(t.cnt) = 1

      I didn't get what this line doing

    • ysixsix + 0 comments

      Awesome. The subquery version works, but I like your version with better performance

      select h.hacker_id, h.name, tmp.challenges_created from Hackers h, (select hacker_id, count(challenge_id) challenges_created from Challenges group by hacker_id ) tmp where tmp.hacker_id = h.hacker_id and not exists (select 'x' from (select hacker_id, count(challenge_id) challenges_created from Challenges group by hacker_id ) tmp2 where tmp2.hacker_id != tmp.hacker_id and tmp2.challenges_created = tmp.challenges_created and tmp2.challenges_created < (select count(challenge_id) from Challenges group by hacker_id order by count(challenge_id) desc limit 1 ) ) order by challenges_created desc, hacker_id;

    • faradillah_suka1 + 0 comments

      I only got to half of this on my own /w\

    • vsharma030 + 0 comments

      :) Great help

    • saisowhit + 0 comments

      Dude how can you alias column name in having,doesn't it generate error?

    • vchandola94 + 0 comments

      Yes thank you for the comments. I learned a lot too from this problem :)

    • vandan_singh + 0 comments

      not using the alias c_count returns a query of only the highest no of submissions.could you please explain why?

    • [deleted] + 0 comments

      You need 'group by h.name' after the

      /* after they have been grouped by hacker */ group by c.hacker_id

  • sabin774 + 5 comments

    My solution:

    select c.hacker_id, h.name, count(c.challenge_id) as total from challenges c

inner join hackers h
on h.hacker_id= c.hacker_id

group by c.hacker_id having 

total=
(select  count(c1.challenge_id) from challenges c1
group by c1.hacker_id 
ORDER BY count(c1.challenge_id) desc limit 1)

or 

count(c.challenge_id) in 
(select t1.tot from ((select t.tot, count(t.hacker_id) from (select count(c2.challenge_id) as tot, c2.hacker_id from challenges c2
group by c2.hacker_id) as t
group by t.tot having count(t.hacker_id)=1) as t1))

order by total desc, h.hacker_id;
    • mschou05 + 1 comment

      right result, though the clause "select t1.tot" can be dropped if you just do "select t.tot" on the inner clause instead of "select t.tot, count(t.hacker_id)"

      • sabin774 + 1 comment

        thanks! (Y)

        • jessiebuy + 0 comments

          But the result does pass the test. Anyone know why?

    • princejaideep10 + 1 comment

      I DONT UNDERSTAND HOW THE FIRST QUERY WORKS! I'M STUCK CAN U PLEASE ELABORATE

      • sanjay_dev + 0 comments

        first will execute for highest value since order by desc will give highest value at top and limit 1 will choose only first value from top i.e max

    • punitdabhi007 + 0 comments

      Good work!

    • tucaoneo + 0 comments

      This doesn't work. Why people thumbed up this? Are we using different mysql's?

    • banerjee_a21 + 0 comments

      the inner query only gives you one value of 50, not the values for each group of hackers, hence the error

  • stormshadowx3 + 2 comments

    It took me an hour to correctly write the query. For people struggling with the logic I advice you to take a paper and pen and analyze how you can seperate the data which is required.

        select a.hacker_id,a.name,count(b.hacker_id)    
    from Hackers a, Challenges b
    WHERE a.hacker_id = b.hacker_id
    GROUP BY a.hacker_id,a.name
    HAVING count(b.hacker_id) not in (select  distinct          count(hacker_id) from Challenges
    WHERE hacker_id <> a.hacker_id
    group by hacker_id
    having count(hacker_id) < (select                           max(x.challenge_count) 
    from (select count(b.challenge_id) as challenge_count                 from Challenges b GROUP BY b.hacker_id) as x ))
    ORDER BY count(b.hacker_id) desc, a.hacker_id
    • ram9151 + 0 comments

      Can you please explain the query in detail..

    • v1ntg + 0 comments

      i did it a mix of the top 2 solutions but i like your method better

      can you clarify if 'where hacker_id <> a.hacker_id' compares all the id's < max with the first result in the main query?

      if so, then i get it

  • yogesh_joshi + 5 comments

    mysql

    select c.hacker_id, h.name, count(c.challenge_id) as cn
from challenges as c join hackers as h 
on c.hacker_id = h.hacker_id
group by c.hacker_id having 
cn = 
(select count(c1.challenge_id) from challenges as c1 
 group by c1.hacker_id
 order by count(c1.challenge_id) desc limit 1) 
or 
cn not in 
(select count(c2.challenge_id) from challenges as c2 
 group by c2.hacker_id having
 c.hacker_id != c2.hacker_id)
order by cn desc, c.hacker_id;
    • arpit88 + 2 comments

      plz explain how cn is being calculated according to your code

      • amarnathgona367 + 0 comments

        can you please elaborate how cn not in query works

      • suguorui + 0 comments

        In the first part, cn is to calculate the max number of challenges created; in the second part, cn is to exclude those entries with the same number of challenges created.

    • robinson_ma_hao1 + 0 comments

      This one doesn't work as it only get the hacker_id with max records

    • WittyNotes + 0 comments

      Could you explain the final section of your code:

      cn not in 
(select count(c2.challenge_id) from challenges as c2 
 group by c2.hacker_id having
 c.hacker_id != c2.hacker_id)

      It seems to me as if it's checking that the count of challenges is NOT in the set of 'unique' hackers.

    • juanfernandoct + 1 comment

      could you explain why do you use this line please? how does it work?

      c.hacker_id != c2.hacker_id
      • jyang316 + 0 comments

        (!=) = not equal

    • tucaoneo + 0 comments

      This doesn't work. Why people thumbed up this? Are we using different mysql's?

  • karimnot + 0 comments

    I think you have to split the problem, 1. Subquery where you find the max(ChallengedCount) 2. Subquery where find elements who appear at least two times except (subquery 1) 3. Query where find all elements except (suquery 2)

    I hope it help you all

  • nizamovich + 3 comments

    MSSQL solution with CTE 

    with c1 as (
	select hacker_id, count(*) as cc_ch
	from Challenges as c
	group by hacker_id
), c2 as (
	select max(cc_ch) as max_cc_ch 
	from c1
), c3 as (
	select cc_ch
	from c1
	where cc_ch < (select top 1 max_cc_ch from c2)
	group by cc_ch
	having count(*) = 1
	
	union 
	select max_cc_ch from c2
)
select h.hacker_id, h.name, c1.cc_ch
from c1 
join c3 on c1.cc_ch = c3.cc_ch
join Hackers as h on c1.hacker_id = h.hacker_id 
order by c1.cc_ch desc, hacker_id
    • BPricester + 0 comments

      I pasted your MSSQL solution and got an error. I have been unsuccessful at using CTEs on HackerRank. I am not sure if this is HackerRanks issue or mine.

      My solution could use some CTEs to clean it up.

      SET @max = (SELECT count(*) FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name ORDER BY count(*) DESC LIMIT 1);

      SELECT h.hacker_id, h.name, count(*) as count FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name -- Having the max count HAVING count(*) = @max

      UNION ALL

      SELECT h.hacker_id, h.name, count(*) as count FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING count(*) IN -- a list of challenge counts that = 1 and not max (SELECT a.challengeCounts FROM(SELECT h.hacker_id, h.name, count(*) as challengeCounts FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name) a GROUP BY a.challengeCounts -- Having count less than the max count HAVING count() < @max AND count()=1)

      ORDER BY count DESC, hacker_id

    • bobalpradeep + 0 comments

      one more way... only two ctes replaced c2 with the MAX window function in c1 ;with cChngCount as ( select
      H.hacker_id,H.name,count() chngcnt, MAX(count()) over () Maxy from hackers H join Challenges C on C.hacker_id = H.hacker_id group by H.hacker_id,H.name ) ,cXludeChngs as ( select chngcnt from cChngCount where chngcnt <> Maxy group by chngcnt having count(*) > 1 ) select CC.hacker_id,CC.name,chngcnt from cChngCount CC where not exists (select XC.chngcnt from cXludeChngs XC where XC.chngcnt = CC.chngcnt) order by chngcnt desc, hacker_id

    • electricmo + 2 comments

      Here's my attempt to solve the task using MSSQL's CTE:

      WITH COUNTS AS (
    SELECT HACKER_ID, COUNT(CHALLENGE_ID) AS TOTALCNT 
    FROM  CHALLENGES 
    GROUP BY HACKER_ID)

SELECT H.HACKER_ID, H.NAME, C.TOTALCNT 
FROM HACKERS H 
JOIN COUNTS C ON H.HACKER_ID = C.HACKER_ID
WHERE 
        C.TOTALCNT = (SELECT MAX(TOTALCNT) FROM COUNTS)
      OR
        C.TOTALCNT NOT IN (
            SELECT TOTALCNT FROM COUNTS AS T
            WHERE H.HACKER_ID != T.HACKER_ID)
ORDER BY C.TOTALCNT DESC, H.HACKER_ID
      • zhangzz2015 + 1 comment

        with clause can't for in hackerrank. and I think this part: C.TOTALCNT NOT IN ( SELECT TOTALCNT FROM COUNTS AS T WHERE H.HACKER_ID != T.HACKER_ID)

        is wrong...

        • electricmo + 1 comment

          Could you be more specific? I have double checked my solution and it still works fine ;)

          • zhangzz2015 + 1 comment

            I tried your code and it doesn't work. I think that is because hackerrank doen't support CTE(WITH clause). Moreover, would you please exlain how does this work—— SELECT TOTALCNT FROM COUNTS AS T WHERE H.HACKER_ID != T.HACKER_ID thank you

            • electricmo + 2 comments

              Please check first if you selected MS SQL Server from the dropdown list. If so, my solution should work fine, I even checked it today by inserting the code to the hackerrank's code editor and it passed the sample test case. Anyway, as we are supposed to print the total number of challenges created by each student the WHERE clause:

              WHERE 
        C.TOTALCNT = (SELECT MAX(TOTALCNT) FROM COUNTS)
      OR
        C.TOTALCNT NOT IN (
            SELECT TOTALCNT FROM COUNTS AS T
            WHERE H.HACKER_ID != T.HACKER_ID)

              ensures that the result includes all students with the maximum number of challenges as well as students whose number of challenges is lower but they are unique (no other student has the same number of challenges created). Hope it helps.

              • zhangzz2015 + 1 comment

                thanks a lot for your explanation. I'm a beginner ,so I can't quite understand: C.TOTALCNT NOT IN ( SELECT TOTALCNT FROM COUNTS AS T WHERE H.HACKER_ID != T.HACKER_ID) does this mean for every H.HACKER_ID, we SELECT all the numbers of different H.HACKER_ID? and this process will be executed for the number of H.HACKER_ID times in total? so exhausting...

                • electricmo + 1 comment

                  This line simply means that we are not interested in a situation where more than one student (hence != hacker_id) has the same number of challenges. Here's an alternative way (maybe more understandable for you) to include only those students who have a unique number of challenges (i.e. exclude the number of challenges lower than the max and occurring more than once). 

                  WHERE 
        C.TOTALCNT = (SELECT MAX(TOTALCNT) FROM COUNTS)
      OR
        C.TOTALCNT NOT IN (
            SELECT TOTALCNT FROM COUNTS
            GROUP BY TOTALCNT
            HAVING COUNT(TOTALCNT) > 1)

                  P.S. I totally understand that something maybe unclear for you and perhaps there are better ways to solve this task, but saying from the get-go that something is wrong is not the best way to understand things ;) All in all, I hope that after my explanation you have a better understanding of the idea :)

                  • zhangzz2015 + 1 comment

                    Thank you so much for showing me this way and I understood it completely. And sorry for saying wrong in a rush. I appreciate your help so much that I hope you can guide me more on SQL.

                    • electricmo + 0 comments

                      No problem and I'm glad I was able to help you to understand the concept better :)

              • ram9151 + 0 comments

                Hi, I am not able to understand the code for getting students who numbers of challenges is unique..can you please explain.

      • aadishchopra + 0 comments

        One of the best solutions !

  • voidpro + 1 comment

    Oracle Solution:

    SELECT HACKER_ID, NAME, CHALLENGES_CREATED FROM (
SELECT 
H.HACKER_ID,
H.NAME,
CHALLENGES_CREATED,
MAX(CHALLENGES_CREATED) OVER (ORDER BY 1) AS MX_CHLNG,
COUNT(CHALLENGES_CREATED) OVER(PARTITION BY CHALLENGES_CREATED) AS NO_OF_COMMON_CHLNG_HCKR
FROM HACKERS H
JOIN (SELECT DISTINCT
     HACKER_ID, COUNT(CHALLENGE_ID) OVER (PARTITION BY HACKER_ID) AS CHALLENGES_CREATED
     FROM CHALLENGES
     )C
ON H.HACKER_ID=C.HACKER_ID
)A
WHERE NO_OF_COMMON_CHLNG_HCKR=1 OR
(CHALLENGES_CREATED>=MX_CHLNG AND NO_OF_COMMON_CHLNG_HCKR!=1)
ORDER BY CHALLENGES_CREATED DESC, HACKER_ID
;
    • Stj11 + 1 comment

      Hi, can you please explain this solution?

      • voidpro + 1 comment
        Note these scenario:
1.Find total challenges created by a hacker --SELECT DISTINCT HACKER_ID, COUNT(CHALLENGE_ID) OVER (PARTITION BY HACKER_ID) AS CHALLENGES_CREATED FROM CHALLENGES
2.Find the maxinum number of challenges created --MAX(CHALLENGES_CREATED) OVER (ORDER BY 1) AS MX_CHLNG
3.Find the number of hackers for each no. of challenges created. --COUNT(CHALLENGES_CREATED) OVER(PARTITION BY CHALLENGES_CREATED) AS NO_OF_COMMON_CHLNG_HCKR
4.Applying the conditions to exclude more than one hacker for same no. of challenges created, the no. of challenges should be greater than max. no. of challenge --(CHALLENGES_CREATED>=MX_CHLNG AND NO_OF_COMMON_CHLNG_HCKR!=1)
5.Include the hackers who do not hold same no of challenge with others. --NO_OF_COMMON_CHLNG_HCKR=1
        • Stj11 + 1 comment

          I appreciate your effort. Thanks a lot :)

          • voidpro + 0 comments

            Anytime!!

  • simple_guitar99 + 1 comment

    MS SQL using WITH

    /* count total submissions of challenges of each user */
WITH data
AS
(
SELECT c.hacker_id as id, h.name as name, count(c.hacker_id) as counter
FROM Hackers h
JOIN Challenges c on c.hacker_id = h.hacker_id
GROUP BY c.hacker_id, h.name
)
/* select records from above */
SELECT id,name,counter
FROM data
WHERE
counter=(SELECT max(counter) FROM data) /*select user that has max count submission*/
OR
counter in (SELECT counter FROM data
GROUP BY counter
HAVING count(counter)=1 ) /*filter out the submission count which is unique*/
ORDER BY counter desc, id
    • lxy_celia + 0 comments

      Very clean!!!! I like it!

  • yubaraj + 2 comments

    Here is my solution and its working:

    select b.hacker_id, a.name, b.tot_cha from hackers a join (select hacker_id, count(challenge_id) as tot_cha from challenges group by hacker_id)b on a.hacker_id = b.hacker_id where b.tot_cha not in (select c.tot_cha from (select hacker_id, count(challenge_id) as tot_cha from challenges group by hacker_id)c where c.tot_cha != (select count(challenge_id) as tot_cha from challenges group by hacker_id order by tot_cha desc limit 1) group by c.tot_cha having count(c.tot_cha) > 1) order by b.tot_cha desc, b.hacker_id;

    • eswar_kai + 1 comment

      It doesnt work for oracle as oracle prevents order by clause in the subqueries.

      • yubaraj + 1 comment

        Sorry, I have not tested in oracle but it works in mysql :)

        • eswar_kai + 0 comments

          Indeed it works on MySQL. I had my version similar to yours but its always timed out.

          SELECT h.hacker_id, h.name, count(c.hacker_id) as cnt
          FROM hackers h
          JOIN challenges c
          on (h.hacker_id=c.hacker_id) group by h.hacker_id, h.name, c.hacker_id having count(c.challenge_id) not in ( SELECT max(total) FROM (SELECT c.hacker_id, count(c.challenge_id) TOTAL FROM challenges c GROUP BY c.hacker_id) c where c.total <> (SELECT count(c.challenge_id) TOT FROM challenges GROUP BY c.hacker_id )
          GROUP BY c.total having count(c.total) > 1 ) order by count(c.hacker_id) desc , h.hacker_id;

    • eswar_kai + 0 comments

      Excellent logic by the way. Cheers.

  • WittyNotes + 1 comment

    Can anyone explain simply why this code correctly calculates the maximum number of challenges solved by one person...

    select count(c1.challenge_id) from challenges as c1
    group by c1.hacker_id
    order by count(c1.challenge_id) desc limit 1

    ... but this throws an error?

    select max(count(c1.challenge_id)) from challenges as c1
    group by c1.hacker_id
    • rasaab1029 + 1 comment

      the issue with the second query is that you can't do a "max" and a "count" simultaneously. you need to chain them so that the result of count is fed to max. max is meant to be run over a group in the table, when you get the count for each hacker_id group, max doesn't know which group to use. you should use a subquery. check out this link: http://www.w3resource.com/sql/aggregate-functions/max-count.php

      • WittyNotes + 0 comments

        Thank you so much for the explanation!

Load more conversations