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

|

1235 Discussions

|

  • karthickvenkat23
     + 0 comments

    USING RECURSION IN CTE

    SET NOCOUNT ON;
WITH CTE_details AS (
    SELECT submission_date,
        submission_id,
        hacker_id, 
        score,
        DENSE_RANK() OVER (PARTITION BY submission_date ORDER BY hacker_id) AS dist_rk,
        DENSE_RANK() OVER (ORDER BY submission_date) AS dt_rk
    FROM Submissions
), CTE_dt_hkr AS (
    SELECT d.*,r.dt_hk
    FROM CTE_details d
    JOIN (
        SELECT dt_rk,COUNT(DISTINCT dist_rk) AS dt_hk
        FROM CTE_details
        GROUP BY dt_rk 
    ) r 
        ON r.dt_rk = d.dt_rk
), CTE_recur AS (
    SELECT *,
        'each' AS eachs,
        1 AS anchor
    FROM CTE_dt_hkr
    WHERE dt_rk = 1

    UNION ALL

    SELECT a.*,
        r.eachs,
        r.anchor + 1
    FROM CTE_dt_hkr a 
    JOIN CTE_recur r 
        ON r.anchor + 1 = a.dt_rk AND r.hacker_id = a.hacker_id
), CTE_final AS (
    SELECT dh.submission_date,
        COUNT(DISTINCT r.hacker_id) AS each_ct,
        MAX(dh.score) AS score
    FROM CTE_dt_hkr dh 
    LEFT JOIN CTE_recur r
        ON r.submission_date = dh.submission_date
        AND r.submission_id = dh.submission_id
        AND r.hacker_id = dh.hacker_id
    GROUP BY dh.submission_date
), CTE_subs AS (
    SELECT submission_date,
    MIN(hacker_id) AS hacker_id
FROM (
    SELECT s.submission_date,
        s.hacker_id,
        s.ct
    FROM (
        SELECT submission_date,hacker_id, COUNT(1) AS ct
        FROM Submissions
        --WHERE submission_date = '2016-03-01'
        GROUP BY submission_date,hacker_id
    ) s
    JOIN (
        SELECT submission_date, MAX(ct) AS ct
        FROM (
            SELECT submission_date,hacker_id, COUNT(1) AS ct
            FROM Submissions
            --WHERE submission_date = '2016-03-01'
            GROUP BY submission_date,hacker_id
        ) q
        GROUP BY submission_date
    ) t
        ON t.submission_date = s.submission_date
        AND t.ct = s.ct
) g
GROUP BY submission_date
)

SELECT f.submission_date,
    f.each_ct,
    h.hacker_id,
    h.name
FROM CTE_final f
JOIN CTE_subs s
    ON s.submission_date = f.submission_date
JOIN Hackers h 
    ON h.hacker_id = s.hacker_id
ORDER BY f.submission_date



go

  • umeshtrivedi339
     + 0 comments
    WITH max_sub AS (
    SELECT submission_date,
           hacker_id
    FROM (
        SELECT submission_date,
               hacker_id,
               COUNT(DISTINCT submission_id) AS num_submissions,
               ROW_NUMBER() OVER (
                   PARTITION BY submission_date
                   ORDER BY COUNT(DISTINCT submission_id) DESC,
                            hacker_id ASC
               ) AS row_num
        FROM Submissions
        GROUP BY submission_date, hacker_id
    ) a1
    WHERE row_num = 1
),
each_day AS (
    SELECT s1.submission_date,
           COUNT(DISTINCT s1.hacker_id) AS num_hacker
    FROM Submissions s1
    WHERE s1.hacker_id IN (
        SELECT s2.hacker_id
        FROM Submissions s2
        WHERE s2.submission_date <= s1.submission_date
        GROUP BY s2.hacker_id
        HAVING COUNT(DISTINCT s2.submission_date) =
               DATEDIFF(DAY, '2016-03-01', s1.submission_date) + 1
    )
    GROUP BY s1.submission_date
)

SELECT m.submission_date,
       e.num_hacker,
       m.hacker_id,
       h.name
FROM max_sub m
JOIN each_day e
    ON m.submission_date = e.submission_date
JOIN Hackers h
    ON m.hacker_id = h.hacker_id
ORDER BY m.submission_date;

  • cristianandreir1
     + 1 comment

    Julia should try to be more independent and solver her own problems....

  • basu6607
     + 0 comments

    SELECT d.submission_date,

    (
    SELECT COUNT(DISTINCT s2.hacker_id)
    FROM Submissions s2
    WHERE s2.submission_date = d.submission_date
    AND (
        SELECT COUNT(DISTINCT s3.submission_date)
        FROM Submissions s3
        WHERE s3.hacker_id = s2.hacker_id
        AND s3.submission_date <= d.submission_date
    ) = DATEDIFF(d.submission_date, '2016-03-01') + 1
) AS total_hackers,

(
    SELECT s4.hacker_id
    FROM Submissions s4
    WHERE s4.submission_date = d.submission_date
    GROUP BY s4.hacker_id
    ORDER BY COUNT(*) DESC, s4.hacker_id ASC
    LIMIT 1
) AS hacker_id,

(
    SELECT h.name
    FROM Hackers h
    WHERE h.hacker_id = (
        SELECT s5.hacker_id
        FROM Submissions s5
        WHERE s5.submission_date = d.submission_date
        GROUP BY s5.hacker_id
        ORDER BY COUNT(*) DESC, s5.hacker_id ASC
        LIMIT 1
    )
) AS name

    FROM (SELECT DISTINCT submission_date FROM Submissions) d ORDER BY d.submission_date;

  • rravulapalli6
     + 0 comments
    WITH user_submissions AS (
    SELECT 
        hacker_id,
        submission_date,
        ROW_NUMBER() OVER(PARTITION BY submission_date 
                                              ORDER BY COUNT(*) DESC, hacker_id ASC) 
																											 AS hacker_rk,

        DATEADD(DAY,
                       -ROW_NUMBER() OVER(PARTITION BY hacker_id
                                                               ORDER BY submission_date) + 1,
                        submission_date) AS first_submission_date
    FROM submissions 
    GROUP BY hacker_id, submission_date
),
submission_stats AS (
    SELECT 
        submission_date, 
        SUM(CASE WHEN first_submission_date = '2016-03-01' 
		        THEN 1 
			  ELSE 0 
				END) AS num_users,
        MIN(CASE WHEN hacker_rk = 1 
		       THEN hacker_id 
			            END) AS hacker_id
    FROM user_submissions 
    GROUP BY submission_date
)
SELECT 
    submission_date,
    num_users,
    s.hacker_id,
    h.name
FROM submission_stats s 
INNER JOIN hackers h 
            ON s.hacker_id = h.hacker_id
ORDER BY submission_date