Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
550 B
1
Indexable
Never
SELECT
hackers.hacker_id,
hackers.name
FROM hackers
LEFT JOIN (
SELECT
submissions.hacker_id AS hacker_id,
COUNT(DISTINCT submissions.submission_id) AS fullscore
FROM submissions
LEFT JOIN challenges ON challenges.challenge_id = submissions.challenge_id
LEFT JOIN difficulty ON difficulty.difficulty_level = challenges.difficulty_level
WHERE submissions.score = difficulty.score
GROUP BY submissions.hacker_id
) hacker ON hacker.hacker_id = hackers.hacker_id
WHERE hacker.fullscore > 1
ORDER BY hacker.fullscore DESC, hackers.hacker_id