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


Solution - John

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

Solution - Oscar

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