We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
New Oracle SQL , It's easy to understandable.
WITH s1 AS (
SELECT c.challenge_id, c.difficulty_level, d.score
FROM challenges c
JOIN difficulty d ON c.difficulty_level = d.difficulty_level
),
s2 AS (
SELECT s.hacker_id, s.challenge_id, s.score, c.difficulty_level
FROM submissions s
JOIN challenges c ON s.challenge_id = c.challenge_id
),
s3 AS (
SELECT h.hacker_id, h.name, s.score
FROM hackers h
JOIN submissions s ON s.hacker_id = h.hacker_id
),
s4 AS (
SELECT s1.score, s2.hacker_id, s1.challenge_id, s1.difficulty_level
FROM s1
JOIN s2 ON s1.challenge_id = s2.challenge_id
WHERE s1.score = s2.score
),
s5 AS (
SELECT s4.hacker_id, COUNT(DISTINCT s4.challenge_id) AS total_challenges
FROM s4
GROUP BY s4.hacker_id
HAVING COUNT(DISTINCT s4.challenge_id) > 1
)
SELECT h.hacker_id, h.name
FROM hackers h
JOIN s5 ON h.hacker_id = s5.hacker_id
ORDER BY s5.total_challenges DESC, h.hacker_id ASC;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Top Competitors
You are viewing a single comment's thread. Return to all comments →
New Oracle SQL , It's easy to understandable. WITH s1 AS ( SELECT c.challenge_id, c.difficulty_level, d.score FROM challenges c JOIN difficulty d ON c.difficulty_level = d.difficulty_level ), s2 AS ( SELECT s.hacker_id, s.challenge_id, s.score, c.difficulty_level FROM submissions s JOIN challenges c ON s.challenge_id = c.challenge_id ), s3 AS ( SELECT h.hacker_id, h.name, s.score FROM hackers h JOIN submissions s ON s.hacker_id = h.hacker_id ), s4 AS ( SELECT s1.score, s2.hacker_id, s1.challenge_id, s1.difficulty_level FROM s1 JOIN s2 ON s1.challenge_id = s2.challenge_id WHERE s1.score = s2.score ), s5 AS ( SELECT s4.hacker_id, COUNT(DISTINCT s4.challenge_id) AS total_challenges FROM s4 GROUP BY s4.hacker_id HAVING COUNT(DISTINCT s4.challenge_id) > 1 ) SELECT h.hacker_id, h.name FROM hackers h JOIN s5 ON h.hacker_id = s5.hacker_id ORDER BY s5.total_challenges DESC, h.hacker_id ASC;