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

|

1437 Discussions

|

  • ivan_belov90
     + 2 comments

    I can not submit a submission because it times out.

  • mdnizamjaafar
     + 0 comments
    WITH AGGREGATE_VIEW AS (
    SELECT 
        D.contest_id,
        D.hacker_id,
        D.name,
        SUM(total_views) AS total_views,
        SUM(total_unique_views) AS total_unique_views
    FROM
        View_Stats A
    JOIN Challenges B ON A.challenge_id = B.challenge_id
    JOIN Colleges C ON B.college_id = C.college_id
    JOIN Contests D ON C.contest_id = D.contest_id
    GROUP BY
        D.contest_id,
        D.hacker_id,
        D.name
),

AGGREGATE_SUBMISSION AS (
    SELECT 
        D.contest_id,
        D.hacker_id,
        D.name,
        SUM(total_submissions) AS total_submissions,
        SUM(total_accepted_submissions) AS total_accepted_submissions
    FROM
        Submission_Stats A
    JOIN Challenges B ON A.challenge_id = B.challenge_id
    JOIN Colleges C ON B.college_id = C.college_id
    JOIN Contests D ON C.contest_id = D.contest_id
    GROUP BY
        D.contest_id,
        D.hacker_id,
        D.name
)

SELECT
    A.contest_id,
    A.hacker_id,
    A.name,
    A.total_submissions,
    A.total_accepted_submissions,
    B.total_views,
    B.total_unique_views
FROM AGGREGATE_SUBMISSION A
JOIN AGGREGATE_VIEW B
ON A.contest_id = B.contest_id AND A.hacker_id = B.hacker_id AND A.name = B.name
WHERE total_submissions+total_accepted_submissions+total_views+total_unique_views > 0
ORDER BY contest_id   
`

    `

  • olorunsogoabiod1
     + 0 comments

    with submissions as (select c.contest_id, c.hacker_id, sum(s.total_submissions) as total_submissions, sum(s.total_accepted_submissions) as total_accepted_submissions from contests c join colleges cg on c.contest_id = cg.contest_id join challenges chg on cg.college_id = chg.college_id join submission_stats s on chg.challenge_id = s.challenge_id group by c.contest_id, c.hacker_id ), views as (select c.contest_id, c.hacker_id, sum(v.total_views) as total_views, sum(v.total_unique_views) as total_unique_views from contests c join colleges cg on c.contest_id = cg.contest_id join challenges chg on cg.college_id = chg.college_id join view_stats v on chg.challenge_id = v.challenge_id group by c.contest_id, c.hacker_id ) select c.contest_id, c.hacker_id, c.name, s.total_submissions, s.total_accepted_submissions, v.total_views, v.total_unique_views from contests c join submissions s on c.contest_id = s.contest_id join views v on c.contest_id = v.contest_id group by c.contest_id, c.hacker_id, c.name, s.total_submissions, s.total_accepted_submissions, v.total_views, v.total_unique_views having (s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) > 0

  • jarun_vtn
     + 0 comments
    select con.contest_id, con.hacker_id, con.name,
    sum(s.total_submissions), sum(s.total_accepted_submissions),
    sum(v.total_views), sum(v.total_unique_views)
from contests as con
left join colleges   as col on con.contest_id = col.contest_id
left join challenges as cha on col.college_id = cha.college_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 s on cha.challenge_id = s.challenge_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 v on cha.challenge_id = v.challenge_id

group by con.contest_id, con.hacker_id, con.name
having sum(v.total_views) + sum(v.total_unique_views) + sum(s.total_submissions) + sum(s.total_accepted_submissions) > 0
order by con.contest_id
;

  • 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;