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.
one more way... only two ctes
replaced c2 with the MAX window function in c1
;with cChngCount
as
(
select
H.hacker_id,H.name,count() chngcnt, MAX(count()) over () Maxy
from
hackers H join
Challenges C on C.hacker_id = H.hacker_id
group by
H.hacker_id,H.name
)
,cXludeChngs
as
(
select
chngcnt
from
cChngCount
where
chngcnt <> Maxy
group by
chngcnt
having
count(*) > 1
)
select
CC.hacker_id,CC.name,chngcnt
from
cChngCount CC
where
not exists (select XC.chngcnt from cXludeChngs XC where XC.chngcnt = CC.chngcnt)
order by
chngcnt desc, hacker_id
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Challenges
You are viewing a single comment's thread. Return to all comments →
one more way... only two ctes replaced c2 with the MAX window function in c1 ;with cChngCount as ( select
H.hacker_id,H.name,count() chngcnt, MAX(count()) over () Maxy from hackers H join Challenges C on C.hacker_id = H.hacker_id group by H.hacker_id,H.name ) ,cXludeChngs as ( select chngcnt from cChngCount where chngcnt <> Maxy group by chngcnt having count(*) > 1 ) select CC.hacker_id,CC.name,chngcnt from cChngCount CC where not exists (select XC.chngcnt from cXludeChngs XC where XC.chngcnt = CC.chngcnt) order by chngcnt desc, hacker_id