Sort by

recency

|

1376 Discussions

|

  • + 0 comments

    I follow the guy below me, and it worked. I break it up to be more readable:

        
    SELECT 
        c.contest_id,
        c.hacker_id, 
        c.name, 
        sum(ts),
        sum(tas),
        sum(tv),
        sum(tuv) 
    FROM 
        contests c 
    INNER JOIN 
        colleges col 
    ON 
        c.contest_id = col.contest_id 
    INNER JOIN
        challenges cha 
    ON
        cha.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 
            1 ) vs 
    ON 
        vs.challenge_id = cha.challenge_id 
    LEFT JOIN
        (select 
            challenge_id, 
            sum(total_submissions) as ts, 
            sum(total_accepted_submissions) as tas 
         from 
            submission_stats 
         group by 
            1 ) ss 
    ON
        ss.challenge_id = cha.challenge_id 
    GROUP BY 
        1,2,3 
    HAVING 
        sum(tv)+sum(tuv)+sum(ts)+sum(tas) >0 
    ORDER BY 
        contest_id
    
  • + 0 comments

    I tried this, but it does not work. I dunno what the s. is going on :/

    SELECT
        ccc.contest_id,
        ccc.hacker_id,
        ccc.name,
        SUM(vst.ss),
        SUM(vst.sas),
        SUM(vst.sv),
        SUM(vst.suv)
    FROM      
        (SELECT
            ct.contest_id AS contest_id,
            ct.hacker_id AS hacker_id,
            ct.name AS name,
            c.college_id AS c_id
        FROM
            contests ct
        INNER JOIN
            colleges c
        ON
            ct.contest_id = c.contest_id
        RIGHT JOIN
            challenges ch
        ON
            c.college_id = ch.college_id
        GROUP BY
            ct.contest_id,
            ct.hacker_id,
            ct.name,
            c.college_id
        HAVING
            COUNT(ch.challenge_id) > 0) ccc
    INNER JOIN
        (SELECT
            ch.college_id AS c_id,
            IFNULL(SUM(chs.ss),0) AS ss,
            IFNULL(SUM(chs.sas),0)AS sas,
            IFNULL(SUM(chs.sv),0) AS sv,
            IFNULL(SUM(chs.suv),0) AS suv
        FROM
            challenges ch
        LEFT JOIN
            (SELECT
                v.challenge_id AS ch_id,
                IFNULL(SUM(total_submissions),0) AS ss,
                IFNULL(SUM(total_accepted_submissions),0) AS sas,
                IFNULL(SUM(total_views),0) AS sv,
                IFNULL(SUM(total_unique_views),0) AS suv
            FROM
                view_stats v
            LEFT JOIN
                submission_stats s
            ON
                v.challenge_id = s.challenge_id
            GROUP BY 
                ch_id) chs
        ON 
            ch.challenge_id = chs.ch_id
        WHERE   
            NOT (ss = 0 AND sas = 0 AND sv = 0 AND suv = 0)
        GROUP BY
            ch.college_id) vst
    ON
        ccc.c_id = vst.c_id
    GROUP BY
        ccc.contest_id,
        ccc.hacker_id,
        ccc.name
    ORDER BY
        ccc.contest_id
    
  • + 0 comments

    select c.contest_id, c.hacker_id, c.name, sum(ts),sum(tas),sum(tv),sum(tuv) from contests c inner join colleges col on c.contest_id = col.contest_id inner join challenges cha on cha.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 1 ) vs on vs.challenge_id = cha.challenge_id left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by 1 ) ss on ss.challenge_id = cha.challenge_id group by 1,2,3 having sum(tv)+sum(tuv)+sum(ts)+sum(tas) >0 order by contest_id

  • + 0 comments

    might have made it overly complicated, but it finally worked ...

    WITH -- Aggregate view_stats by challenge

    view_agg AS ( SELECT challenge_id, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM view_stats GROUP BY challenge_id ),

    -- Aggregate submission_stats by challenge

    submission_agg AS ( SELECT challenge_id, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas FROM submission_stats GROUP BY challenge_id ),

    -- Join challenges to view/submission stats

    challenge_data AS ( SELECT ch.college_id, ch.challenge_id, COALESCE(sa.ts, 0) AS ts, COALESCE(sa.tas, 0) AS tas, COALESCE(va.tv, 0) AS tv, COALESCE(va.tuv, 0) AS tuv FROM challenges ch LEFT JOIN submission_agg sa ON ch.challenge_id = sa.challenge_id LEFT JOIN view_agg va ON ch.challenge_id = va.challenge_id ),

    -- Aggregate all stats by college

    college_totals AS ( SELECT college_id, SUM(ts) AS cts, SUM(tas) AS ctas, SUM(tv) AS ctv, SUM(tuv) AS ctuv FROM challenge_data GROUP BY college_id ),

    -- Join college_totals to contests and colleges

    all_data AS ( SELECT con.hacker_id, con.name, col.contest_id, colt.college_id, colt.cts, colt.ctas, colt.ctv, colt.ctuv FROM college_totals colt JOIN colleges col on colt.college_id = col.college_id JOIN contests con on col.contest_id = con.contest_id),

    -- Aggregate all stats by contest

    contest_totals AS ( SELECT contest_id, hacker_id, name, SUM(cts) AS total_submissions, SUM(ctas) AS total_accepted_submissions, SUM(ctv) AS total_views, SUM(ctuv) AS total_unique_views FROM all_data GROUP BY contest_id, hacker_id, name )

    -- Final filter and select

    SELECT contest_id, hacker_id, name, total_submissions, total_accepted_submissions, total_views, total_unique_views FROM contest_totals WHERE (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 ORDER BY contest_id;

  • + 1 comment

    WITH tb1 AS ( SELECT col.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY col.contest_id ), tb2 AS ( SELECT col.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN View_Stats vs ON ch.challenge_id = vs.challenge_id GROUP BY col.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tb1.ts, tb1.tas, tb2.tv, tb2.tuv FROM Contests con JOIN tb1 ON con.contest_id = tb1.contest_id JOIN tb2 ON con.contest_id = tb2.contest_id WHERE tb1.ts > 0 AND tb1.tas > 0 AND tb2.tv > 0 AND tb2.tuv > 0 ORDER BY con.contest_id;