Here are the solution of Interviews Hackerrank Solutions you can find All HackerRank SQL Programming Solutions in Single Post HackerRank SQL Programming Solutions

select ct.contest_id,hacker_id,name,sum(total_submissions),sum(total_accepted_submissions),sum(total_views),sum(total_unique_views) from contests ct join colleges cl on ct.contest_id=cl.contest_id join challenges chl on cl.college_id=chl.college_id left join (select challenge_id,sum(total_views) as total_views, sum(total_unique_views) as total_unique_views from view_stats vw_st group by challenge_id ) as r on r.challenge_id=chl.challenge_id left join (select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions from submission_stats sub group by challenge_id) as s on s.challenge_id=chl.challenge_id group by ct.contest_id,hacker_id,name order by ct.contest_id,hacker_id,name

Oracle

SELECT Contests.contest_id,
    Contests.hacker_id,
    Contests.NAME,
    COALESCE(SUM(Submission_Stats.total_submissions), 0),
    COALESCE(SUM(Submission_Stats.total_accepted_submissions), 0),
    COALESCE(SUM(View_Stats.total_views), 0),
    COALESCE(SUM(View_Stats.total_unique_views), 0)
FROM Contests,
    Colleges,
    Challenges
    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
    ) View_Stats ON Challenges.challenge_id = View_Stats.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
    ) Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id
WHERE Contests.contest_id = Colleges.contest_id
    AND Colleges.college_id = Challenges.college_id
GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME
ORDER BY Contests.contest_id;

MySQL

SELECT Contests.contest_id,
    Contests.hacker_id,
    Contests.NAME,
    IFNULL(SUM(Submission_Stats.total_submissions), 0),
    IFNULL(SUM(Submission_Stats.total_accepted_submissions), 0),
    IFNULL(SUM(View_Stats.total_views), 0),
    IFNULL(SUM(View_Stats.total_unique_views), 0)
FROM Contests,
    Colleges,
    Challenges
    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
    ) AS View_Stats ON Challenges.challenge_id = View_Stats.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
    ) AS Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id
WHERE Contests.contest_id = Colleges.contest_id
    AND Colleges.college_id = Challenges.college_id
GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME
ORDER BY Contests.contest_id

;with cte as (select vs.challenge_id,vs.total_views, vs.total_unique_views,ss.total_submissions,ss.total_accepted_submissions from view_stats vs join submission_stats ss on vs.challenge_id = ss.challenge_id ), cte1 as (select cs.contest_id,cs.hacker_id,cs.name,cl.college_id,ch.challenge_id from colleges cl join challenges ch on cl.college_id = ch.college_id join Contests cs on cl.contest_id = cs.contest_id), cte2 as ( select cte1.contest_id,cte1.hacker_id,cte1.name,cte.total_submissions,cte.total_accepted_submissions,cte.total_views,cte.total_unique_views from cte1 join cte on cte1.challenge_id = cte.challenge_id ) select contest_id,hacker_id,name,SUM(total_submissions),SUM(total_accepted_submissions),SUM(total_views),SUM(total_unique_views) from cte2 group by contest_id,hacker_id,name having SUM(total_submissions) + SUM(total_accepted_submissions) + SUM(total_views) + SUM(total_unique_views)>0

order by contest_id

SELECT ct.contest_id, ct.hacker_id, ct.Name, SUM(ss.total_submissions) as sum_submissions, SUM(ss.total_accepted_submissions) as sum_accepted_submissions, SUM(vs.total_views) as sum_views, SUM(vs.total_unique_views) as sum_unique_views FROM
Contests ct JOIN Colleges clg ON ct.contest_id=clg.contest_id JOIN Challenges cs ON clg.college_id=cs.college_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)vs ON cs.challenge_id = vs.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)ss ON cs.challenge_id = ss.challenge_id GROUP BY ct.contest_id, ct.hacker_id, ct.Name HAVING sum_views >0 or sum_unique_views >0 or sum_submissions >0 or sum_accepted_submissions>0 ORDER BY ct.contest_id