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. Contest Leaderboard
  5. Discussions

Contest Leaderboard

Sort by

recency

|

2271 Discussions

|

  • mayunuo0
     + 0 comments

    select hacker_id,name,sum(total) from (select h.hacker_id,name,challenge_id,max(score) as total from Hackers h left join submissions s on s.hacker_id = h.hacker_id group by h.hacker_id,name,challenge_id ) as N group by hacker_id,name having sum(total) > 0 order by sum(total) desc,hacker_id asc

  • hanzalahali1062
     + 0 comments
    with cte as(
select hackers.hacker_id as id,hackers.name as name,submissions.challenge_id as ch_id ,submissions.score as score,
row_number() over (partition by hackers.hacker_id,submissions.challenge_id order by submissions.score desc) as rn from hackers
inner join submissions
on hackers.hacker_id=submissions.hacker_id
)
select cte.id,cte.name,sum(cte.score) as sm from cte
where cte.rn=1
group by cte.name,cte.id
having sum(cte.score)>1
order by sm desc,cte.id asc;

  • vinathibharadwaj
     + 3 comments

    can anyone please explain why this code is giving error

    with cte1 as (
select h.hacker_id as hacker_id, 
    h.name as name, 
    s.score as score, 
    s.challenge_id as challenge_id,
    row_number() over (partition by hacker_id, challenge_id order by score) as rn
from hackers h 
join submissions s
on h.hacker_id = s.hacker_id
-- group by h.hacker_id, h.name
)

select hacker_id, name, sum(score) as total_score
from cte1
where rn=1
group by hacker_id, name
having sum(score) > 0 
order by total_score desc, hacker_id ASC

  • gldnganesh
     + 0 comments

    select hacker_id,name, sum(max_score) from ( select h.hacker_id,h.name,s.challenge_id,max(s.score) as max_score from hackers h join submissions s on h.hacker_id=s.hacker_id group by hacker_id,name,challenge_id
    ) max_table group by hacker_id,name having sum(max_score)!=0 order by sum(max_score) desc,hacker_id

  • surana_dipanshi1
     + 1 comment

    Can someone explain what am I doing wrong? Code:-

    select h.hacker_id,h.name,sum(s.score)
from hackers h join Submissions s on h.hacker_id=s.hacker_id 
where 
score in (select max(score) from Submissions group by hacker_id,challenge_id)
group by h.hacker_id,h.name,s.challenge_id
having sum(s.score)>0
order by sum(s.score) desc, h.hacker_id asc``