John Lekberg

JUNTO Practice - "Top Competitors"

Discussed on 2019-10-22.

Problem is "Top Competitors".

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

Solution - Daniel

select i,n
from (select h.hacker_id i, 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,
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

Solution - John

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


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

Solution - Oscar

Hackers AS H
COUNT(S.challenge_id) AS CCount
Submissions as S 
Challenges as Ch
ON S.challenge_id = Ch.challenge_id
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