mysql - SQL 问题涉及已解决的挑战数量

Question

以下问题基于 HackerRank 问题 here 。写法如下:

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.

在挑战中，我提交了以下内容，但由于某种原因存在语法错误。有什么问题吗？

 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) < 
                              (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 <> hh.hacker_id 
          ) c 
      group 
         by h.hacker_id
          , h.name
      order 
         by count(c.challenge_id)
          , h.hacker_id desc

Answer 1

我无法评论以下内容的有效性，但它至少在语法上是有效的......

select c.hacker_id
     , h.name 
     , count(c.hacker_id) c_count
  from hackers h
  join challenges c 
    on c.hacker_id = h.hacker_id
 group 
    by c.hacker_id
     , h.name
having c_count = 
   ( SELECT MAX(temp1.cnt) 
       from 
          ( SELECT COUNT(hacker_id) cnt
              from challenges
             group 
                by hacker_id
          ) temp1
    ) 
    or c_count in 
        (select t.cnt
           from 
              ( select count(*) cnt 
                  from challenges
                 group 
                    by hacker_id
              ) t
          group 
             by t.cnt
         having count(t.cnt) = 1)
          order 
             by c_count DESC
              , c.hacker_id