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.
- Prepare
- SQL
- Basic Join
- Top Competitors
- Discussions
Top Competitors
Top Competitors
Sort by
recency
|
2170 Discussions
|
Please Login in order to post a comment
MySQL
SELECT CONCAT(hacker_id," ", name) FROM ( SELECT s.hacker_id, h.name, COUNT(*) AS count_full_Scores
FROM Submissions as s INNER JOIN Challenges as c ON s.challenge_id = c.challenge_id INNER JOIN Difficulty as d ON c.difficulty_level = d.difficulty_level INNER JOIN Hackers as h ON s.hacker_id = h.hacker_id
WHERE s.score = d.score
GROUP BY s.hacker_id, h.name) AS new_table WHERE count_full_Scores > 1 ORDER BY count_full_Scores DESC, hacker_id ASC
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;
I don't think 'Hacker_ID' is needed in the Challenges table. It is causing unnecessary confustion.