Discussion on 15 Days of Learning SQL Challenge

Sort by

recency

|

1080 Discussions

|

11 hours ago+ 0 comments

I can't figure out why my below query (MSSQL) isn't working. I tried to break up each reequirement into smaller steps here using cte's. First finding the number of submissions each hacker made on a given day. Then filtering to only include hacker id's that submitted at least once a day. Finally generating some flags to identify if a hacker submitted an amount on a day equal to the maximum submissions anyone had registereed on that same day. If there is a tie, I use another flag to pull the smallest hackere id in the case of a multiway tie. Any insight would be greatly appreciated please, thank you!

with

    hackerSubmissionCountByDay as (
        select
            submission_date,
            submission_id,
            hacker_id,
            count(*) over (partition by submission_date,hacker_id) as total_submissions_on_day 
        from Submissions
    ),

    hackersWithDailySubs as (

        select 
            hacker_id,
            count(*) as num_unique_days_submitted,
            case
                when count(*) = (select count(distinct(submission_date)) from Submissions) then 'daily hacker'
                else 'non daily hacker'
            end as is_daily_hacker
        from hackerSubmissionCountByDay
        group by hacker_id
    ),
    
    dailyHackerSubmissionCountByDayv2 as (
        select
            hackerSubmissionCountByDay.*,
            hackersWithDailySubs.num_unique_days_submitted,
            hackersWithDailySubs.is_daily_hacker
            
        from hackerSubmissionCountByDay left join hackersWithDailySubs
        on hackerSubmissionCountByDay.hacker_id = hackersWithDailySubs.hacker_id
    ),
    
    dailyHackerNames as (
        select
            hacker_id,
            name
        from Hackers
        where hacker_id in (select hacker_id from hackersWithDailySubs where is_daily_hacker = 'daily hacker')
    ),

    filteredSubmissionCounts as (
        select * from dailyHackerSubmissionCountByDayv2 where is_daily_hacker = 'daily hacker'
    ),

    joined as (
        select
            filteredSubmissionCounts.submission_date,
            filteredSubmissionCounts.submission_id,
            filteredSubmissionCounts.hacker_id,
            filteredSubmissionCounts.total_submissions_on_day,
            dailyHackerNames.name
        from
            filteredSubmissionCounts left join dailyHackerNames on
            filteredSubmissionCounts.hacker_id = dailyHackerNames.hacker_id
    ),

    final as (
        select 
            submission_date,
            total_submissions_on_day,
            hacker_id,
            name,
            max(total_submissions_on_day) over (partition by submission_date,hacker_id) as max_subs,

            case
                when total_submissions_on_day = max(total_submissions_on_day) over (partition by submission_date,hacker_id,name) then 1
                else 0
            end as is_max_subs,

            case
                when hacker_id = min(hacker_id) over (partition by submission_date) then 1
                else 0
            end as is_lowest_hacker_id

        from joined
    )
    
select         
    submission_date,
    total_submissions_on_day,
    hacker_id,
    name 
from final 
where is_max_subs = 1 and is_lowest_hacker_id = 1;

1 week ago+ 0 comments

select sup.*, h.name from Hackers h, ( select s.submission_date, count(distinct s.hacker_id) as counter, (select s4.hacker_id from Submissions s4 where s4.submission_date = s.submission_date group by s4.submission_date, s4.hacker_id order by count(s4.submission_id) desc, s4.hacker_id asc limit 1 ) as hacker from Submissions s where s.hacker_id in ( select s2.hacker_id from Submissions s2 where s2.submission_date <= s.submission_date and datediff(s.submission_date, '2016-03-01')+1 = ( select count(distinct s3.submission_date) from Submissions s3 where s3.hacker_id = s2.hacker_id and s3.submission_date <= s.submission_date group by s3.hacker_id ) group by s2.hacker_id ) group by s.submission_date ) sup where sup.hacker = h.hacker_id order by sup.submission_date asc

2 weeks ago+ 0 comments

WITH HackerDaily AS (
    -- Extract distinct submission_date and hacker_id
    SELECT DISTINCT submission_date, hacker_id
    FROM Submissions
),
RecursiveActiveHackers AS (
    -- Base case - Include all hackers on the first day
    SELECT 
        h1.submission_date,
        h1.hacker_id
    FROM HackerDaily h1
    WHERE h1.submission_date = (SELECT MIN(submission_date) FROM HackerDaily)

    UNION ALL

    -- Recursive case - Retain hackers who are active on all previous days
    SELECT 
        h1.submission_date,
        h1.hacker_id
    FROM HackerDaily h1
    INNER JOIN RecursiveActiveHackers r
        ON h1.hacker_id = r.hacker_id -- Hacker must be active on all previous days
       AND h1.submission_date = DATEADD(DAY, 1, r.submission_date) -- Move to the next day
),
DailyMaxSubmissions AS (
    -- Calculate the hacker with the maximum submissions for each day
    SELECT 
        Submissions.submission_date,
        Submissions.hacker_id,
        Hackers.name,
        COUNT(*) AS submissions,
        RANK() OVER (
            PARTITION BY Submissions.submission_date 
            ORDER BY COUNT(*) DESC, Submissions.hacker_id ASC
        ) AS rnk
    FROM Submissions
    JOIN Hackers ON Submissions.hacker_id = Hackers.hacker_id
    GROUP BY Submissions.submission_date, Submissions.hacker_id, Hackers.name
)
-- Combine results
SELECT 
    r.submission_date,
    COUNT(DISTINCT r.hacker_id) AS active_hackers,
    d.hacker_id AS max_submission_hacker_id,
    d.name AS max_submission_hacker_name
FROM RecursiveActiveHackers r
LEFT JOIN DailyMaxSubmissions d
    ON r.submission_date = d.submission_date AND d.rnk = 1
GROUP BY r.submission_date, d.hacker_id, d.name
ORDER BY r.submission_date;

2 weeks ago+ 0 comments

WITH DateSeries AS ( -- Generate a list of dates from 2016-03-01 to 2016-03-15 SELECT CAST('2016-03-01' AS DATE) AS submission_date UNION ALL SELECT DATEADD(DAY, 1, submission_date) FROM DateSeries WHERE submission_date < '2016-03-15' ), DateRowNumbers AS ( -- Assign a row number to each date SELECT submission_date, ROW_NUMBER() OVER (ORDER BY submission_date) AS day FROM DateSeries ), HackerSubmissions AS ( -- Count the number of submissions per hacker per day SELECT submission_date, hacker_id, COUNT(submission_id) AS count_sub FROM submissions GROUP BY submission_date, hacker_id ), HackerRanks AS ( -- Assign a row number to each hacker to identify the hacker with the most submissions for each day SELECT submission_date, hacker_id, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY count_sub DESC, hacker_id) AS day_champ FROM HackerSubmissions ), DayChampions AS ( -- Select the hacker with the most submissions for each day SELECT ds.submission_date, hr.hacker_id, h.name FROM DateRowNumbers ds LEFT JOIN HackerRanks hr ON ds.submission_date = hr.submission_date LEFT JOIN hackers h ON hr.hacker_id = h.hacker_id WHERE hr.day_champ = 1 ), HackerStreaks AS ( -- Generate a row number for each day in streak for each hacker SELECT submission_date, hacker_id, count_sub, ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS day_row FROM HackerSubmissions ), StreakDetails AS ( -- Join the day numbers with the hacker's submission data SELECT ds.submission_date, hs.hacker_id, hs.count_sub, hs.day_row, ds.day FROM DateRowNumbers ds LEFT JOIN HackerStreaks hs ON ds.submission_date = hs.submission_date WHERE ds.day = hs.day_row ), TotalParticipants AS ( -- Count the number of hackers who participated on each day SELECT submission_date, COUNT(hacker_id) AS total_participants_streak FROM StreakDetails GROUP BY submission_date ) -- Final result: Join the total participants per day with the day champions SELECT tp.submission_date, tp.total_participants_streak, dc.hacker_id, dc.name FROM TotalParticipants tp LEFT JOIN DayChampions dc ON tp.submission_date = dc.submission_date;

2 weeks ago+ 0 comments

--SQL

 
SELECT z.submission_date,
       z.total_count,
       z.hacker_id,
       c.name
FROM
  (SELECT y.*,
          sum(CASE
                  WHEN y.rn = y.sub_date_cnt THEN 1
                  ELSE 0
              END) OVER (PARTITION BY submission_date) AS total_count,
          dense_rank() OVER (PARTITION BY submission_date
                             ORDER BY cnt DESC, hacker_id) AS rnk
   FROM
     (SELECT a.hacker_id,
             a.submission_date,
             x.rn,
             sum(CASE
                     WHEN a.submission_date = b.submission_date THEN 1
                     ELSE 0
                 END) AS cnt ,
             count(DISTINCT b.submission_date) AS sub_date_cnt
      FROM
        (SELECT Submission_date,
                rank() OVER (
                             ORDER BY Submission_date) AS rn
         FROM
           (SELECT DISTINCT submission_date
            FROM Submissions
            WHERE submission_date >= '2016-03-01'
              AND submission_date <= '2016-03-15'))x
      LEFT JOIN Submissions a ON a.Submission_date = x.Submission_date
      LEFT JOIN Submissions b ON a.hacker_id = b.hacker_id
      AND b.Submission_date <= a.submission_date
      GROUP BY a.hacker_id,
               a.submission_date,
               x.rn
      ORDER BY a.submission_date,
               sum(CASE
                       WHEN a.submission_date = b.submission_date THEN 1
                       ELSE 0
                   END) DESC , a.hacker_id) y) z
LEFT JOIN hackers c ON z.hacker_id = c.hacker_id
WHERE z.rnk = 1
ORDER BY submission_date ;

Sort by

|

1080 Discussions

|

Cookie support is required to access HackerRank