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

|

2126 Discussions

|

  • bhargavibaichwa1
     + 0 comments

    WITH TMP as (Select hacker_id,challenge_id,max(score) as total_score from Submissions group by hacker_id, challenge_id ) Select TMP.hacker_id, H.name, Sum(TMP.total_score) as sum_score from TMP join Hackers H on TMP.hacker_id = H.hacker_id where total_score!=0 group by TMP.hacker_id, H.name ORDER BY sum_score DESC , Tmp.hacker_id ASC

    I got the results with the above query. Can someone help optimize this query

  • bchirag2696
     + 0 comments

    Oracle: WITH cte_1 AS (select hacker_id, challenge_id, max(score) as total from submissions group by hacker_id, challenge_id) select h.hacker_id, h.name, sum(c.total) as total_score from hackers h join cte_1 c on h.hacker_id = c.hacker_id having sum(c.total) != 0 group by h.hacker_id, h.name order by total_score desc, h.hacker_id asc;

  • hoangnhat0910
     + 0 comments
    /*
MSSQL
*/
with cte as (
select s.hacker_id, h.name, s.challenge_id, s.score,
    row_number() over(partition by s.hacker_id, s.challenge_id order by s.score desc) as rn
    from submissions s join hackers h
    on s.hacker_id = h.hacker_id
)
select hacker_id, name,
    sum(score) as total_score
from cte
    where rn = 1
    group by hacker_id, name
    having sum(score) > 0
    order by total_score desc, hacker_id asc

  • captaine0luffy
     + 0 comments

    Hi guys this code is working fine but I am getting error in hacker rank `select t1.hacker_id, h.name, t1.sum from ( select t.hacker_id, sum(distinct (t.result)) as sum from ( select * ,max(score) over(partition by hacker_id,challenge_id) as result from submissions) t group by 1 having sum(distinct (t.result)) > 0 order by 2 desc, 1

    ) t1 left join Hackers h on h.hacker_id = t1.hacker_id `

  • yusufguvenbastu1
     + 0 comments

    SELECT h1.hacker_id, h2.name, h1.sumscore

    FROM (

    SELECT 

    hacker_id, 

    SUM(max_score) AS sumscore

FROM (

    SELECT 

        hacker_id, 

        challenge_id, 

        MAX(score) AS max_score

    FROM submissions

    GROUP BY hacker_id, challenge_id

    ) AS max_scores

    GROUP BY hacker_id

HAVING SUM(max_score) > 0

    ) AS h1 JOIN hackers AS h2 ON h1.hacker_id = h2.hacker_id order by h1.sumscore DESC, h1.hacker_id ASC;