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

    You are viewing a single comment's thread. Return to all comments →

  • minimax271828182
    2 months ago+ 0 comments
    with t as (
    select submission_date,
           h.hacker_id,
           name,
           count( submission_id) as br, 
           dense_rank() over (partition by h.hacker_id order by submission_date) as rbr,
           dense_rank() over (partition by submission_date order by count(*) desc,h.hacker_id ) as rk1   
    from hackers h inner join submissions s on h.hacker_id=s.hacker_id
    group by submission_date,h.hacker_id,name
        ),
    tt as (
    select t.*,
           dense_rank() over (partition by submission_date order by br desc,hacker_id ) as rk,
           count( hacker_id) over (partition by submission_date) as broj 
    from t where rbr=datediff(day,'2016-03-01',submission_date)+1
            )
    select t.submission_date,tt.broj,t.hacker_id,t.name from t inner join tt on t.submission_date=tt.submission_date
    where tt.rk=1 and t.rk1=1
    order by 1
    
    0|
    Permalink
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy