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 873 Discussions, By:

  • 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

  • hongntfx19807
     + 0 comments
    1. ect1 => total_submissions & accepted_submissions
    2. ect2 => total_views & total_unique_views
    3. ect1 FULL JOIN ect2
    with ect1 as (SELECT Co.contest_id, Co.hacker_id, Co.name, 
    sum(ss.total_submissions) as 's1', 
    sum(ss.total_accepted_submissions) as 's2'
    FROM Contests co
    JOIN Colleges col ON Co.contest_id  = Col.contest_id 
    JOIN Challenges ch ON Col.college_id = Ch.college_id
    JOIN Submission_Stats ss ON Ch.challenge_id = Ss.challenge_id
    GROUP BY Co.contest_id, Co.hacker_id, Co.name),

ect2 as (SELECT Co.contest_id, Co.hacker_id, Co.name, 
    sum(vs.total_views) as 's3', 
    sum(vs.total_unique_views) as 's4'
    FROM Contests co
    JOIN Colleges col ON Co.contest_id  = Col.contest_id 
    JOIN Challenges ch ON Col.college_id = Ch.college_id
    JOIN View_Stats vs ON Ch.challenge_id = vs.challenge_id
    GROUP BY Co.contest_id, Co.hacker_id, Co.name)

SELECT ect1.contest_id, ect1.hacker_id, ect1.name, 
    ect1.s1, ect1.s2, ect2.s3, ect2.s4
FROM ect1 FULL JOIN ect2 ON ect1.contest_id = ect2.contest_id and ect1.hacker_id = ect2.hacker_id and ect1.name = ect2.name
WHERE  ect1.s1+ ect1.s2+ ect2.s3+ ect2.s4 <>0
ORDER BY ect1.contest_id

  • Zyad_Mohamed21
     + 1 comment

    anyone know what is wrong?

    SELECT c.contest_id, c.hacker_id, c.name, 
       SUM(s.total_submissions) AS total_submissions, 
       SUM(s.total_accepted_submissions) AS total_accepted_submissions, 
       SUM(v.total_views) AS total_views, 
       SUM(v.total_unique_views) AS total_unique_views 
FROM Contests c 
JOIN Colleges cl ON c.contest_id = cl.contest_id 
JOIN Challenges ch ON ch.college_id = cl.college_id 
JOIN View_Stats v ON v.challenge_id = ch.challenge_id 
JOIN Submission_Stats s ON s.challenge_id = ch.challenge_id 
GROUP BY c.contest_id, c.hacker_id, c.name 
HAVING SUM(s.total_submissions) + SUM(s.total_accepted_submissions) + SUM(v.total_views) + SUM(v.total_unique_views) > 0 
ORDER BY c.contest_id

  • sudheerchowdar16
     + 0 comments

    can anyone explain, where i'm missing the logic ?

    WITH temp AS ( SELECT b.contest_id, SUM(total_submissions) AS sum_total_submissions, SUM(total_accepted_submissions) AS sum_total_accepted_submissions, SUM(total_views) AS sum_total_views, SUM(total_unique_views) AS sum_total_unique_views FROM colleges AS b LEFT JOIN challenges AS c ON b.college_id = c.college_id LEFT JOIN view_stats AS d ON c.challenge_id = d.challenge_id LEFT JOIN submission_stats AS e ON c.challenge_id = e.challenge_id GROUP BY b.contest_id ) SELECT a.contest_id,f.hacker_id,f.name,sum_total_submissions,sum_total_accepted_submissions, sum_total_views,sum_total_unique_views FROM temp as a inner join contests as f on a.contest_id=f.contest_id where (sum_total_submissions+ sum_total_accepted_submissions+ sum_total_views + sum_total_unique_views)> 0 order by a.contest_id ;

  • kiranbi0217
     + 0 comments

    can someone help me what is wrong in my code:

    with college_id as
    (select  co.name, co.contest_id, co.hacker_id, ch.challenge_id from contests co
    join colleges cl on co.contest_id = cl.contest_id
    join challenges ch on cl.college_id = ch.college_id)
select c.contest_id, c.hacker_id, c.name, sum(s.total_submissions), sum(s.total_accepted_submissions), sum(v.total_views), sum(v.total_unique_views) from college_id c
join submission_stats s on c.challenge_id = s.challenge_id
join view_stats v on c.challenge_id = v.challenge_id
where (s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) <> 0
group by c.contest_id, c.hacker_id, c.name
order by c.contest_id;
Load more conversations