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

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