Hackerrank SQL - Advanced joins

 Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds  screening contest.

---

Input Format

The following tables hold interview data:

• Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.

• Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.

• Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.

• View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.

• Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.

---

Sample Input

Contests Table:Colleges Table:Challenges Table:View_Stats Table:Submission_Stats Table:

Sample Output

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

Explanation

The contest  is used in the college . In this college , challenges  and  are asked, so from the view and submission stats:

• Sum of total submissions 

• Sum of total accepted submissions 

• Sum of total views 

• Sum of total unique views 

Similarly, we can find the sums for contests  and .

Soultion in MSSQL

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/

WITH S_ANSWER AS 
(SELECT CONTESTS.CONTEST_ID, HACKER_ID, NAME,
    SUM(TOTAL_SUBMISSIONS) AS S1, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS S2
FROM CONTESTS
    INNER JOIN COLLEGES ON CONTESTS.CONTEST_ID = COLLEGES.CONTEST_ID
    INNER JOIN CHALLENGES ON CHALLENGES.COLLEGE_ID = COLLEGES.COLLEGE_ID
    INNER JOIN SUBMISSION_STATS
        ON SUBMISSION_STATS.CHALLENGE_ID = CHALLENGES.CHALLENGE_ID
    GROUP BY CONTESTS.CONTEST_ID, HACKER_ID, NAME), 
V_ANSWER AS
(SELECT CONTESTS.CONTEST_ID, HACKER_ID, NAME,
    SUM(TOTAL_VIEWS) AS V1, SUM(TOTAL_UNIQUE_VIEWS) AS V2
FROM CONTESTS
    INNER JOIN COLLEGES ON CONTESTS.CONTEST_ID = COLLEGES.CONTEST_ID
    INNER JOIN CHALLENGES ON CHALLENGES.COLLEGE_ID = COLLEGES.COLLEGE_ID
    INNER JOIN VIEW_STATS
        ON VIEW_STATS.CHALLENGE_ID = CHALLENGES.CHALLENGE_ID
    GROUP BY CONTESTS.CONTEST_ID, HACKER_ID, NAME)
SELECT S_ANSWER.CONTEST_ID, S_ANSWER.HACKER_ID, S_ANSWER.NAME,
    S_ANSWER.S1, S_ANSWER.S2, V_ANSWER.V1, V_ANSWER.V2
FROM
    S_ANSWER INNER JOIN V_ANSWER ON S_ANSWER.CONTEST_ID = V_ANSWER.CONTEST_ID
    ORDER BY S_ANSWER.CONTEST_ID

Solution in Mysql

/*
Enter your query here.
*/

select
contest_id, hacker_id, name,
a,b,c,d
from

(select 
contest_id, hacker_id, name,
sum(total_submissions) a,
sum(total_accepted_submissions) b
from
Contests
left join
Colleges using(contest_id)
left join
Challenges using(college_id)
left join
Submission_Stats using(challenge_id)
group by contest_id, hacker_id, name) A
left join

(
select 
contest_id, hacker_id, name,
sum(total_views ) c,
sum(total_unique_views ) d
from
Contests
left join
Colleges using(contest_id)
left join
Challenges using(college_id)
left join
View_Stats using(challenge_id)
group by
contest_id, hacker_id, name) B
using(contest_id, hacker_id, name)

where
a or b or c or d;

Solution in oracle:-

SELECT contest_id, hacker_id, name,
SUM(total_submissions) total_submissions,SUM(total_accepted_submissions) total_accepted_submissions,
SUM(TOTAL_VIEWS) TOTAL_VIEWS,SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
FROM(
SELECT A.contest_id, A.hacker_id, A.name,
0 total_submissions,0 total_accepted_submissions,
SUM(D.TOTAL_VIEWS) TOTAL_VIEWS, SUM(D.TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
FROM CONTESTS A, Colleges B,Challenges C,View_Stats D
WHERE A.contest_id=B.contest_id
AND B.COLLEGE_ID=C.COLLEGE_ID
AND C.CHALLENGE_ID=D.CHALLENGE_ID
GROUP BY A.contest_id, A.hacker_id, A.name
UNION
SELECT A.contest_id, A.hacker_id, A.name,
SUM(total_submissions) total_submissions,SUM(total_accepted_submissions) total_accepted_submissions,
0 TOTAL_VIEWS,0 TOTAL_UNIQUE_VIEWS
FROM CONTESTS A, Colleges B,Challenges C,Submission_Stats  D
WHERE A.contest_id=B.contest_id
AND B.COLLEGE_ID=C.COLLEGE_ID
AND C.CHALLENGE_ID=D.CHALLENGE_ID
GROUP BY A.contest_id, A.hacker_id, A.name)
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 1;

Solution in DB2 :-

SELECT A.contest_id
     , A.hacker_id
     , A.name
     , SUM(D.TS)
     , SUM(D.TAS)
     , SUM(E.TV)
     , SUM(E.TUV)
FROM Contests A
INNER JOIN Colleges B
 ON A.contest_id = B.contest_id
INNER JOIN Challenges C
 ON B.college_id = C.college_id
LEFT OUTER JOIN (SELECT challenge_id
                      , SUM(total_submissions) AS "TS"
                      , SUM(total_accepted_submissions) AS "TAS"
                 FROM Submission_Stats
                 GROUP BY challenge_id) D
 ON C.challenge_id = D.challenge_id
LEFT OUTER JOIN (SELECT challenge_id
                      , SUM(total_views) AS "TV"
                      , SUM(total_unique_views) AS "TUV"
                 FROM View_Stats
                 GROUP BY challenge_id) E
 ON C.challenge_id = E.challenge_id
GROUP BY A.contest_id
       , A.hacker_id
       , A.name
ORDER BY A.contest_id;

or

/*
    Enter your query here and follow these instructions:
    1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
    2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"
    3. Type your code immediately after comment. Don't leave any blank line.
*/
select
    CONTEST_ID || ' ' ||
    HACKER_ID || ' ' ||
    NAME || ' ' ||
    TOTAL_SUBMISSIONS || ' ' ||
    TOTAL_ACCEPTED_SUBMISSIONS || ' ' ||
    TOTAL_VIEWS || ' ' ||
    TOTAL_UNIQUE_VIEWS
from
(
select
    CONT.CONTEST_ID
,   CONT.HACKER_ID
,   CONT.NAME
,   sum(TOTAL_SUBMISSIONS) as "TOTAL_SUBMISSIONS"
,   sum(TOTAL_ACCEPTED_SUBMISSIONS) as "TOTAL_ACCEPTED_SUBMISSIONS"
,   sum(TOTAL_VIEWS) as "TOTAL_VIEWS"
,   sum(TOTAL_UNIQUE_VIEWS) as "TOTAL_UNIQUE_VIEWS"
from CONTESTS "CONT"
join COLLEGES "COLL"
  on COLL.CONTEST_ID = CONT.CONTEST_ID
join CHALLENGES "CHALL"
  on CHALL.COLLEGE_ID = COLL.COLLEGE_ID
left join
(
select
    coalesce(VIEW.CHALLENGE_ID, SUBM.CHALLENGE_ID) as "CHALLENGE_ID"
,   sum(TOTAL_SUBMISSIONS) as "TOTAL_SUBMISSIONS"
,   sum(TOTAL_ACCEPTED_SUBMISSIONS) as "TOTAL_ACCEPTED_SUBMISSIONS"
,   sum(TOTAL_VIEWS) as "TOTAL_VIEWS"
,   sum(TOTAL_UNIQUE_VIEWS) as "TOTAL_UNIQUE_VIEWS"
from
(
select
    CHALLENGE_ID
,   sum(TOTAL_SUBMISSIONS) as "TOTAL_SUBMISSIONS"
,   sum(TOTAL_ACCEPTED_SUBMISSIONS) as "TOTAL_ACCEPTED_SUBMISSIONS"
from SUBMISSION_STATS
group by
    CHALLENGE_ID
) "SUBM"
full outer 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"
 on VIEW.CHALLENGE_ID = SUBM.CHALLENGE_ID
group by
    coalesce(VIEW.CHALLENGE_ID, SUBM.CHALLENGE_ID)
) "STATS"
 on STATS.CHALLENGE_ID = CHALL.CHALLENGE_ID
group by
    CONT.CONTEST_ID
,   CONT.HACKER_ID
,   CONT.NAME
)
where
    TOTAL_SUBMISSIONS > 0
 or TOTAL_ACCEPTED_SUBMISSIONS > 0
 or TOTAL_VIEWS > 0
 or TOTAL_UNIQUE_VIEWS > 0
order by CONTEST_ID
;