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

|

1433 Discussions

|

  • saarthak_choudh1
     + 0 comments
    SELECT * FROM (
    SELECT 
        C.CONTEST_ID, 
        C.HACKER_ID, 
        C.NAME, 
        COALESCE(SUM(E.TS), 0) AS TS,
        COALESCE(SUM(E.TAS), 0) AS TAS,
        COALESCE(SUM(D.TOTAL_VIEWS), 0) AS TV,
        COALESCE(SUM(D.TUV), 0) AS TUV
        
    FROM CONTESTS AS C
    LEFT JOIN Colleges AS CO ON C.CONTEST_ID = CO.CONTEST_ID
    LEFT JOIN CHALLENGES AS CH ON CO.COLLEGE_ID = CH.COLLEGE_ID
    LEFT JOIN (
        SELECT 
            CHALLENGE_ID, 
            SUM(TOTAL_VIEWS) AS TOTAL_VIEWS, 
            SUM(TOTAL_UNIQUE_VIEWS) AS TUV
        FROM VIEW_STATS
        GROUP BY CHALLENGE_ID
    ) AS D ON CH.CHALLENGE_ID = D.CHALLENGE_ID
    LEFT JOIN (
        SELECT 
            CHALLENGE_ID, 
            SUM(TOTAL_SUBMISSIONS) AS TS, 
            SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TAS
        FROM Submission_Stats 
        GROUP BY CHALLENGE_ID
    ) AS E ON CH.CHALLENGE_ID = E.CHALLENGE_ID
    GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME
) AS CTE
WHERE TV + TS + TUV + TAS != 0
ORDER BY CONTEST_ID;

  • bachuvikas2
     + 0 comments

    MySQL Query:

    SELECT Contests.contest_id, hacker_id, name, SUM(COALESCE(total_submissions, 0)) AS total_submissions, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM Contests JOIN ( SELECT DISTINCT college_id, contest_id FROM Colleges ) AS colg ON Contests.contest_id = colg.contest_id JOIN ( SELECT DISTINCT college_id, challenge_id FROM Challenges ) AS chlng ON colg.college_id = chlng.college_id LEFT JOIN ( 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 ) AS vs ON chlng.challenge_id = vs.challenge_id LEFT JOIN ( 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 ) AS ss ON chlng.challenge_id = ss.challenge_id GROUP BY Contests.contest_id, hacker_id, name HAVING SUM(COALESCE(total_submissions, 0)) != 0 OR SUM(COALESCE(total_accepted_submissions, 0)) != 0 OR SUM(COALESCE(total_views, 0)) != 0 OR SUM(COALESCE(total_unique_views, 0)) != 0 ORDER BY Contests.contest_id;

  • priyank_inbox
     + 0 comments

    select Contests.contest_id, hacker_id, name , SUM(COALESCE(total_submissions,0)), SUM(COALESCE(total_accepted_submissions,0)) , SUM(COALESCE(total_views,0)), SUM(COALESCE(total_unique_views,0)) from Contests JOIN (Select distinct college_id, contest_id from Colleges) colg on Contests.contest_id = colg.contest_id JOIN (Select distinct college_id, challenge_id from Challenges) chlng on colg.college_id = chlng.college_id LEFT JOIN (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) vs on chlng.challenge_id = vs.challenge_id LEFT JOIN (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) ss on chlng.challenge_id = ss.challenge_id Group by Contests.contest_id, hacker_id, name having SUM(COALESCE(total_submissions,0)) != 0 or SUM(COALESCE(total_accepted_submissions,0)) != 0 or SUM(COALESCE(total_views,0)) != 0 or SUM(COALESCE(total_unique_views,0)) != 0 order by Contests.contest_id;

  • harinadhmadhava5
     + 0 comments

    with cte_stud as ( select b.contest_id,a.hacker_id,a.name,c.challenge_id ,c.college_id from contests a join colleges b on a.contest_id = b.contest_id join challenges c on b.college_id = c.college_id ),cte_view as ( select ct.challenge_id,sum(v.total_views) as tv,sum(v.total_unique_views) as tuv from view_stats v join cte_stud ct on v.challenge_id = ct.challenge_id group by ct.challenge_id ),cte_sub as ( select ct.challenge_id,sum(total_submissions) as ts ,sum(total_accepted_submissions) as tas from submission_stats s join cte_stud ct on s.challenge_id = ct.challenge_id group by ct.challenge_id) select a.contest_id,a.hacker_id,a.name,sum(b.ts),sum(b.tas),sum(c.tv),sum(c.tuv) from cte_stud a left join cte_sub b on a.challenge_id = b.challenge_id left join cte_view c on a.challenge_id = c.challenge_id group by a.contest_id,a.hacker_id,a.name having (sum(b.ts)+sum(b.tas)+sum(c.tv)+sum(c.tuv)) > 0 order by a.contest_id

  • shashankdr06
     + 0 comments
    • SELECT con.contest_id, con.hacker_id, con.name, SUM(ss.total_submissions) AS total_submissions, SUM(ss.total_accepted_submissions) AS total_accepted_submissions, SUM(vs.total_views) AS total_views, SUM(vs.total_unique_views) AS total_unique_views FROM Contests AS con JOIN Colleges AS col ON con.contest_id = col.contest_id JOIN Challenges AS ch ON col.college_id = ch.college_id LEFT JOIN View_Stats AS vs ON ch.challenge_id = vs.challenge_id LEFT JOIN Submission_Stats AS ss ON ch.challenge_id = ss.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING SUM(ss.total_submissions) > 0 OR SUM(ss.total_accepted_submissions) > 0 OR SUM(vs.total_views) > 0 OR SUM(vs.total_unique_views) > 0 ORDER BY con.contest_id;