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

|

1394 Discussions

|

  • sammpathan
     + 0 comments

    Simple and faster query as below

    with cte1 as ( select a.contest_id s, a.hacker_id t, a.name u,
    sum(d.total_submissions) w, sum(d.total_accepted_submissions) x 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 d on c.challenge_id = d.challenge_id

    group by a.contest_id, a.hacker_id, a.name), cte2 as ( select e.contest_id l, e.hacker_id m, e.name n,
    sum(h.total_views) y, sum(h.total_unique_views) z from contests e left join colleges f on e.contest_id = f.contest_id left join challenges g on f.college_id = g.college_id left join view_stats h on g.challenge_id = h.challenge_id

    group by e.contest_id, e.hacker_id, e.name )

    select cte1.s, cte1.t, cte1.u, cte1.w, cte1.x, cte2.y, cte2.z from cte1, cte2 where cte1.s = cte2.l and cte1.t = cte2.m and cte1.u = cte2.n and cte1.w+cte1.x+cte2.y+cte2.z>0 order by cte1.s asc

  • angad_gadre_1989
     + 0 comments

    I'm not sure why this logic won't work. More generally, this looks like an edge case testing interview question (e.g. for nulls etc) and don't see much value with

    WITH contest_stats AS ( -- First aggregate all metrics at the contest level SELECT co.contest_id, 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 colleges co LEFT JOIN challenges ch ON ch.college_id = co.college_id LEFT JOIN view_stats vs ON vs.challenge_id = ch.challenge_id LEFT JOIN submission_stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id )

    -- Join back to get contest details SELECT c.contest_id, c.hacker_id, c.name, cs.total_submissions, cs.total_accepted_submissions, cs.total_views, cs.total_unique_views FROM contests c JOIN contest_stats cs ON c.contest_id = cs.contest_id WHERE (cs.total_submissions + cs.total_accepted_submissions+cs.total_views+cs.total_unique_views) > 0 ORDER BY c.contest_id;

  • j_ibarralemp
     + 1 comment

    Took a little advice from previous posts here, but arrived at a solution finally.

    Careful with the null values and group all to contest level with college table before doing the final join with contests.

    SELECT con.contest_id,
    con.hacker_id,
    con.name,
    ts,
    tas,
    tv,
    tuv
FROM contests AS con
INNER JOIN (
    SELECT coll.contest_id,
        SUM(COALESCE(ts,0)) AS ts,
        SUM(COAlESCE(tas,0)) AS tas,
        SUM(COALESCE(tv,0)) AS tv,
        SUM(COALESCE(tuv,0)) AS tuv
    FROM challenges AS chall
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_submissions,0)) AS ts,
                SUM(COALESCE(total_accepted_submissions,0)) AS tas
            FROM submission_stats AS sstats
            GROUP BY challenge_id
            ) AS sstats
    ON chall.challenge_id = sstats.challenge_id
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_views,0)) AS tv,
                SUM(COALESCE(total_unique_views,0)) AS tuv
            FROM view_stats AS vstats
            GROUP BY challenge_id
            ) AS vstats
    ON chall.challenge_id = vstats.challenge_id
    LEFT JOIN colleges AS coll
    ON chall.college_id = coll.college_id
    GROUP BY coll.contest_id) AS fstats
ON con.contest_id = fstats.contest_id
ORDER BY con.contest_id
;

  • BlurFeng
     + 0 comments

    Because I didn't fully understand INNER JOIN, I spent some time on this. INNER JOINs are chained, so if any condition is not met, the entire row is discarded. Therefore, data like 'TV=NULL, TUV=NULL, TS=8, TAS=4' would be discarded. Below is my answer.

    SELECT
    Co.contest_id,
    Co.hacker_id,
    Co.name,
    TS,
    TAS,
    TV,
    TUV
FROM Contests AS Co
JOIN (
    SELECT
        Col.contest_id AS contest_id,
        SUM(TV) AS TV,
        SUM(TUV) AS TUV,
        SUM(TS) AS TS,
        SUM(TAS) AS TAS
    FROM Colleges AS Col
    JOIN Challenges AS Ch ON Ch.college_id = Col.college_id 
    LEFT JOIN (
        SELECT
            challenge_id,
            SUM(total_views) AS TV,
            SUM(total_unique_views) AS TUV
        FROM View_Stats GROUP BY challenge_id
    ) AS VS ON VS.challenge_id = Ch.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 SS ON SS.challenge_id = Ch.challenge_id
    GROUP BY Col.contest_id
) AS CD ON CD.contest_id = Co.contest_id
WHERE TV + TUV + TS + TAS > 0
ORDER BY Co.contest_id

  • mohabdev25
     + 1 comment

    Guys my query runs but still hackerrank can't accept it as it's valid , can anyone tell me why ?

    with tvs as (
select challenge_id ,
        sum(total_views) "tv" ,
        sum(total_unique_views) "tuv"

from View_Stats 
group by challenge_id
) ,
tss as ( 
    select challenge_id,
        sum(total_submissions) "t_sub",
        sum(total_accepted_submissions)"t_acc_sub"
    from Submission_Stats 
    group by challenge_id
)


select 
    c.contest_id , 
    c.hacker_id , 
    c.name ,
    coalesce(sum(tvs.tv),0) "total_views",
    coalesce(sum(tvs.tuv),0) "total_unique_views", 
    coalesce(sum(tss.t_sub),0) "t_sub",
    coalesce(sum(tss.t_acc_sub),0) "t_acc_subs"

from Contests c
LEFT JOIN Colleges co on c.contest_id = co.contest_id
LEFT JOIN Challenges ch on co.college_id = ch.college_id
LEFT JOIN tvs  on tvs.challenge_id = ch.challenge_id
LEFT JOIN tss on tss.challenge_id = ch.challenge_id 

group by  c.contest_id ,c.hacker_id , c.name 

having coalesce(sum(tvs.tv),0)  + 
        coalesce(sum(tvs.tuv),0) +
        coalesce(sum(tss.t_sub),0) +
        coalesce(sum(tss.t_acc_sub),0) <> 0

    order by c.contest_id ;