title: SQL Question Involving Number of Challenges Solved
body: <p>The following question is based on a HackerRank question <a href="https://www.hackerrank.com/challenges/challenges/problem" rel="nofollow noreferrer">here</a>. It is written as follows:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>In the challenge, I submitted the following, but for some reason there is a syntax error. What is the problem?</p>
  
  <pre><code> select h.hacker_id
       , h.name
       , count(c.challenge_id) count1 
    from hackers h 
    join challenges c
      on c.hacker_id = h.hacker_id 
   where h.hacker_id not in  
            (select hh.hacker_id 
               from hackers hh 
               join challenges cc
                 on cc.hacker_id = hh.hacker_id 
               join 
                  ( select hhh.hacker_id
                         , count(ccc.challenge_id) count2 
                      from hackers hhh 
                      join challenges ccc
                        on ccc.hacker_id = hhh.hacker_id 
                     group 
                        by hhh.hacker_id 
                    having count(ccc.challenge_id) &lt; 
                                (select max(count2) 
                                   from 
                                      ( select count(cc.challenge_id) count2 
                                          from hackers hh 
                                          join challenges cc
                                            on hh.hacker_id = cc.hacker_id
                                      ) a
                                 ) b
                  ) t 
                 on t.hacker_id &lt;&gt; hh.hacker_id 
            ) c 
        group 
           by h.hacker_id
            , h.name
        order 
           by count(c.challenge_id)
            , h.hacker_id desc
  </code></pre>