• + 0 comments

    MS SQL Solution :

    with contest_stat as ( select c.contest_id , (total_submissions) as total_submissions ,(total_accepted_submissions) as total_accepted_submissions ,(total_views) as total_views ,(total_unique_views) as total_unique_views from Contests c left join Colleges cl on c.contest_id = cl.contest_id left join Challenges ch

    on cl.college_id = ch.college_id /* left join View_Stats vs on ch.challenge_id = vs.challenge_id left join Submission_Stats ss on ch.challenge_id = ss.challenge_id */ left join ( select challenge_id ,sum(total_views) as total_views ,sum(total_unique_views) as total_unique_views from View_Stats group by
    challenge_id ) vin on ch.challenge_id = vin.challenge_id left join ( select challenge_id ,sum(total_submissions) as total_submissions ,sum(total_accepted_submissions) as total_accepted_submissions from Submission_Stats group by challenge_id ) vin1 on ch.challenge_id = vin1.challenge_id ) select c.contest_id ,c.hacker_id ,c.name ,sum(total_submissions) ,sum(total_accepted_submissions) ,sum(total_views) ,sum(total_unique_views) from Contests c inner join contest_stat cs on c.contest_id = cs.contest_id group by c.contest_id ,c.hacker_id ,c.name having sum(total_submissions) + sum(total_accepted_submissions) + sum(total_views) + sum(total_unique_views) > 0 order by c.contest_id