15 Days of Learning SQL

Sort by

recency

|

1193 Discussions

|

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

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

  • + 1 comment

    CAN ANYONE HELP WHY THIS DOESN'T WOTK

    with cte as (select submission_date,count(distinct hacker_id)as cnt from Submissions group by submission_date) , cte2 as (select submission_date , hacker_id , count()as sub_cnt , rank() over(partition by submission_date order by count()desc ,hacker_id asc)as rnk from Submissions group by hacker_id,submission_date) select cte.submission_date , cte.cnt , cte2.hacker_id,h.name from cte2 join Hackers h on h.hacker_id=cte2.hacker_id join cte on cte2.submission_date=cte.submission_date

    can anyone

    where cte2.rnk=1 order by submission_date;