# JUNTO Practice: Top Competitors

Discussed on October 22, 2019.

Problem is "Top Competitors".

Hint: you cannot select a column if it is not in group by clause.

## Solutions

Click to see:

### Oscar Martinez

``````SELECT
H.hacker_id,
H.name
FROM
Hackers AS H
RIGHT JOIN
(
SELECT
S.hacker_id,
COUNT(S.challenge_id) AS CCount
FROM
Submissions as S
LEFT JOIN
Challenges as Ch
ON S.challenge_id = Ch.challenge_id
LEFT JOIN
Difficulty as D
ON Ch.difficulty_level = D.difficulty_level
WHERE S.score = D.score
GROUP BY S.hacker_id
) AS J
ON H.hacker_id = J.hacker_id
WHERE J.CCount > 1
ORDER BY J.CCount DESC, H.hacker_id ASC
``````

I think it would be more readable to use `COUNT(*)` instead of `COUNT(S.challenge_id)`. Why do you use `LEFT JOIN` and `RIGHT JOIN`?

-- John

When joining, I like having one of the tables be the "standard of truth". So I use `LEFT JOIN` or `RIGHT JOIN` to indicate which table is the "standard of truth".

-- Oscar

### John Lekberg

``````WITH
-- Full_Score(C, S)
-- Challenge `C` has a full score of `S`.
Full_Score(challenge_id, score) AS (
SELECT challenge_id, score
FROM Difficulty
NATURAL JOIN Challenges
),
-- Best_Submission(H, C, S)
-- Hacker `H`s best submission on challenge `C` had a score of `S`.
Best_Submission(hacker_id, challenge_id, score) AS (
SELECT hacker_id, challenge_id, MAX(score)
FROM Submissions
GROUP BY hacker_id, challenge_id
),
-- Aced_N_Challenges(H, N)
-- Hacker `H` got a full score on `N` challenges (`N` > 0).
Aced_N_Challenges(hacker_id, n_challenges) AS (
SELECT hacker_id, COUNT(*)
FROM Best_Submission
NATURAL JOIN Full_Score
GROUP BY hacker_id
)
SELECT hacker_id, name
FROM Hackers
NATURAL JOIN Aced_N_Challenges
WHERE n_challenges > 1
ORDER BY n_challenges DESC, hacker_id ASC
;
``````

Just a reminder to never use natural joins in production code. I used natural joins instead of inner joins for this problem because it produced terser, easier to read code, and because the problem is a one-off.

-- John

What is the `WITH` stuff?

-- Oscar

The `WITH` clauses are called "common table expressions". They can be used to name subqueries, which can make SQL code more readable.

-- John

How would this look without `NATURAL JOIN`?

-- Daniel

I would use `INNER JOIN` instead. For example, I would replace

``````Best_Submission NATURAL JOIN Full_Score
``````

with

``````Best_Submission INNER JOIN Full_Score
ON Best_Submission.challenge_id = Full_Score.challenge_id
AND Best_Submission.score = Full_Score.score
``````

### Daniel Bassett

``````select i,n
from (select h.hacker_id i,h.name n,count(h.hacker_id) c
from ((Submissions s join Challenges c on s.challenge_id=c.challenge_id) join Difficulty d on d.score=s.score and d.difficulty_level=c.difficulty_level)join Hackers h on h.hacker_id=s.hacker_id
group by h.hacker_id,h.name
)
where c>1
order by c desc,i asc;
``````

I think using `COUNT(*)` would be more readable than `COUNT(h.hacker_id)`. I think aliasing columns and tables to such short names makes this less readable. Aliasing both `COUNT(h.hacker_id)` and `Challenges` to `c` is especially confusing.

-- John

Don't alias the columns to single letter names.

-- Oscar