We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Loading...
  1. Practice
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

Sort 147 Discussions, By:

votes

  • DZ
    dongyuzhang + 0 comments

    The relationship: contest 1---* college 1---*challenges For each challenge, there could be no views(View_Stats) or submissions(Submission_Stats), therefore, we use left join with these two tables.

    select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.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 cha.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 cha.challenge_id = ss.challenge_id
    group by con.contest_id, con.hacker_id, con.name
        having sum(total_submissions)!=0 or 
                sum(total_accepted_submissions)!=0 or
                sum(total_views)!=0 or
                sum(total_unique_views)!=0
            order by contest_id;

  • DR
    dwightrobinson + 2 comments

    Using CTE to group the Stats and Submission tables first is crucial, after that, its just a matter of left joining the results.

    WITH SUM_View_Stats AS (
SELECT challenge_id
    , total_views = sum(total_views)
    , total_unique_views = sum(total_unique_views)
FROM View_Stats 
GROUP BY challenge_id
)
,SUM_Submission_Stats AS (
SELECT challenge_id
    , total_submissions = sum(total_submissions)
    , total_accepted_submissions = sum(total_accepted_submissions)
FROM Submission_Stats 
GROUP BY challenge_id
)

SELECT    con.contest_id
        , con.hacker_id
        , con.name
        , SUM(total_submissions)
        , sum(total_accepted_submissions)
        , sum(total_views)
        , sum(total_unique_views)
FROM Contests con
INNER JOIN Colleges col
    ON con.contest_id = col.contest_id
INNER JOIN Challenges cha
    ON cha.college_id = col.college_id
LEFT JOIN SUM_View_Stats vs
    ON vs.challenge_id = cha.challenge_id
LEFT JOIN SUM_Submission_Stats ss
    ON ss.challenge_id = cha.challenge_id
GROUP BY con.contest_id,con.hacker_id,con.name
HAVING (SUM(total_submissions)
        +sum(total_accepted_submissions)
        +sum(total_views)
        +sum(total_unique_views)) <> 0
ORDER BY con.contest_ID
    • IL
      ianlee313 + 1 comment

      Hi, May I know why we have to group the stats and submission?

      • F
        fluecke + 1 comment

        If you look at the table, some challenge_ids s occur more than once. By using group by challenge_id the group function (sum) is only applied to those rows with the same challenge_id, thus giving the total submissions etc for the challenge with id challenge_id

        • IL
          ianlee313 + 0 comments

          Got it! thanks!

    • karllchris + 2 comments

      Hi, may I know why it's using left join for view and submission? I use inner join and it didn't pass, when I change to left, then it passes. thanks.

      • AS
        agung_sriwongo + 0 comments
        [deleted]
      • S
        nickat + 0 comments

        Look at the sample input in the problem statement. In the table Challenges there is a challenge_id 18765. This ID is not present in the Submission_Stats table, but is present in the View_Stats table. So, if you do the inner join on Challenges and Submissions_Stats first, there will be no rows with challenge_id 18765 in the result set. And when you do the second inner join with Vew_Stats table, the rows with challenge_id 18765 in it will not be regarded.

        If you do the left join, the rows with challege_id 18765 from the Challenges table (the left table) will be preserved in the result set even if there are no rows with this id in the rgiht table.

  • nera_nesic + 1 comment

    To save you 5 minutes in recreating the sample dataset, here it is for MySQL:

    create table Contests ( contest_id INT, hacker_id INT, name VARCHAR(200) );

    insert into Contests (contest_id, hacker_id, name) values (66406, 17973, "Rose"), (66556, 79153, "Angela"), (94828, 80275, "Frank");

    create table Colleges( college_id INT, contest_id INT );

    insert into Colleges (college_id, contest_id) values (11219, 66406), (32473, 66556), (56685, 94828);

    create table Challenges ( challenge_id INT, college_id INT );

    insert into Challenges (challenge_id, college_id) values (18765, 11219), (47127, 11219), (60292, 32473), (72974, 56685);

    create table View_Stats ( challenge_id INT, total_views INT, total_unique_views INT );

    insert into View_Stats (challenge_id, total_views, total_unique_views) values (47127, 26, 19), (47127, 15, 14), (18765, 43, 10), (18765, 72, 13), (75516, 35, 17), (60292, 11, 10), (72974, 41, 15), (75516, 75, 11);

    create table Submission_Stats ( challenge_id INT, total_submissions INT, total_accepted_submissions INT );

    insert into Submission_Stats (challenge_id, total_submissions, total_accepted_submissions) values (75516, 34, 12), (47127, 27, 10), (47127, 56, 18), (75516, 74, 12), (75516, 83, 8), (72974, 68, 24), (72974, 82, 14), (47127, 28, 11);

    • humoyun_ahmad + 0 comments

      good job

  • _Sulo + 4 comments

    Hints:

    • Not necessary that all CHALLENGE_ID have a value in VIEW_STATS and/or SUBMISSION_STATS. (LEFT JOIN)

    • Treat each table SUMs individually (VIEW_STATS[total_views, total_unique_views], SUBMISSION_STATS[total_submissions, total_accepted_submissions])

    • ajit0308 + 0 comments

      How is it possible to have a CHALLENGE_ID in SUBMISSION_STATS but not in VIEW_STATS? Without viewing any challenge how can one submit a challenge?

    • jacksonlango + 0 comments

      A key that you aren't treating SUMs individually is that you are getting larger values than what is expected. If so it could be because you're counting data twice.

    • sunliang0617 + 0 comments

      This is helpful. It is important to sum in tables separately before joining them. Thanks!

    • VikashYadav + 0 comments

      Shouldn't OUTER JOIN be use to retain CHALLENGE_ID of both VIEW_STATS and SUBMISSION_STATS?

  • PS
    SnoopDizzle + 3 comments

    To save some trouble...

    http://sqlfiddle.com/#!9/10d6ce/34/0

    • _Sulo + 0 comments

      Thanks,

      The "view_stats" table first entry

      (47127, 28, 19) -> (47127, 26, 19) :p

    • JY
      jyang4 + 0 comments

      Thanks so much! This was great. Working on a smaller dataset definitely helped.

      In the View_Stats table, the first entry under the column total_views should be 26 instead of 28.

    • zenbaku + 0 comments

      Adding on this.

      Here is a working MySQL solution.

      http://sqlfiddle.com/#!9/0bbb1/415/0

  • castrolem + 0 comments

    This is more of an exercise about aggregations than joins

    You have to create multiple aggregations, SUM the different values grouped by contest, convert null values into zero (for the where clause) and be careful about duplication (the only part where I can see join is actually an important thing here)

    I believe this exercise should be in a different domain.

  • RJ
    rjhan + 4 comments
    SELECT A.contest_id, A.hacker_id, A.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 AS A
LEFT JOIN Colleges AS B
    ON A.contest_id = B.contest_id 
LEFT JOIN Challenges AS C
    ON B.college_id = C.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) AS D 
    ON C.challenge_id = D.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 E
    ON C.challenge_id = E.challenge_id 
GROUP BY A.contest_id, A.hacker_id, A.Name
HAVING (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 
ORDER BY A.contest_id
;
    • HH
      tucaoneo + 1 comment

      No. Use your code to work on the sample input and you will not print this line

      66556 79153 Angela 0 0 11 10
      • HH
        tucaoneo + 0 comments

        Add some COALESCE's and it will be fine.

    • S
      sowmithreddy + 0 comments
      [deleted]
    • zexianxu + 1 comment
      SELECT A.contest_id, A.hacker_id, A.Name, 
        ifnull(SUM(total_submissions),0) As total_submissions, 
        ifnull(SUM(total_accepted_submissions),0) AS total_accepted_submissions,
        ifnull(SUM(total_views),0) AS total_views,
        ifnull(SUM(total_unique_views),0) AS total_unique_views
FROM Contests AS A

LEFT JOIN Colleges AS B
    ON A.contest_id = B.contest_id 

LEFT JOIN Challenges AS C
    ON B.college_id = C.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) AS D 
    ON C.challenge_id = D.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 E
    ON C.challenge_id = E.challenge_id 
    
GROUP BY A.contest_id, A.hacker_id, A.Name
HAVING (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0

ORDER BY A.contest_id
;
      • sanjay_dev + 1 comment

        purpose of ifnull please?

        • RaviPushkar + 0 comments

          It works same as 'COALESCE()' which just replaces the 'NULL' value with the value specified.

          For eg. above 'ifnull(SUM(total_submissions),0)' could be written as 'COALESCE(SUM(total_submissions),0)' which means if no NULL is present in the row, give output as SUM(total_submissions) and if NULL is present, it will replace it as 0.

    • AG
      ganisettiarjun + 0 comments

      HAVING (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0

      having cluase is missing aggregation so aplly sum HAVING sum(total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0

  • SP
    lovelysiva124 + 2 comments

    My Solution in oracle:

    select t1.contest_id, c.hacker_id, c.name, t1.st_submissions1, t1.sta_submissions1, t1.st_views1, t1.stu_views1 from (select c.contest_id, sum(t.st_views) st_views1, sum(t.stu_views) stu_views1, sum(t.st_submissions) st_submissions1, sum(t.sta_submissions) sta_submissions1 from (select v.college_id, v.st_views, v.stu_views, s.st_submissions, s.sta_submissions from (select c.college_id, sum(v.total_views) st_views, sum(v.total_unique_views) stu_views from challenges c, view_stats v where c.challenge_id = v.challenge_id group by c.college_id) v, (select c.college_id, sum(s.total_submissions) st_submissions, sum(s.total_accepted_submissions) sta_submissions from challenges c, submission_stats s where c.challenge_id = s.challenge_id group by c.college_id) s where v.college_id=s.college_id and (v.st_views<>0 or v.stu_views<>0 or s.st_submissions<>0 or s.sta_submissions<>0)) t, colleges c where t.college_id=c.college_id group by c.contest_id) t1, contests c where t1.contest_id = c.contest_id order by t1.contest_id;

    • DK
      MaterOfThe + 0 comments

      It will pass the test, but with example from the output "66406 17973 Rose 111 39 156 56 66556 79153 Angela 0 0 11 10 94828 80275 Frank 150 38 41 15"-It will not work.

      Below is my code, but output is not passing the test. SELECT VS_C.CONTEST_ID, CON.HACKER_ID, CON.NAME,VS_C.TS,VS_C.TAS, VS_C.TV,VS_C.TUV FROM CONTESTS CON, (SELECT COL1.CONTEST_ID, V_S.COL_ID, V_S.TV, V_S.TUV, V_S.TS, V_S.TAS FROM COLLEGES COL1, (SELECT V.COL_ID,V.TV,V.TUV,S.TS,S.TAS FROM (SELECT CH.COLLEGE_ID COL_ID, SUM(NVL(VS.TOTAL_VIEWS,0)) TV, SUM(NVL(VS.TOTAL_UNIQUE_VIEWS,0)) TUV FROM VIEW_STATS VS RIGHT OUTER JOIN CHALLENGES CH ON CH.CHALLENGE_ID=VS.CHALLENGE_ID GROUP BY COLLEGE_ID ) V, (SELECT CH.COLLEGE_ID COL_ID, SUM(NVL(SS.TOTAL_SUBMISSIONS,0)) TS ,SUM(NVL(SS.TOTAL_ACCEPTED_SUBMISSIONS,0)) TAS FROM SUBMISSION_STATS SS RIGHT OUTER JOIN CHALLENGES CH ON SS.CHALLENGE_ID = CH.CHALLENGE_ID GROUP BY COLLEGE_ID ) S WHERE V.COL_ID=S.COL_ID AND (V.TV>0 OR V.TUV>0 OR S.TS>0 OR S.TAS>0)) V_S WHERE COL1.COLLEGE_ID=V_S.COL_ID )VS_C WHERE CON.CONTEST_ID=VS_C.CONTEST_ID;

    • DC
      danielgcwik + 0 comments

      thanks I tried "lovelysiva124"'s solution and it worked

  • rajeshksethi + 0 comments

    Oracle Version

    select *
  from (select con.*,
               (select sum(total_submissions)
                  from submission_stats s, challenges c, colleges col
                 where s.challenge_id = c.challenge_id
                   and c.college_id = col.college_id
                   and col.contest_id = con.contest_id) total_submissions,
               (select sum(total_accepted_submissions)
                  from submission_stats s, challenges c, colleges col
                 where s.challenge_id = c.challenge_id
                   and c.college_id = col.college_id
                   and col.contest_id = con.contest_id) total_accepted_submissions,
               (select sum(total_views)
                  from View_Stats v, challenges c, colleges col
                 where v.challenge_id = c.challenge_id
                   and c.college_id = col.college_id
                   and col.contest_id = con.contest_id) total_views,
               (select sum(total_unique_views)
                  from View_Stats v, challenges c, colleges col
                 where v.challenge_id = c.challenge_id
                   and c.college_id = col.college_id
                   and col.contest_id = con.contest_id) total_unique_views
          from contests con)
 where total_submissions <> 0
   and total_accepted_submissions <> 0
   and total_views <> 0
   and total_unique_views <> 0
 order by contest_id;

  • LY
    yeyushengfan + 1 comment

    My solution in mysql.

    select tmp.contest_id,tmp.hacker_id,tmp.name,tmp.ss,tmp.sas,tmp1.sv,tmp1.suv from (select con.contest_id,con.hacker_id,con.name,sum(ifnull(ss.total_submissions,0)) as ss,sum(ifnull(ss.total_accepted_submissions,0)) as sas from contests con left join colleges col on con.contest_id=col.contest_id left join challenges cha on col.college_id=cha.college_id left join submission_stats ss on cha.challenge_id=ss.challenge_id group by con.contest_id) tmp left join (select con.contest_id,con.hacker_id,con.name,sum(ifnull(vs.total_views,0)) as sv,sum(ifnull(vs.total_unique_views,0)) as suv from contests con left join colleges col on con.contest_id=col.contest_id left join challenges cha on col.college_id=cha.college_id left join view_stats vs on cha.challenge_id=vs.challenge_id group by con.contest_id) tmp1 on tmp.contest_id=tmp1.contest_id where ss!=0 or sas!=0 or sv!=0 or suv!=0 order by contest_id;
    • jaybyrrd + 1 comment

      Formatted so that it is more readable:

      SELECT CT.contest_id, 
       CT.hacker_id, 
       CT.name, 
       Sum(ts), 
       Sum(tas), 
       Sum(tv), 
       Sum(tuv) 
FROM   contests CT 
       INNER JOIN colleges AS CL 
               ON CL.contest_id = CT.contest_id 
       INNER JOIN challenges AS CH 
               ON CH.college_id = CL.college_id 
       LEFT JOIN (SELECT challenge_id, 
                         Sum(total_submissions)          AS TS, 
                         Sum(total_accepted_submissions) AS TAS 
                  FROM   submission_stats 
                  GROUP  BY challenge_id) S 
              ON S.challenge_id = CH.challenge_id 
       LEFT JOIN (SELECT challenge_id, 
                         Sum(total_views)        AS TV, 
                         Sum(total_unique_views) AS TUV 
                  FROM   view_stats 
                  GROUP  BY challenge_id) V 
              ON V.challenge_id = CH.challenge_id 
GROUP  BY CT.contest_id 
ORDER  BY CT.contest_id;
      • sanjay_dev + 1 comment

        no where clause to check if sum!=0 to exclue? or it otherwise but condition is missing

        • PI
          paddyi + 0 comments

          It should be having clause not where caluse

Load more conversations