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.
  • HackerRank Home

    HackerRank

  • |
  • Prepare
  • Certify
  • Compete
  • Apply
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Advanced Join
  4. 15 Days of Learning SQL
  5. Discussions

15 Days of Learning SQL

Problem
Submissions
Leaderboard
Discussions

Sort 835 Discussions, By:

recency

Please Login in order to post a comment

  • kpieta89
    1 day ago+ 0 comments

    MS SQL Server

    WITH 
    cte1 AS (
            SELECT DISTINCT submission_date
            FROM submissions),
    cte2 AS (
            SELECT submission_date,
                    ROW_NUMBER() OVER(ORDER BY submission_date) AS rank_date
            FROM cte1),
    cte3 AS (
            SELECT DISTINCT submission_date,
                    hacker_id
            FROM submissions),
    cte4 AS (
            SELECT DISTINCT hacker_id,
                    submission_date,
            ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS rank_hacker
            FROM cte3),
    cte5 AS (
            SELECT DISTINCT cte2.submission_date,
            COUNT(cte4.hacker_id) OVER(PARTITION BY cte4.submission_date) AS cnt_id
            FROM cte2
            JOIN cte4 ON cte2.submission_date = cte4.submission_date
            WHERE rank_date = rank_hacker),
    cte6 AS (
            SELECT submission_date, 
                    hacker_id, 
                    COUNT(submission_id) AS cnt_sub
            FROM submissions
            GROUP BY submission_date,
                     hacker_id),
    cte7 AS (
            SELECT submission_date, 
                   hacker_id, 
                   ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY cnt_sub DESC, hacker_id) AS rank_hacker_daily
            FROM cte6)
    SELECT cte5.submission_date,
           cte5.cnt_id,
           cte7.hacker_id,
           h.name
    FROM cte5
    JOIN cte7 ON cte5.submission_date = cte7.submission_date
    JOIN hackers h ON cte7.hacker_id = h.hacker_id
    WHERE cte7.rank_hacker_daily = 1;
    
    0|
    Permalink
  • suruchiarora257
    1 day ago+ 0 comments

    What's the issue in this Code ?

    It's giving run time Error

    with cte as (SELECT Submission_Date,hacker_id,count(hacker_id) as cnt,
    ROW_NUMBER() OVER (PARTITION BY Submission_Date ORDER BY cnt desc, hacker_id asc) AS row_num
    FROM Submissions
    GROUP by Submission_Date,hacker_id),
    
    t1 as (SELECT Submission_Date,count(DISTINCT(hacker_id))uniq FROM `Submissions` group BY Submission_Date),
    
    t2 as(select cte.Submission_Date,h.hacker_id,h.Name  from cte join Hackers h on h.hacker_id=cte.hacker_id where cte.row_num=1)
    
    select t1.Submission_Date,t1.uniq,t2.hacker_id,t2.Name from t1 join t2 on t1.Submission_Date=t2.Submission_Date;
    
    -1|
    Permalink
  • yogeshnayak081
    5 days ago+ 0 comments

    **MS SQL **

    with first_output as
    (Select submission_date,x.hacker_id,name from (Select submission_date,hacker_id,count(submission_id) cou,
    dense_rank() over(partition by submission_date order by count(submission_id) desc , hacker_id) r  from submissions
    group by submission_date,hacker_id) x join hackers h
    on h.hacker_id = x.hacker_id
    where r = 1  ),
    
    cte as (Select distinct submission_date,hacker_id,dense_rank() over(order by submission_date) as rs from submissions),
    
    second_output as(Select c1.submission_date,count(hacker_id) users from cte c1
    where hacker_id in
    (Select c2.hacker_id from cte c2
    where c2.submission_Date  < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end
     group by c2.hacker_id
    having count(hacker_id) = 
    (select max(rs) from cte c3 
     where c3.submission_date < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end))
     group by c1.submission_date
    )
    
    
    Select fo.submission_date,users,hacker_id,name from first_output fo,second_output so
    where fo.submission_date = so.submission_Date
    order by fo.submission_date
    
    0|
    Permalink
  • srikrishnavanam1
    7 days ago+ 0 comments

    platform is broken , 1) MYSQL - unable to recognize even row_number() over (partition by ... order by ...) 2) I wasted an hour trying to see what is wrong , i tried copy pasting the top submission which got 50 points .. even that threw the same error .

    what a useless website.

    0|
    Permalink
  • utsavvatsal
    7 days ago+ 0 comments

    --This worked fine for me -- with date_hacker as (select submission_date, hacker_id, count() as no_submissions from submissions s group by submission_date, hacker_id ), top_hacker as ( select a.submission_date, a.hacker_id, h.name from (select submission_date, hacker_id, rank() over (partition by submission_date order by no_submissions desc, hacker_id asc ) as rr from date_hacker) a inner join hackers h on a.hacker_id=h.hacker_id where a.rr=1 ), denserank as (select distinct submission_date, dense_rank() over (order by submission_date asc) as denserank from submissions), raw_d as ( select d1.submission_date,dr.denserank, d1.hacker_id from date_hacker d1 inner join date_hacker d2 on d1.submission_date>=d2.submission_date and d1.hacker_id=d2.hacker_id inner join denserank dr on d1.submission_date=dr.submission_date group by d1.submission_date,dr.denserank, d1.hacker_id having dr.denserank=count() ) select t.submission_date, coalesce(r.unique_hackers,0), t.hacker_id, t.name from top_hacker t left outer join (select submission_date, count(*) as unique_hackers from raw_d group by submission_date) r on t.submission_date=r.submission_date order by t.submission_date asc

    0|
    Permalink
Load more conversations

Need Help?


View top submissions
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy