Exclude results based on test cases

Question

Write a request to print hacker_id, the name and total number of calls made by each student. Sort results by total number of problems in descending order. If more than one student has created the same number of problems, then sort the result using hacker_id. If more than one student has created the same number of tasks, and the number is less than the maximum number of tasks created, then we exclude these students from the result.

I also linked images for the corresponding tables

Hackers table :

hacker_id name
     5077 Rose
    21283 Angela
    62743 Frank
    88255 Patrick
    96196 Lisa

Task Table :

challenge_id hacker_id
       61654      5077
       58302     21283
       40587     88255
       29477      5077
        1220     21283
       69514     21283
       46561     62743
       58077     62743
       18483     88255
       76766     21283
       52382      5077
       74467     21283
       33625     96196
       26053     88255
       42665     62743
       12859     62743
       70094     21283
       34599     88255
       54680     88255
       61881      5077

So far I made it

SELECT c.hacker_id, h.name, COUNT(c.challenge_id) AS challenge_count
FROM Challenges c LEFT JOIN Hackers h on  c.hacker_id = h.hacker_id
GROUP by 1,c.hacker_id HAVING challenge_count >= 
MAX(challenge_count) ORDER BY challenge_count DESC ,c.hacker_id DESC;

But do not get the expected result. My way out

, , .

Answer 1

:

• hacker_id, ,
• .
• , hacker_id.
• , .
• , ,

1,2 3...

SELECT h.*
     , COUNT(c.challenge_id) challenge_count 
  FROM hackers h 
  JOIN challenges c 
    ON c.hacker_id = h.hacker_id 
 GROUP 
    BY h.hacker_id
 ORDER 
    BY challenge_count DESC, hacker_id;

, 4 5 :

SELECT DISTINCT a.*
           FROM 
              ( SELECT h.*
                     , COUNT(c.challenge_id) challenge_count 
                  FROM hackers h 
                  JOIN challenges c 
                    ON c.hacker_id = h.hacker_id 
                 GROUP 
                    BY h.hacker_id
              ) a
           LEFT
           JOIN
              ( SELECT h.*
                     , COUNT(c.challenge_id) challenge_count 
                  FROM hackers h 
                  JOIN challenges c 
                    ON c.hacker_id = h.hacker_id 
                 GROUP 
                    BY h.hacker_id
              ) b
             ON b.hacker_id <> a.hacker_id AND b.challenge_count = a.challenge_count
           LEFT
           JOIN
              ( SELECT h.*
                     , COUNT(c.challenge_id) challenge_count 
                  FROM hackers h 
                  JOIN challenges c 
                    ON c.hacker_id = h.hacker_id 
                 GROUP 
                    BY h.hacker_id
              ) c
             ON c.challenge_count > a.challenge_count
          WHERE b.hacker_id IS NULL  
             OR c.hacker_id IS NULL
          ORDER 
             BY challenge_count DESC, hacker_id;

Answer 2

SELECT t1.name,
       t1.hacker_id,
       COALESCE(t2.challengeCount, 0) AS challengeCount
FROM Hackers t1
LEFT JOIN
(
    SELECT hacker_id, COUNT(*) AS challengeCount
    FROM Challenges
    GROUP BY hacker_id
) t2
    ON t1.hacker_id = t2.hacker_id
WHERE COALESCE(t2.challengeCount, 0) IN
(
    SELECT t1.challengeCount
    FROM
    (
        SELECT t1.hacker_id,
               COALESCE(t2.challengeCount, 0) AS challengeCount
        FROM Hackers t1
        LEFT JOIN
        (
            SELECT hacker_id, COUNT(*) AS challengeCount
            FROM Challenges
            GROUP BY hacker_id
        ) t2
            ON t1.hacker_id = t2.hacker_id
    ) t1
    GROUP BY t1.challengeCount
    HAVING COUNT(*) = 1
) OR COALESCE(t2.challengeCount, 0) =
(
    SELECT MAX(t.challengeCount) FROM
    (
        SELECT COUNT(*) AS challengeCount
        FROM Challenges GROUP BY hacker_id
    ) t
)
ORDER BY COALESCE(t2.challengeCount, 0) DESC,
         t1.hacker_id