hackerrank scenario

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 .

Table Creation scripts:

CREATE TABLE CONTESTS ( CONTEST_ID NUMBER, HACKER_ID NUMBER, NAME VARCHAR(200) );

INSERT INTO CONTESTS VALUES (66406, 17973, 'ROSE');
INSERT INTO CONTESTS VALUES (66556, 79153, 'ANGELA');
INSERT INTO CONTESTS VALUES (94828, 80275, 'FRANK');

SELECT * FROM CONTESTS;

CREATE TABLE COLLEGES( COLLEGE_ID NUMBER, CONTEST_ID NUMBER );

INSERT INTO COLLEGES VALUES (11219, 66406);
INSERT INTO COLLEGES VALUES (32473, 66556);
INSERT INTO COLLEGES VALUES (56685, 94828);

SELECT * FROM COLLEGES;

CREATE TABLE CHALLENGES ( CHALLENGE_ID NUMBER, COLLEGE_ID NUMBER );

INSERT INTO CHALLENGES VALUES (18765, 11219);
INSERT INTO CHALLENGES VALUES (47127, 11219);
INSERT INTO CHALLENGES VALUES (60292, 32473);
INSERT INTO CHALLENGES VALUES (72974, 56685);

SELECT * FROM  CHALLENGES;

CREATE TABLE VIEW_STATS ( CHALLENGE_ID NUMBER, TOTAL_VIEWS NUMBER, TOTAL_UNIQUE_VIEWS NUMBER );

INSERT INTO VIEW_STATS VALUES (47127, 26, 19);
INSERT INTO VIEW_STATS VALUES (47127, 15, 14); 
INSERT INTO VIEW_STATS VALUES (18765, 43, 10);
INSERT INTO VIEW_STATS VALUES (18765, 72, 13); 
INSERT INTO VIEW_STATS VALUES (75516, 35, 17); 
INSERT INTO VIEW_STATS VALUES (60292, 11, 10);
INSERT INTO VIEW_STATS VALUES (72974, 41, 15); 
INSERT INTO VIEW_STATS VALUES (75516, 75, 11);

SELECT * FROM VIEW_STATS;

CREATE TABLE SUBMISSION_STATS ( CHALLENGE_ID NUMBER, TOTAL_SUBMISSIONS NUMBER, TOTAL_ACCEPTED_SUBMISSIONS NUMBER );

INSERT INTO SUBMISSION_STATS  VALUES (75516, 34, 12);
INSERT INTO SUBMISSION_STATS  VALUES (47127, 27, 10);
INSERT INTO SUBMISSION_STATS  VALUES (47127, 56, 18);
INSERT INTO SUBMISSION_STATS  VALUES (75516, 74, 12);
INSERT INTO SUBMISSION_STATS  VALUES (75516, 83, 8);
INSERT INTO SUBMISSION_STATS  VALUES (72974, 68, 24);
INSERT INTO SUBMISSION_STATS  VALUES (72974, 82, 14);
INSERT INTO SUBMISSION_STATS  VALUES (47127, 28, 11);

SELECT * FROM SUBMISSION_STATS;

Query :

WITH 
SUM_SUBS AS (
SELECT CTST.CONTEST_ID,
    CTST.HACKER_ID,
    CTST.NAME,
    NVL(SUM(SS.TOTAL_SUBMISSIONS),0) SUM_OF_TOTAL_SUBMISSIONS,
    NVL(SUM(SS.TOTAL_ACCEPTED_SUBMISSIONS),0) SUM_OF_TOTAL_ACCEPTED_SUBMISSIONS
FROM 
CONTESTS CTST,
COLLEGES COL,
CHALLENGES CH LEFT JOIN  SUBMISSION_STATS SS  ON CH.CHALLENGE_ID = SS.CHALLENGE_ID    
WHERE
CTST.CONTEST_ID = COL.CONTEST_ID
AND COL.COLLEGE_ID = CH.COLLEGE_ID
GROUP BY CTST.CONTEST_ID,CTST.HACKER_ID,CTST.NAME ),

SUM_VIEWS AS (
SELECT CTST.CONTEST_ID,
    NVL(SUM(VS.TOTAL_VIEWS),0) SUM_OF_TOTAL_VIEWS,
    NVL(SUM(VS.TOTAL_UNIQUE_VIEWS),0) SUM_OF_TOTAL_UNIQUE_VIEWS
FROM 
CONTESTS CTST,
COLLEGES COL,
CHALLENGES CH LEFT JOIN  VIEW_STATS VS ON CH.CHALLENGE_ID = VS.CHALLENGE_ID
WHERE
CTST.CONTEST_ID = COL.CONTEST_ID
AND COL.COLLEGE_ID = CH.COLLEGE_ID
GROUP BY CTST.CONTEST_ID,CTST.HACKER_ID,CTST.NAME)

SELECT SS.CONTEST_ID,
SS.HACKER_ID,
SS.NAME,
SS.SUM_OF_TOTAL_SUBMISSIONS ,
SS.SUM_OF_TOTAL_ACCEPTED_SUBMISSIONS ,
SV.SUM_OF_TOTAL_VIEWS ,
SV.SUM_OF_TOTAL_UNIQUE_VIEWS
FROM SUM_SUBS SS,
SUM_VIEWS SV
WHERE SS.CONTEST_ID = SV.CONTEST_ID
AND SS.SUM_OF_TOTAL_SUBMISSIONS + SS.SUM_OF_TOTAL_ACCEPTED_SUBMISSIONS + SV.SUM_OF_TOTAL_VIEWS + SV.SUM_OF_TOTAL_UNIQUE_VIEWS <> 0 
ORDER BY 1;