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

|

1450 Discussions

|

  • pavnisaxena1
     + 0 comments

    with cte as ( select a.contest_id ,a.hacker_id,a.name,sum(d.total_views) as total_views,sum(d.total_unique_views) as total_unique_views --sum(d.total_views),sum(d.total_unique_views), sum(e.total_submissions) , sum(e.total_accepted_submission) from contests as a left join Colleges b on a.contest_id = b.contest_id left join Challenges as c on c.college_id = b.college_id left join View_Stats as d on d.challenge_id = c.challenge_id group by a.contest_id ,a.hacker_id,a.name), cte2 as ( select a.contest_id ,a.hacker_id,a.name,sum(e.total_submissions) as total_submissions ,sum(e.total_accepted_submission) as total_accepted_submission --sum(d.total_views),sum(d.total_unique_views), sum(e.total_submissions) , sum(e.total_accepted_submission) from contests as a left join Colleges b on a.contest_id = b.contest_id left join Challenges as c on c.college_id = b.college_id left join Submission_Stats as e on e.challenge_id = c.challenge_id group by a.contest_id ,a.hacker_id,a.name)

    select a.contest_id ,a.hacker_id,a.name,total_views,total_unique_views ,total_submissions,total_accepted_submission from cte as a inner join cte2 as b on a.contest_id = b.contest_id group by all ;

  • songala_rohith
     + 0 comments

    -- MYSQL Solution without WITH clause

    SELECT contest_id, hacker_id, name, summ_sub, sum_acc_sub, sum_vws, sum_unq_vws FROM ( SELECT co.contest_id, con.hacker_id, con.name, SUM(CASE WHEN sts.sum_sub IS NULL THEN 0 ELSE sts.sum_sub END) summ_sub, SUM(CASE WHEN sts.sum_ac_sub IS NULL THEN 0 ELSE sts.sum_ac_sub END) sum_acc_sub, SUM(CASE WHEN svs.sum_views IS NULL THEN 0 ELSe svs.sum_views END) sum_vws, SUM(CASE WHEN svs.sum_unq_views IS NULL THEN 0 ELSE svs.sum_unq_views END) sum_unq_vws FROM contests con JOIN colleges co on co.contest_id = con.contest_id JOIN challenges ch on co.college_id = ch.college_id LEFT JOIN ( SELECT vs.challenge_id, SUM(vs.total_views) sum_views, SUM(vs.total_unique_views) sum_unq_views FROM view_Stats vs GROUP BY vs.challenge_id ) svs on ch.challenge_id = svs.challenge_id LEFT JOIN ( SELECT ss.challenge_id, SUM(ss.total_submissions) sum_sub, SUM(ss.total_accepted_submissions) sum_ac_sub FROM submission_stats ss GROUP BY ss.challenge_id ) sts on ch.challenge_id = sts.challenge_id GROUP BY co.contest_id, con.hacker_id, con.name ) inr_qry

    WHERE summ_sub <> 0 and sum_acc_sub <> 0 and sum_vws <> 0 and sum_unq_vws <> 0 ORDER BY contest_id

  • gdawar95
     + 0 comments
    SET NULL "NULL";
SET FEEDBACK OFF;
SET ECHO OFF;
SET HEADING OFF;
SET WRAP OFF;
SET LINESIZE 10000;
SET TAB OFF;
SET PAGES 0;
SET DEFINE OFF;

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
with subs as (
    select 
        challenge_id
        , sum(coalesce(total_submissions, 0)) tsubs
        , sum(coalesce(total_accepted_submissions,0)) tasubs
    from Submission_Stats
    group by challenge_id
)
, views as (
    select 
        challenge_id
        , sum(coalesce(total_views,0)) tviews
        , sum(coalesce(total_unique_views,0)) tuviews
    from View_Stats
    group by challenge_id
)
, constest_hacker as (
    select
        ct.contest_id
        , ct.hacker_id
        , ct.name
        , ch.challenge_id
    from Contests ct
    left join Colleges cl on ct.contest_id = cl.contest_id
    left join Challenges ch on cl.college_id = ch.college_id
)
select 
    ch.contest_id
    , ch.hacker_id
    , ch.name
    , sum(coalesce(s.tsubs,0)) stsubs
    , sum(coalesce(s.tasubs,0)) stasubs
    , sum(coalesce(v.tviews,0)) stviews
    , sum(coalesce(v.tuviews,0)) stuviews
from constest_hacker ch
left join views v on ch.challenge_id = v.challenge_id
left join subs s on ch.challenge_id = s.challenge_id
group by ch.contest_id, ch.hacker_id, ch.name
having   ( SUM(COALESCE(s.tsubs,0))
  + SUM(COALESCE(s.tasubs,0))
  + SUM(COALESCE(v.tviews,0))
  + SUM(COALESCE(v.tuviews,0))
  ) > 0
order by contest_id
;

exit;

  • smnkmr
     + 0 comments

    with marks as ( select coalesce(v.challenge_id, s.challenge_id) challenge_id -- ,c.college_id ,sum(v.total_views) total_views ,sum(v.total_unique_views) total_unique_views ,sum(s.total_submissions) total_submissions ,sum(s.total_accepted_submissions) total_accepted_submissions -- from Challenges c from View_Stats v -- on c.challenge_id=v.challenge_id full join Submission_Stats s on v.challenge_id=s.challenge_id group by coalesce(v.challenge_id, s.challenge_id) ), contest as ( select c.contest_id ,c.hacker_id ,c.name ,cl.college_id from Contests C left join Colleges cl on c.contest_id = cl.contest_id

    ), smry as ( select c.contest_id ,c.hacker_id ,c.name ,sum(m.total_views) total_views ,sum(m.total_unique_views) total_unique_views ,sum(m.total_submissions) total_submissions ,sum(m.total_accepted_submissions) total_accepted_submissions from contest c left join Colleges cl on c.contest_id = cl.contest_id left join Challenges ch on cl.college_id=ch.college_id left join marks m on m.challenge_id=ch.challenge_id group by c.contest_id, c.hacker_id, c.name ) select m.* from smry m where (case when ( (total_views = 0 or total_views is null) and (total_unique_views =0 or total_unique_views is null) and (total_submissions =0 or total_submissions is null) and (total_accepted_submissions =0 or total_accepted_submissions is null) ) then 0 else 1 end)=1

  • patelsudha301
     + 0 comments

    select A.contest_id, A.hacker_id, A.name, sum(D.total_submissions)as [total Submission ] , sum( D.total_accepted_submissions )as [total accepted submissions], sum( E.total_views ) as [ total views], sum(E.total_unique_views ) as [total unique views] from Contests as A inner join Colleges as B ON A.contest_id = B.contest_id inner join Challenges as C on B.college_id = c.college_id left join Submission_Stats as D on C.challenge_id = D. challenge_id left join View_Stats as E on D. challenge_id = E.challenge_id GROUP BY A.contest_id, A.hacker_id,A.name HAVING sum(D. total_submissions) > 0 or sum( D.total_accepted_submissions )>0 or sum( E.total_views )>0 or sum(E.total_unique_views )> 0 order by A.contest_id asc ;