SQL Server: Top competitor with at least one full scoring submission

Question

Here is the question context

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

My strategy is to join all tables as a big whole table and group by the data to meet the requirement.

However I get this error from my code:

Column 'Hackers.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SELECT H.hacker_id, H.name
FROM Hackers H
INNER JOIN CHALLENGES C ON H.hacker_id = C.hacker_id
INNER JOIN Submissions S ON S.Challenge_id = C.Challenge_id
INNER JOIN Difficulty D ON D.difficulty_level = C.difficulty_level      
WHERE S.score = D.score 
  AND C.difficulty_level = D.difficulty_level
        
-- Query can work before this line.
                
GROUP BY H.hacker_id 
HAVING COUNT(S.submission_id) > 1
ORDER BY COUNT(S.submission_id) DESC, H.hacker_id ASC

Answer 1

Group by both the hacker id and name

i made this query with another join road and worked fine

SELECT h.hacker_id , h.name
FROM submissions s
INNER JOIN hackers h on h.hacker_id = s.hacker_id
INNER JOIN challenges c on c.challenge_id = s.challenge_id
INNER JOIN difficulty d on d.difficulty_level = c.difficulty_level

WHERE s.score = d.score
AND c.difficulty_level = d.difficulty_level
        
                
GROUP BY h.hacker_id ,h.name
HAVING COUNT(s.submission_id) > 1
ORDER BY COUNT(s.submission_id) DESC, h.hacker_id ASC

Answer 2

this problem can be solved many ways , here i solved this also executed and it worked.

step 1 : first join submissions challenges and difficulty table.
step 2 : score should be maximum. therefore, score from difficulty and submissions should be equal. and group by challenge_id , hacker_id and name this a table t
step 3: now we need name ,hacker_id so we can use table t and hackers to join. also hacker should get full score more than 1 challenges so we used having clause for that condition.

select t.hacker_id, h1.name
from(
select s.hacker_id,s.challenge_id
 from submissions s join challenges c 
 on s.challenge_id = c.challenge_id
 join difficulty d 
 on c.difficulty_level = d.difficulty_level
 where d.score = s.score
 group by s.challenge_id, s.hacker_id
)t 
join hackers h1 on
t.hacker_id = h1.hacker_id
group by t.hacker_id , h1.name
having count(t.hacker_id) >1
order by count(t.hacker_id) desc, t.hacker_id  

Answer 3

First time answering a question on stack but this is what I did

SELECT T.hacker_id, T.name FROM 
(SELECT h.hacker_id,h.name, CASE 
WHEN d.score = s.score then 1
ELSE 0 END AS perfect_score 
FROM hackers h join submissions s on h.hacker_id = s.hacker_id 
join challenges c on s.challenge_id = c.challenge_id 
join difficulty d on c.difficulty_level = d.difficulty_level 
WHERE 
CASE WHEN d.score = s.score then 1 ELSE 0 END = 1) AS T 
GROUP BY T.name, T.hacker_id 
Having COUNT(*)>1 
ORDER BY COUNT(*) DESC, hacker_id ASC;

Answer 4

below one would also work

select h.hacker_id,h.name from hackers h
left join submissions s on s.hacker_id = h.hacker_id 
left join challenges c on c.challenge_id = s.challenge_id 
left join difficulty d on c.difficulty_level = d.difficulty_level 
where s.score = d.score 
group by h.hacker_id,h.name having count(s.challenge_id) > 1 
order by count(s.challenge_id) desc, h.hacker_id asc

Answer 5

select s.submission_id,h.hacker_id,h.name,c.difficulty_level,
row_number() over(partition by h.name,c.difficulty_level,s.score,d.score order by h.name,c.difficulty_level,s.score,d.score) as row
into #result
from submissions s
inner join hackers h on h.hacker_id = s.hacker_id
inner join challenges c on s.challenge_id = c.challenge_id --and h.hacker_id = c.hacker_id
inner join difficulty d on c.difficulty_level = d.difficulty_level
where s.score=d.score

select hacker_id,name, count(name) as success_count 
into #final
from #result
--where row = 1
group by hacker_id,name

select hacker_id,name--,success_count
from #final
where success_count > 1
order by success_count desc,hacker_id

Answer 6

You can try this both ways for mysql

First way

select t.hacker_id,t.name FROM 
    (
        select s.hacker_id, h.name,count(*) as total FROM  Submissions s
        inner join hackers h on h.hacker_id = s.hacker_id
        left join challenges c on c.challenge_id = s.challenge_id
        left join difficulty d on d.difficulty_level = c.difficulty_level
        where s.score = d.score
        group by s.hacker_id, h.name
    ) as t
WHERE t.total > 1 
ORDER BY t.total desc, t.hacker_id

Second Way:

SELECT h.hacker_id , h.name
FROM submissions s
INNER JOIN hackers h on h.hacker_id = s.hacker_id
INNER JOIN challenges c on c.challenge_id = s.challenge_id
INNER JOIN difficulty d on d.difficulty_level = c.difficulty_level
WHERE s.score = d.score             
GROUP BY h.hacker_id ,h.name
HAVING COUNT(s.submission_id) > 1
ORDER BY COUNT(s.submission_id) DESC, h.hacker_id

Answer 7

SELECT h.hacker_id, h.name FROM Submissions AS s JOIN Hackers AS h ON s.hacker_id = h.hacker_id JOIN Challenges AS c ON s.challenge_id = c.challenge_id JOIN Difficulty AS d ON c.difficulty_level = d.difficulty_level WHERE s.score = d.score GROUP BY h.hacker_id, h.name HAVING COUNT()>1 ORDER BY COUNT() DESC, h.hacker_id;