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

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

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;
``````
Source (Stackoverflow)