hackerrank - Basic Join - Challenges

这道题考察了having以及sub-query

---

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.

---

该题有几个问题需要求出：每个学生完成的challengs的数目，可通过join两个table后group by算出；提取出完成了最多challenges数的学生，需要通过sub-query得出；提取出完成数目unique（即没有两个及其以上的学生完成了相同数目的challenges）的学生，首先算出每个学生的完成的challenges数，再算出challenges数为unique的学生。

coding如下：

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

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

/* after they have been grouped by hacker */
Group by h.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 the max count */
c_count = 
(select max(t1.num)
 from (select count(hacker_id) as num
       from Challenges
       group by hacker_id
       order by hacker_id) t1
) 
/* or anyone who's count is in.. */
or c_count in 
/* the set of counts.. */
(select t2.n
from (select count(*) as n
     from Challenges
     Group by hacker_id) t2
 /* who's group of counts */
 group by t2.n
 /* has only one element */
 having count(t2.n) = 1)

/* finally, the order the rows should be output */
Order by c_count desc, c.hacker_id;