Akash KR Akash KR - 3 months ago 22
MySQL Question

Exclude results on basis of test cases

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.

I have also attached the images for the respective tables

Table Hackers:

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


Table Challenges:

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 have done this

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 not getting expected output. My output

enter image description here

I need to exclude duplicate results from output such as hackers with same number of challenges should be excluded.

Answer

There are several criteria here:

  1. hacker_id, name, and the total number of challenges created by each student
  2. sort your results by the total number of challenges in descending order.
  3. If more than one student created the same number of challenges, then sort the result by hacker_id.
  4. If more than one student created the same number of challenges then exclude those students from the result.
  5. Except if the count equals the maximum number of challenges created,

The following deals with items 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;

We can join this query to itself once, to resolve criteria 4, and again to resolve item 5, as follows:

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;
Comments