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
|
2473 Discussions
|
Please Login in order to post a comment
what am I doing wrong I am getting No response on stdout
SELECT h.hacker_id,h.name FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id JOIN difficulty d ON c.difficulty_level = d.difficulty_level JOIN Submissions s ON c.challenge_id = s.challenge_id WHERE s.score = d.score GROUP BY h.hacker_id,h.name HAVING COUNT(DISTINCT s.challenge_id) > 1 ORDER BY COUNT(DISTINCT s.challenge_id) DESC, h.hacker_id ASC
By far, this is the most attention-seeking query I faced in this platform!
SELECT h.hacker_id, h.name FROM hackers AS h JOIN submissions AS s ON h.hacker_id = s.hacker_id JOIN challenges AS c ON c.challenge_id = s.challenge_id JOIN difficulty AS d ON d.difficulty_level = c.difficulty_level WHERE d.score = s.score GROUP BY h.hacker_id, h.name HAVING COUNT(DISTINCT s.challenge_id) > 1 ORDER BY COUNT(DISTINCT s.challenge_id) DESC, h.hacker_id ASC
SELECT tt.hacker_id, tt.name FROM ( SELECT t.hacker_id, t.name, COUNT(hacker_id) AS times_max FROM ( SELECT h.name AS name, c.challenge_id AS challenge_id, c.difficulty_level AS difficulty_level, d.score AS max_score, s.hacker_id AS hacker_id, s.score AS user_score FROM challenges AS c JOIN difficulty AS d ON d.difficulty_level = c.difficulty_level JOIN submissions AS s ON c.challenge_id = s.challenge_id JOIN hackers AS h ON h.hacker_id = s.hacker_id WHERE s.score = d.score ) AS t GROUP BY hacker_id, name HAVING times_max > 1 ORDER BY times_max DESC, hacker_id ASC )AS tt;
this problem is tricky and waste of time, make sure, you know that you need hacker_id to find submission_id first to get score, then use challenge id to get the full score with difficulty level; not the other way around..
SELECT h.hacker_id, name FROM hackers AS h join submissions AS s ON h.hacker_id = s.hacker_id join challenges AS c ON c.challenge_id = s.challenge_id join difficulty AS d ON d.difficulty_level = c.difficulty_level WHERE d.score = s.score GROUP BY h.hacker_id, h.name HAVING COUNT(DISTINCT s.challenge_id) > 1 ORDER BY COUNT(DISTINCT s.challenge_id) DESC, h.hacker_id asc