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

|

1206 Discussions

|

  • joshisonu252
     + 0 comments

    I didn't get the question, how are the number of sumbissions everyday varying? If they're daily submitters, their count would be seen daily, ie. if 4 people submitted on the 1st they'd submit even on the 2, 3 and so on. Also the min(hacker_id) is changing daily.

  • gs06sid
     + 0 comments
    WITH
  cte1 AS   (   SELECT s.submission_date, s.submission_id, s.hacker_id, h.name
                    , DAY(s.submission_date) AS day_num
                    , DENSE_RANK() OVER(PARTITION BY s.hacker_id ORDER BY s.submission_date) AS rnk
                FROM submissions s
                JOIN hackers h ON h.hacker_id = s.hacker_id
                WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' )
, cte2 AS   (   SELECT submission_date, COUNT(DISTINCT hacker_id) AS unique_h_count
                FROM cte1
                WHERE day_num = rnk
                GROUP BY submission_date )

, cte3 AS   (   SELECT submission_date, hacker_id, COUNT(submission_id) AS s_count
                FROM cte1
                GROUP BY submission_date, hacker_id )
, cte4 AS   (   SELECT *, ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY s_count DESC, hacker_id) AS rn
                FROM cte3 )

SELECT cte4.submission_date, cte2.unique_h_count, cte4.hacker_id, h.name
FROM cte4
JOIN hackers h ON h.hacker_id = cte4.hacker_id
JOIN cte2 ON cte2.submission_date = cte4.submission_date
WHERE rn = 1

  • grv08singh
     + 0 comments
    WITH 
cte0 AS( --Join of tables (Creating Master Table).
    SELECT s.submission_date, s.submission_id, s.hacker_id, h.name
    FROM submissions s
    LEFT JOIN hackers h ON s.hacker_id = h.hacker_id
    WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15'),
cte1 AS( --Date & Sequence
    SELECT 
        submission_date,
        ROW_NUMBER() OVER(ORDER BY submission_date ASC) AS dt_seq
    FROM cte0
    GROUP BY submission_date),
cte2 AS( --All hackers with at least one submission.
    SELECT hacker_id, submission_date, COUNT(submission_id) AS sb_count
    FROM cte0
    GROUP BY hacker_id, submission_date
    HAVING COUNT(submission_id)>0),
cte3 AS( --All hackers with at least one submission each day.
    SELECT 
        hacker_id, submission_date,
        ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS h_seq
    FROM cte2),
cte4 AS ( -- result of first query
    SELECT cte3.submission_date, COUNT(hacker_id) AS num_hackers
    FROM cte3
    JOIN cte1 ON cte1.submission_date = cte3.submission_date AND cte1.dt_seq = cte3.h_seq
    GROUP BY cte3.submission_date),
cte5 AS ( 
    SELECT 
        submission_date, hacker_id,
        RANK() OVER(PARTITION BY submission_date ORDER BY sb_count DESC, hacker_id ASC) AS rnk
    FROM cte2),
cte6 AS ( -- result of 2nd query
    SELECT submission_date, hacker_id 
    FROM cte5 
    WHERE rnk=1),
cte7 AS (
    SELECT hacker_id, name
    FROM cte0
    GROUP BY hacker_id, name)

SELECT cte4.submission_date, cte4.num_hackers, cte6.hacker_id, cte7.name
FROM cte4
JOIN cte6 ON cte6.submission_date = cte4.submission_date
JOIN cte7 ON cte7.hacker_id = cte6.hacker_id
ORDER BY cte4.submission_date;

  • jr_josias2
     + 0 comments

    I was pursuing the wrong solution, as the problem statement said to print the total of unique hackers that had submitted to every day, I thought the whole period of 15 days, not the up-to-day.

    After struggling a lot, to calculate the distinct hackers up-to-day, that had submitted work on previous days, I decided to sneak and peak in here and found the solution from @praval8765419559, although very resourceful, I thought it hard to comprehend, and I decided to reverse engineering his solution, and here is MySQL script that worked:

    SELECT
    min_hk_id.submission_date AS submission_date,
    hacker_counts.hacker_count,
    MIN(min_hk_id.hacker_id) AS hacker_id,
    (SELECT name FROM hackers WHERE hacker_id = MIN(min_hk_id.hacker_id)) AS name
FROM(
    SELECT
        submission_date,
        hacker_id,
        COUNT(hacker_id) AS sub_counts
    FROM submissions
    GROUP BY 1,2
) AS min_hk_id
JOIN(
    SELECT submission_date, MAX(sub_counts) AS sub_counts
    FROM(
        SELECT
            submission_date,
            hacker_id,
            COUNT(hacker_id) AS sub_counts
        FROM submissions
        GROUP BY 1,2
    ) as inner_counts
    GROUP BY 1
) AS max_counts
ON min_hk_id.submission_date = max_counts.submission_date AND min_hk_id.sub_counts = max_counts.sub_counts
JOIN(
    SELECT s.submission_date, COUNT(*) AS hacker_count
    FROM (
      SELECT d1.hacker_id, d1.submission_date
      FROM (SELECT DISTINCT hacker_id, submission_date FROM submissions) d1
      JOIN (SELECT DISTINCT hacker_id, submission_date FROM submissions) d2
        ON d2.hacker_id = d1.hacker_id
       AND d2.submission_date <= d1.submission_date
      GROUP BY d1.hacker_id, d1.submission_date
      HAVING COUNT(*) = DATEDIFF(d1.submission_date, DATE '2016-03-01') + 1
    ) AS s
    GROUP BY s.submission_date
) AS hacker_counts
ON hacker_counts.submission_date = min_hk_id.submission_date
GROUP BY min_hk_id.submission_date,hacker_counts.hacker_count

    Was a great challenge, even though I failed, I could refresh older concepts and grasp new ones.

    `

  • rena2mofu
     + 0 comments

    MS SQL Server:

    WITH all_days AS ( SELECT DISTINCT submission_date FROM submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' ), daily_loyalty AS ( SELECT d.submission_date AS ref_date, s.hacker_id, s.submission_date FROM all_days d JOIN submissions s ON s.submission_date <= d.submission_date ), loyal_hackers AS ( SELECT ref_date, hacker_id, COUNT(DISTINCT submission_date) AS days_active FROM daily_loyalty GROUP BY ref_date, hacker_id ), final_counts AS ( SELECT ref_date, COUNT(*) AS loyal_hacker_count FROM loyal_hackers WHERE days_active = DATEDIFF(DAY, '2016-03-01', ref_date) + 1 GROUP BY ref_date ), count_date AS ( SELECT s.submission_date, count(s.hacker_id) AS totals, h.hacker_id, h.name FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id GROUP BY submission_date, h.hacker_id, h.name ), max_date AS ( SELECT submission_date, max(totals) AS max_totals FROM count_date GROUP BY submission_date ), min_hacker as ( SELECT c.submission_date, c.totals AS max_totals, hacker_id, name, fc.loyal_hacker_count AS totale FROM count_date c JOIN max_date m ON c.submission_date = m.submission_date AND c.totals = m.max_totals JOIN final_counts fc ON c.submission_date = fc.ref_date ) SELECT submission_date, totale, hacker_id, name FROM min_hacker mh WHERE hacker_id = (SELECT MIN(hacker_id) FROM min_hacker m WHERE m.submission_date = mh.submission_date) ORDER BY submission_date;