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

  • engahmedabdulla1
     + 0 comments

    Explanation: you cannot do it with one query, because submission_stat and view_stat tables are indepandent of each other, if you join them with one statement you will get wrong result because it will double the number of rows having contest_id so, you have to process them with different queries

    with cte1 as (
    select 
        con.contest_id,
        con.hacker_id,
        con.name,
        sum(total_submissions) as totSub,
        sum(total_accepted_submissions) as totAcSub
    from contests con
    join colleges col on col.contest_id = con.contest_id
    join challenges cha on cha.college_id = col.college_id
    join Submission_Stats sub on sub.challenge_id = cha.challenge_id 
    group by con.contest_id,con.hacker_id,con.name
),

cte2 as
(
    select 
        con.contest_id,
        sum(total_views) as totVie,
        sum(total_unique_views) as totUnVie
    from contests con
    join colleges col on col.contest_id = con.contest_id
    join challenges cha on cha.college_id = col.college_id
    join View_Stats vi on vi.challenge_id = cha.challenge_id
    group by con.contest_id
)

select 
    cte1.contest_id,
    cte1.hacker_id,
    cte1.name,
    totSub,
    totAcSub,
    totVie,
    totUnVie
from cte1
join cte2 on cte1.contest_id = cte2.contest_id
order by cte1.contest_id