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

|

1194 Discussions

|

  • shubhamkt3
     + 0 comments

    WITH t1 AS ( SELECT submission_date, hacker_id, COUNT(submission_id) AS cnt FROM Submissions GROUP BY submission_date, hacker_id ), t2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id) AS rn FROM t1 ), t3 AS ( SELECT t2.submission_date, t2.hacker_id, b.name, t2.cnt FROM t2 JOIN Hackers b ON t2.hacker_id = b.hacker_id WHERE t2.rn = 1 ), t4 AS ( SELECT hacker_id, submission_date, Dense_rank() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS row_num FROM Submissions group by hacker_id, submission_date ), t5 AS ( SELECT submission_date, count(hacker_id) as hcnt FROM t4 where Datepart(Day, submission_date) = row_num group by submission_date )

    SELECT t5.submission_date, hcnt, t3.hacker_id, t3.name FROM t5 join t3 on t5.submission_date = t3.submission_date

    ;

  • praval8765419559
     + 0 comments

    Explanation: Now, the code below might seem daunting that's why it took me more than a day to figure out with a little help from various advance chat bots; more often than giving wrong response. The real challenge was to figure out the number of coders who have submitted at leat one submission from the start date to the current date, which is not mentioned clearly in the question itself, without the use of CTEs and Window Functions since my MySQL version was not allowing it Being from Statistics background and having has studied Probability theory, I can see that the question is ambiguous in its statement about the requirement for the first column.

    MySQL Code:

    SELECT
    dates.submission_date,
    IFNULL(consistent_counts.hacker_count, 0) AS distinct_hacker_count,
    top_hacker.hacker_id,
    top_hacker.name
FROM
    (SELECT DISTINCT submission_date FROM submissions) AS dates
LEFT JOIN
    (
        SELECT
            s1.submission_date,
            COUNT(s1.hacker_id) AS hacker_count
        FROM
            (SELECT DISTINCT submission_date, hacker_id FROM submissions) AS s1
        WHERE
            (SELECT COUNT(DISTINCT s2.submission_date)
             FROM submissions s2
             WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date <= s1.submission_date
            ) = DATEDIFF(s1.submission_date, '2016-03-01') + 1
        GROUP BY
            s1.submission_date
    ) AS consistent_counts ON dates.submission_date = consistent_counts.submission_date
LEFT JOIN
    (
        SELECT
            daily_counts.submission_date,
            MIN(daily_counts.hacker_id) AS hacker_id,
            (SELECT name FROM hackers WHERE hacker_id = MIN(daily_counts.hacker_id)) AS name
        FROM
            (
                SELECT submission_date, hacker_id, COUNT(*) AS subs_count
                FROM submissions
                GROUP BY submission_date, hacker_id
            ) AS daily_counts
        JOIN
            (
                SELECT submission_date, MAX(subs_count) AS max_subs
                FROM (
                    SELECT submission_date, hacker_id, COUNT(*) AS subs_count
                    FROM submissions
                    GROUP BY submission_date, hacker_id
                ) AS inner_counts
                GROUP BY submission_date
            ) AS max_counts ON daily_counts.submission_date = max_counts.submission_date
                            AND daily_counts.subs_count = max_counts.max_subs
        GROUP BY
            daily_counts.submission_date
    ) AS top_hacker ON dates.submission_date = top_hacker.submission_date
ORDER BY
    dates.submission_date;

  • nandun5
     + 0 comments

    Took a long time after missing a point in the question. doesn't seem like there's any way to do this without a temp table, or CTE 

    DECLARE @st DATE

SELECT @st = CAST(MIN(submission_date) as DATE) FROM Submissions

SELECT DISTINCT CAST(submission_date as DATE) submission_date
INTO #ContestDates
FROM Submissions
ORDER BY submission_date

--select @st, submission_date from #ContestDates

;WITH HackerSubmissions AS (
    SELECT cd.submission_date by_date, s.submission_date, hacker_id, count(*) sub_count
    FROM #ContestDates cd 
    INNER JOIN Submissions s
    ON s.submission_date >= @st
    AND s.submission_date <= cd.submission_date
    GROUP BY cd.submission_date, s.submission_date, hacker_id
),
DailyHackers AS (
    SELECT by_date, hacker_id, count(sub_count) sub_count
    FROM HackerSubmissions
    GROUP BY by_date, hacker_id
    HAVING count(sub_count) >= DATEDIFF(dd, @st, by_date) + 1
    --ORDER BY by_date, hacker_id
),
SubmissionCounts AS(
    SELECT by_date, count(distinct hacker_id) hacker_count
    FROM DailyHackers dh
    GROUP BY by_date
), 
HackerSubmissionCounts AS (
    SELECT s.submission_date, hacker_id, count(*) sub_count
    FROM Submissions s
    GROUP BY s.submission_date, hacker_id
),
DailyMaxSubmissions AS (
    SELECT submission_date, max(sub_count) sub_count
    FROM HackerSubmissionCounts
    GROUP BY submission_date
),
DailyMaxHackers AS (
    SELECT hsc.submission_date, min(hacker_id) hacker_id
    FROM HackerSubmissionCounts hsc
    INNER JOIN DailyMaxSubmissions dms
    ON hsc.submission_date = dms.submission_date
    AND hsc.sub_count = dms.sub_count
    GROUP BY hsc.submission_date
)
SELECT cd.submission_date, sc.hacker_count, h.hacker_id, h.name
FROM #ContestDates cd
INNER JOIN SubmissionCounts sc
ON cd.submission_date = sc.by_date
INNER JOIN DailyMaxHackers dmh
ON dmh.submission_date = cd.submission_date
INNER JOIN Hackers h
ON dmh.hacker_id = h.hacker_id
ORDER BY cd.submission_date

  • cutber
     + 0 comments

    Hi, where's my problem?

    select fecha, trabajos, numero, a.name from ( select fecha, max(trabajos) trabajos, min(numero) numero from ( select a.submission_date fecha, count(a.submission_date) trabajos, a.hacker_id numero from ( SELECT hacker_id id, COUNT(DISTINCT submission_date) AS total FROM Submissions GROUP BY hacker_id HAVING COUNT(DISTINCT submission_date) = (SELECT COUNT(DISTINCT submission_date) FROM Submissions) ) temp inner join Submissions a on a.hacker_id = temp.id where temp.total = (select count(distinct submission_date) from Submissions) group by a.hacker_id, a.submission_date ) temp2 group by fecha ) temp3 inner join Hackers a on a.hacker_id = temp3.numero order by fecha;

  • digvijay_allp
     + 0 comments

    with daily_count as ( select s.submission_date,s.hacker_id,h.name,count(s.submission_id) as dail_cna from submissions s left join hackers h on s.hacker_id=h.hacker_id group by s.submission_date,s.hacker_id,h.name ), max_daily as ( select submission_date,hacker_id,name, row_number() over(partition by submission_date order by dail_cna desc,hacker_id) as rna from daily_count ), fin as ( select hacker_id,submission_date, dense_rank() over(partition by hacker_id order by submission_date ) as sina from submissions group by hacker_id,submission_date ), fina1 as ( select submission_date,count(hacker_id) as bbc from fin where day(submission_date)=sina group by submission_date ) select distinct m.submission_date,f.bbc,m.hacker_id,m.name from max_daily m left join fina1 f on m.submission_date=f.submission_date where rna=1;