15 Days of Learning SQL

Sort by

recency

|

1215 Discussions

|

  • + 1 comment
    -- Filter out submissions from the second time onwards 
    WITH distinct_values AS (
        SELECT DISTINCT 
            submission_date, 
            hacker_id
        FROM submissions
    ), 
    -- Filter hackers who submitted the required number of submissions for each corresponding day (rolling/counting up to each day).
    filter_hackers AS(
        SELECT
            submission_date,
            hacker_id,
            DENSE_RANK() OVER(ORDER BY submission_date ASC) AS rank_date,
            COUNT(hacker_id) OVER(PARTITION BY hacker_id ORDER BY submission_date ASC) AS hacker_sub 
        FROM distinct_values
    ),
    -- Count the number of submissions each hacker made per day
    top_sub AS (
        SELECT 
            s.submission_date,
            s.hacker_id, 
            h.name,
            COUNT(s.hacker_id) AS count_sub,
            ROW_NUMBER() OVER(PARTITION BY s.submission_date ORDER BY submission_date ASC, COUNT(s.hacker_id) DESC ,s.hacker_id ASC) AS flag
        FROM submissions AS s 
        JOIN hackers AS h 
            ON h.hacker_id = s.hacker_id
        GROUP BY s.submission_date, s.hacker_id, h.name
    ) 
    SELECT
        fh.submission_date,
        COUNT(fh.hacker_id),
        ts.hacker_id,
        ts.name
    FROM filter_hackers AS fh
    JOIN top_sub AS ts
        ON fh.submission_date = ts.submission_date 
    WHERE fh.rank_date = fh.hacker_sub AND ts.flag = 1
    GROUP BY fh.submission_date, ts.hacker_id, ts.name
    ORDER BY fh.submission_date ASC
    
  • + 0 comments

    MS SQL SERVER

    WITH TBL1 AS ( -- all hacker_id who made at least one submission each day
        SELECT DISTINCT
            submission_date
            ,hacker_id
        From Submissions Sub
        WHERE hacker_id IN (
            SELECT DISTINCT hacker_id 
            FROM Submissions 
            WHERE submission_date <= Sub.submission_date
            GROUP BY hacker_id
            HAVING COUNT(DISTINCT submission_date) = (
                SELECT COUNT(DISTINCT submission_date)
                FROM Submissions
                WHERE submission_date <= Sub.submission_date
            )
        )
    )
    ,TBL2 AS ( --no of unique hacker_id who made at least one submission each day
        SELECT
            submission_date
            ,COUNT(hacker_id) AS hackerNr
        From TBL1
        GROUP BY submission_date
    )
    ,TBL3 AS ( -- number of submissions made by hacker each day
        SELECT 
            hacker_id
            ,submission_date
            ,COUNT(submission_id) AS nrSub 
        FROM Submissions 
        GROUP BY hacker_id, submission_date
    )
    ,TBL4 AS ( -- lowest hacker_id each day
        SELECT DISTINCT
            T1.submission_date
            ,T2.hackerNr
            ,MIN(T3.hacker_id) AS hackerID
        FROM TBL1 T1
        LEFT JOIN TBL2 T2 ON T1.submission_date = T2.submission_date
        LEFT JOIN TBL3 T3 ON T1.submission_date = T3.submission_date
        WHERE T3.nrSub = (SELECT MAX(nrSub) FROM TBL3 WHERE submission_date = T1.submission_date GROUP BY submission_date)
        GROUP BY T1.submission_date, T2.hackerNr
    )
    SELECT 
        TBL4.*
        ,H.name
    FROM TBL4
    LEFT JOIN Hackers H ON H.hacker_id = TBL4.hackerID
    ORDER BY TBL4.submission_date
    
  • + 0 comments

    In Oracle:

    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;
    
    WITH champions AS (
    SELECT
        s_r.submission_date,
        s_r.hacker_id
    FROM
    (
        SELECT
            s_c.submission_date,
            s_c.hacker_id,
            s_c.subs,
            ROW_NUMBER() OVER(PARTITION BY s_c.submission_date ORDER BY s_c.subs DESC, s_c.hacker_id ASC) rn
        FROM
        (
            SELECT
                s.submission_date,
                s.hacker_id,
                COUNT(*) subs
            FROM Submissions s
            GROUP BY s.submission_date, s.hacker_id
        ) s_c
    ) s_r
    WHERE s_r.rn = 1
    ),
    dated_subs AS (
        SELECT s.submission_date,
            COUNT(DISTINCT s.hacker_id) unique_hackers
        FROM Submissions s
        WHERE s.hacker_id IN (
            SELECT
                s2.hacker_id
            FROM Submissions s2
            WHERE s2.hacker_id = s.hacker_id
                AND s2.submission_date <= s.submission_date
            GROUP BY s2.hacker_id
            HAVING COUNT(DISTINCT s2.submission_date) = (
                SELECT COUNT(DISTINCT(submission_date))
                FROM Submissions
                WHERE Submissions.submission_date <= s.submission_date
            )
        )
        GROUP BY s.submission_date
    )
    SELECT
        ds.submission_date,
        ds.unique_hackers,
        c.hacker_id,
        h.name
    FROM dated_subs ds
    INNER JOIN champions c
        ON c.submission_date = ds.submission_date
    INNER JOIN Hackers h
        ON h.hacker_id = c.hacker_id
    ORDER BY ds.submission_date ASC;
    
    exit;
    
  • + 0 comments

    SET NOCOUNT ON;

    WITH DATA_TANGGAL AS ( SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15'

    ), DATA_HACKERS_MAX AS ( SELECT a.HACKER_ID, a.NAME, MIN(a.SUBMISSION_DATE) AS MIN_SUBMISSION_DATE FROM (SELECT DISTINCT SUBMISSION_DATE, HACKER_ID, NAME FROM HACKERS CROSS JOIN DATA_TANGGAL) A LEFT JOIN (select distinct SUBMISSION_DATE, HACKER_ID from SUBMISSIONS
    WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15' ) B ON A.HACKER_ID = B.HACKER_ID AND A.SUBMISSION_DATE = B.SUBMISSION_DATE WHERE B.HACKER_ID IS NULL GROUP BY a.HACKER_ID, a.NAME ) , COUNT_DATA AS (

    SELECT SUBMISSION_DATE,COUNT(*) AS TOTAL FROM (
    SELECT DISTINCT A.SUBMISSION_DATE ,A.HACKER_ID FROM SUBMISSIONS A 
    INNER JOIN DATA_TANGGAL B 
    ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
    LEFT JOIN DATA_HACKERS_MAX C 
    ON A.HACKER_ID = C.HACKER_ID 
    WHERE C.HACKER_ID IS NULL OR A.SUBMISSION_DATE <= C.MIN_SUBMISSION_DATE
    ) A 
    GROUP BY SUBMISSION_DATE
    

    ), MAX_DATA AS (

    SELECT SUBMISSION_DATE,HACKER_ID,NAME,COUNT(SCORE) AS TOTAL FROM (
    SELECT DISTINCT A.SUBMISSION_DATE ,SUBMISSION_ID,NAME,A.HACKER_ID, SCORE FROM SUBMISSIONS A 
    INNER JOIN DATA_TANGGAL B 
    ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
    INNER JOIN HACKERS C 
    ON A.HACKER_ID = C.HACKER_ID
    ) A 
    GROUP BY SUBMISSION_DATE, HACKER_ID, NAME
    

    ), MAX_DATA_FINAL AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY SUBMISSION_DATE ORDER BY TOTAL DESC, HACKER_ID ASC) AS RN FROM MAX_DATA )

    SELECT A.SUBMISSION_DATE, B. TOTAL, C.HACKER_ID, C.NAME

    FROM DATA_TANGGAL A LEFT JOIN COUNT_DATA B ON A.SUBMISSION_DATE = B.SUBMISSION_DATE LEFT JOIN MAX_DATA_FINAL C ON A.SUBMISSION_DATE = C.SUBMISSION_DATE WHERE RN = 1 ORDER BY A.SUBMISSION_DATE ASC

    go

  • + 0 comments

    --Using SQL Server-- declare @consistent_hackers table( hacker_id int, submission_date date );

    declare @count_uniqhackers table( hacker_count int, submission_date date );

    insert into @consistent_hackers select hacker_id, submission_date from submissions where submission_date like '2016-03-01' declare @submssn_date date declare @remaining_date date

    set @submssn_date = '2016-03-01' set @remaining_date = '2016-03-01'

    while @submssn_date < '2016-03-15' begin set @submssn_date = dateadd(day, 1, @submssn_date) insert into @consistent_hackers select s.hacker_id, s.submission_date from submissions s join @consistent_hackers c on s.hacker_id = c.hacker_id and c.submission_date like @remaining_date where s.submission_date like @submssn_date set @remaining_date = dateadd(day, 1, @remaining_date); end; insert into @count_uniqhackers select count(distinct hacker_id), submission_date from @consistent_hackers group by submission_date ;

    with max_hacker as ( select row_number() over( partition by s.submission_date order by count(s.hacker_id) desc, s.hacker_id asc ) as rn, s.hacker_id, s.submission_date, name from submissions s inner join hackers h on s.hacker_id = h.hacker_id group by s.hacker_id, s.submission_date, name ) select s.submission_date, c.hacker_count, m.hacker_id, m.name from submissions s join max_hacker m on s.submission_date = m.submission_date and m.rn = 1 join @count_uniqhackers c on s.submission_date = c.submission_date --where --s.submission_date like '2016-03-01' group by s.submission_date, c.hacker_count, m.hacker_id, m.name order by s.submission_date

    go