Interviews hacker sql answer

 

Interviews

 

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

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 .

Solution in MSSQL

 

select

               contests.contest_id,

               contests.hacker_id,

               contests.name,

               isnull(sum(total_submissions), 0),

               isnull(sum(total_accepted_submissions),0),

               sum(vs.total_views),

               sum(vs.total_unique_views)

from

               contests

               inner join colleges

                               on contests.contest_id = colleges.contest_id

               inner join challenges

                               on colleges.college_id = challenges.college_id

               left outer 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

               left 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

               ) vs

                               on challenges.challenge_id = vs.challenge_id

group by

               contests.contest_id,

               contests.hacker_id,

               contests.name

order by

               contests.contest_

 

 

Solution in mysql

SELECT contest_id, hacker_id, name, SUM(c1), SUM(c2), SUM(c3), SUM(c4)

FROM

(

SELECT c.*,

IFNULL((SELECT SUM(vs.total_submissions) AS cnt FROM submission_stats vs

                                JOIN challenges cd ON cd.challenge_id = vs.challenge_id

    WHERE cd.challenge_id = ch.challenge_id

), 0) AS c1,

IFNULL((SELECT SUM(vs.total_accepted_submissions) AS cnt FROM submission_stats vs

                                JOIN challenges cd ON cd.challenge_id = vs.challenge_id

    WHERE cd.challenge_id = ch.challenge_id

), 0) AS c2,

IFNULL((SELECT SUM(vs.total_views) AS cnt FROM view_stats vs

                                JOIN challenges cd ON cd.challenge_id = vs.challenge_id

    WHERE cd.challenge_id = ch.challenge_id

), 0) AS c3,

    IFNULL((SELECT SUM(vs.total_unique_views) AS cnt FROM view_stats vs

                                JOIN challenges cd ON cd.challenge_id = vs.challenge_id

    WHERE cd.challenge_id = ch.challenge_id

), 0) AS c4

FROM contests c

JOIN colleges co ON co.contest_id = c.contest_id

JOIN challenges ch ON ch.college_id = co.college_id

HAVING c1 > 0 or c2 > 0 or c3 > 0 or c4 > 0

ORDER BY c.contest_id

) TBL

GROUP BY contest_id

Solution in oracle

 

/*

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 views (contest_id, total_views, total_unique_views) AS (

    SELECT contest_id, SUM(total_views), SUM(total_unique_views)

    FROM Contests

    JOIN Colleges USING(contest_id)

    JOIN Challenges USING(college_id)

    JOIN View_Stats USING(challenge_id)

    GROUP BY contest_id

), subs (contest_id, total_submissions, total_accepted_submissions) AS (

    SELECT contest_id, SUM(total_submissions), SUM(total_accepted_submissions)

    FROM Contests

    JOIN Colleges USING(contest_id)

    JOIN Challenges USING(college_id)

    JOIN Submission_Stats USING(challenge_id)

    GROUP BY contest_id

)

SELECT contest_id, hacker_id, name, total_submissions, total_accepted_submissions, total_views, total_unique_views

FROM Contests

LEFT JOIN views USING(contest_id)

LEFT JOIN subs USING(contest_id)

WHERE total_submissions + total_accepted_submissions + total_views + total_unique_views > 0

ORDER BY contest_id ASC;

 

Solution in db2

/*

    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, max(hacker_id), max(name), 

sum(value(ts,0)), sum(value(tas,0)), 

sum(tv), sum(tuv)

from contests,

(select contest_id ci, challenge_id chi from colleges o, challenges h where

o.college_id=h.college_id) t1

left join (select sum(total_views) tv, sum(total_unique_views) tuv, challenge_id from view_stats group by challenge_id) vs on t1.chi= vs.challenge_id left join     

(select sum(total_submissions) ts, sum(total_accepted_submissions) tas, challenge_id from submission_stats group by challenge_id) ss on t1.chi=ss.challenge_id

where

t1.ci=contests.contest_id 

group by contest_id

having (sum(ts)+ sum(tas)+

sum(tv)+ sum(tuv))>0

order by contest_id

;