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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Top Competitors
  5. Discussions

Top Competitors

Sort by

recency

|

2505 Discussions

|

  • vincentiskandar1
     + 0 comments

    /* Enter your query here. */ SELECT hacker_id, name FROM( SELECT a.hacker_id, a.name, COUNT(DISTINCT CASE WHEN b.score = d.score THEN b.challenge_id else NULL END) AS total_perfect_score FROM Submissions b LEFT JOIN Challenges c ON b.challenge_id = c.challenge_id LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level LEFT JOIN Hackers a ON b.hacker_id = a.hacker_id GROUP BY 1,2) base WHERE total_perfect_score >1 ORDER BY total_perfect_score DESC, hacker_id

  • floreaoana22
     + 0 comments

    SELECT h.hacker_id, h.name FROM Hackers h JOIN ( SELECT DISTINCT s.hacker_id, s.challenge_id FROM Submissions s JOIN Challenges c ON s.challenge_id = c.challenge_id JOIN Difficulty d ON c.difficulty_level = d.difficulty_level WHERE s.score = d.score ) AS full_scores ON h.hacker_id = full_scores.hacker_id GROUP BY h.hacker_id, h.name HAVING COUNT(full_scores.challenge_id) > 1 ORDER BY COUNT(full_scores.challenge_id) DESC, h.hacker_id;

  • didzis_dti
     + 0 comments

    Oracle option

    WITH scores AS (
SELECT 
    s.hacker_id, h.name,
    count(*) total
FROM submissions s
inner join hackers h on h.hacker_id = s.hacker_id
inner join challenges c on c.challenge_id = s.challenge_id
inner join difficulty d on d.difficulty_level = c.difficulty_level
where d.score = s.score
having count(*) > 1
group by s.hacker_id, h.name)
select hacker_id, name from scores
order by total desc, hacker_id asc
;

  • SVVBathini_w
     + 0 comments

    For MySQL Platform

    SELECT hackers.hacker_id, hackers.name FROM hackers
JOIN submissions ON submissions.hacker_id = hackers.hacker_id
JOIN challenges ON challenges.challenge_id = submissions.challenge_id
JOIN difficulty ON difficulty.difficulty_level = challenges.difficulty_level
WHERE difficulty.score = submissions.score
GROUP BY 1, 2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, hackers.hacker_id;

  • anthony_suryaja1
     + 0 comments
    select
    a.hacker_id
    , d.name
from submissions a
join challenges b
    on a.challenge_id = b.challenge_id
join difficulty c
    on b.difficulty_level = c.difficulty_level
    and a.score = c.score
left join hackers d
    on a.hacker_id = d.hacker_id
where a.score = c.score
group by 1,2
having count(distinct a.challenge_id) > 1
order by count(distinct a.challenge_id) desc,a.hacker_id