Challenges

  • + 4 comments

    MSSQL solution with CTE

    with c1 as (
    	select hacker_id, count(*) as cc_ch
    	from Challenges as c
    	group by hacker_id
    ), c2 as (
    	select max(cc_ch) as max_cc_ch 
    	from c1
    ), c3 as (
    	select cc_ch
    	from c1
    	where cc_ch < (select top 1 max_cc_ch from c2)
    	group by cc_ch
    	having count(*) = 1
    	
    	union 
    	select max_cc_ch from c2
    )
    select h.hacker_id, h.name, c1.cc_ch
    from c1 
    join c3 on c1.cc_ch = c3.cc_ch
    join Hackers as h on c1.hacker_id = h.hacker_id 
    order by c1.cc_ch desc, hacker_id