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. Advanced Join
  4. Interviews
  5. Discussions

Interviews

  • mohabdev25
     + 1 comment

    Guys my query runs but still hackerrank can't accept it as it's valid , can anyone tell me why ?

    with tvs as (
select challenge_id ,
        sum(total_views) "tv" ,
        sum(total_unique_views) "tuv"

from View_Stats 
group by challenge_id
) ,
tss as ( 
    select challenge_id,
        sum(total_submissions) "t_sub",
        sum(total_accepted_submissions)"t_acc_sub"
    from Submission_Stats 
    group by challenge_id
)


select 
    c.contest_id , 
    c.hacker_id , 
    c.name ,
    coalesce(sum(tvs.tv),0) "total_views",
    coalesce(sum(tvs.tuv),0) "total_unique_views", 
    coalesce(sum(tss.t_sub),0) "t_sub",
    coalesce(sum(tss.t_acc_sub),0) "t_acc_subs"

from Contests c
LEFT JOIN Colleges co on c.contest_id = co.contest_id
LEFT JOIN Challenges ch on co.college_id = ch.college_id
LEFT JOIN tvs  on tvs.challenge_id = ch.challenge_id
LEFT JOIN tss on tss.challenge_id = ch.challenge_id 

group by  c.contest_id ,c.hacker_id , c.name 

having coalesce(sum(tvs.tv),0)  + 
        coalesce(sum(tvs.tuv),0) +
        coalesce(sum(tss.t_sub),0) +
        coalesce(sum(tss.t_acc_sub),0) <> 0

    order by c.contest_id ;