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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

Sort by

recency

|

1426 Discussions

|

  • eddy_rene_c
     + 0 comments

    this is my version: 

    with cte as 
(
    SELECT a.contest_id, a.hacker_id, a.name,
        sum(e.total_submissions) as s1, 
        sum(e.total_accepted_submissions) as s2, 
        sum(d.total_views) as s3,
        sum(d.total_unique_views) as s4
    FROM 
    Contests a
    left join  Colleges b
    on a.contest_id = b.contest_id
    left join Challenges 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
    ) 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
    ) e 
    on c.challenge_id = e.challenge_id
    group by a.contest_id, a.hacker_id, a.name
    
)
select * from cte 
where s1+s2+s3+s4!=0
order by contest_id

  • agung_miantoro
     + 0 comments

    SET NOCOUNT ON;

    WITH SUM_TOTAL_SUBMISSIONS AS ( select a.contest_id, a.hacker_id, a.name, sum(e.total_submissions) AS total_submissions, sum(e.total_accepted_submissions) AS total_accepted_submissions From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN SUBMISSION_STATS E ON c.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,SUM_TOTAL_VIEW as( select a.contest_id, a.hacker_id, sum(d.total_views) AS total_views, sum(d.total_unique_views) AS total_unique_views From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN VIEW_STATS D ON C.CHALLENGE_ID = D.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,DATA_FINAL AS( SELECT A.*, B.total_views, B.total_unique_views FROM SUM_TOTAL_SUBMISSIONS A INNER JOIN SUM_TOTAL_VIEW B ON A.contest_id = B.contest_id AND A.hacker_id = B.hacker_id )

    SELECt * FROM DATA_FINAL WHERE total_submissions > 0 OR total_unique_views > 0 OR total_accepted_submissions > 0 OR total_views > 0 ORDER BY contest_id

    go

    go

  • maheshyadavbattu
     + 0 comments

    SELECT c.contest_id, c.hacker_id, c.name, IFNULL(s.ts, 0) AS total_submissions, IFNULL(s.tas, 0) AS total_accepted_submissions, IFNULL(v.tv, 0) AS total_views, IFNULL(v.tuv, 0) AS total_unique_views FROM Contests c LEFT JOIN ( SELECT c.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN Submission_Stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id ) s ON s.contest_id = c.contest_id LEFT JOIN ( SELECT c.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN View_Stats vs ON vs.challenge_id = ch.challenge_id GROUP BY c.contest_id ) v ON v.contest_id = c.contest_id WHERE (IFNULL(s.ts, 0) + IFNULL(s.tas, 0) + IFNULL(v.tv, 0) + IFNULL(v.tuv, 0)) > 0 ORDER BY c.contest_id;

  • hzerryn
     + 0 comments
    WITH sub AS (
    SELECT 
        challenge_id,
        SUM(COALESCE(total_submissions, 0)) AS total_submissions,
        SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions
    FROM submission_stats
    GROUP BY challenge_id
),
views AS (
    SELECT 
        challenge_id,
        SUM(COALESCE(total_views, 0)) AS total_views,
        SUM(COALESCE(total_unique_views, 0)) AS total_unique_views
    FROM view_stats
    GROUP BY challenge_id
),
tests AS ( 
    SELECT 
        c.contest_id, 
        c.hacker_id, 
        c.name,
        SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions,
        SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions,
        SUM(COALESCE(vs.total_views, 0)) AS total_views,
        SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views
    FROM contests c 
    INNER JOIN colleges clg ON clg.contest_id = c.contest_id 
    INNER JOIN challenges ch ON ch.college_id = clg.college_id 
    LEFT JOIN sub ss ON ss.challenge_id = ch.challenge_id 
    LEFT JOIN views vs ON vs.challenge_id = ch.challenge_id 
    GROUP BY c.contest_id, c.hacker_id, c.name
)
SELECT * FROM tests
WHERE NOT (
    total_submissions = 0 
    AND total_accepted_submissions = 0 
    AND total_views = 0 
    AND total_unique_views = 0) 
ORDER BY contest_id ASC;

  • mohitverma81
     + 0 comments

    Why the expected output of the Problem is not provided? I am still not able to understand what is wrong in the below query??

    SELECT cs.contest_id ,cs.hacker_id ,cs.name ,sum(nvl(ss.total_submissions,0)) as total_submissions ,sum(nvl(ss.total_accepted_submissions,0)) as total_accepted_submissions ,sum(nvl(vs.total_views,0)) as total_views ,sum(nvl(vs.total_unique_views,0)) as total_unique_views FROM Contests cs LEFT OUTER JOIN Colleges co on cs.contest_id = co.contest_id LEFT OUTER JOIN Challenges ch on co.college_id = ch.college_id LEFT OUTER JOIN Submission_Stats ss on ch.challenge_id = ss.challenge_id LEFT OUTER JOIN View_Stats vs on ch.challenge_id = vs.challenge_id group by cs.contest_id, cs.hacker_id, cs.name having (sum(nvl(ss.total_submissions,0)) + sum(nvl(ss.total_accepted_submissions,0)) + sum(nvl(vs.total_views,0)) + sum(nvl(vs.total_unique_views,0)) ) > 0 order by cs.contest_id;