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. 15 Days of Learning SQL
  5. Discussions

15 Days of Learning SQL

Sort by

recency

|

1215 Discussions

|

  • minhtu6336
     + 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

  • dobrocry
     + 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

  • neutronmax
     + 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;

  • agung_miantoro
     + 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

  • olorunsogoabiod1
     + 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