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.
WITH CTE1 AS (
SELECT H.hacker_id, H.name, COUNT(C.Challenge_Id) AS NoofChallenges
FROM Hackers H
INNER JOIN Challenges C ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
HAVING COUNT(C.Challenge_Id) > 1
),
CTE2 AS (
SELECT S.hacker_id, S.challenge_id, C.difficulty_level
FROM Submissions S
INNER JOIN Challenges C ON S.hacker_id = C.hacker_id AND S.challenge_id = C.challenge_id
INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
WHERE D.score = S.score
)
SELECT DISTINCT H.hacker_id, H.name
FROM CTE1 H
INNER JOIN CTE2 C ON H.hacker_id = C.hacker_id
ORDER BY 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 →
WITH CTE1 AS ( SELECT H.hacker_id, H.name, COUNT(C.Challenge_Id) AS NoofChallenges FROM Hackers H INNER JOIN Challenges C ON H.hacker_id = C.hacker_id GROUP BY H.hacker_id, H.name HAVING COUNT(C.Challenge_Id) > 1 ), CTE2 AS ( SELECT S.hacker_id, S.challenge_id, C.difficulty_level FROM Submissions S INNER JOIN Challenges C ON S.hacker_id = C.hacker_id AND S.challenge_id = C.challenge_id INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level WHERE D.score = S.score ) SELECT DISTINCT H.hacker_id, H.name FROM CTE1 H INNER JOIN CTE2 C ON H.hacker_id = C.hacker_id ORDER BY H.hacker_id ASC;