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
  • Prepare
    NEW
  • Certify
  • Compete
  • Career Fair
  • 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 700 Discussions, By:

recency

Please Login in order to post a comment

  • minimax271828182
    1 week 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
  • sharonhui
    2 weeks ago+ 0 comments

    MySQL

    SELECT 
        b.submission_date, COALESCE(a.contSub,0), b.id, c.name
    FROM (
        SELECT o.submission_date, MIN(o.hacker_id) AS id
        FROM (
            SELECT n.submission_date, n.hacker_id,COUNT(n.submission_id)
            FROM Submissions n
            GROUP BY submission_date, hacker_id
            HAVING COUNT(submission_id)>= ALL(
                  SELECT COUNT(m.submission_id)
                  FROM Submissions m
                  GROUP BY m.submission_date, m.hacker_id
                  HAVING m.submission_date=n.submission_date
              )
        ) AS o
        GROUP BY submission_date
        ) AS b
    LEFT JOIN (
        SELECT aa.submission_date, COUNT(DISTINCT aa.hacker_id) AS contSub
        FROM Submissions AS aa
        WHERE aa.submission_date='2016-03-01' 
        OR submission_date<ALL(
            SELECT bb.submission_date
            FROM (
                SELECT tdy.hacker_id, tdy.submission_date, ytd.submission_date AS SubmittedYtd
                FROM Submissions tdy LEFT JOIN Submissions ytd
                ON DATE_ADD(tdy.submission_date, INTERVAL -1 DAY)= ytd.submission_date 
                AND ytd.hacker_id=tdy.hacker_id    
            ) AS bb
            WHERE aa.hacker_id=bb.hacker_id AND bb.submission_date<> STR_TO_DATE('2016-03-01', '%Y-%m-%d') AND bb.SubmittedYtd IS NULL
        )
        GROUP BY aa.submission_date
    ) AS a 
    ON a.submission_date=b.submission_date
    JOIN Hackers c ON b.id=c.hacker_id
    ORDER BY a.submission_date ASC
    
    0|
    Permalink
  • er_sumitsingh1
    2 weeks ago+ 0 comments

    /* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. / / Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. / with t1 as( SELECT s.submission_date,s.hacker_id,h.name, COUNT(submission_id) as cnt from submissions s inner join hackers h on h.hacker_id=s.hacker_id group by s.hacker_id,h.name,s.submission_date ), t2 as( select submission_date, hacker_id, row_number() over(partition by hacker_id order by submission_date) rn, dense_rank() over(order by submission_date) dr from t1 ), t3 as( select t2.submission_date,count() as cnt1 from t2 where t2.rn=t2.dr group by t2.submission_date ), t4 as( Select submission_date,hacker_id,name from (select submission_date,hacker_id,name, row_number() over(partition by submission_date order by cnt desc,hacker_id) rn from t1)as t where t.rn=1 ) select t3.submission_date, t3.cnt1,t4.hacker_id,t4.name from t3 inner join t4 on t3.submission_date=t4.submission_date order by t3.submission_date ![](![](https://)https://)

    0|
    Permalink
  • canyang0102
    3 weeks ago+ 0 comments

    I love these kinds of questions that practice your logical thinking. You have to find out a way to filter out the rows you need. Once you have a practical idea, the rest is easy because no complex techques are required.

    My code in MS SQL:

    WITH
    -- count each hacker's number of submissions
    a AS
    (
        SELECT DISTINCT s.hacker_id hid, 
                        s.submission_date sdt,
                        COUNT(s.submission_id) csid
        FROM Submissions s
        GROUP BY s.submission_date, s.hacker_id
    ),
    
    -- find out hackers who submitted the most by date and select the one with smallest id
    b AS
    (
        SELECT sdt, MIN(hid) mhid
        FROM a x
        WHERE csid = (SELECT MAX(csid) FROM a y WHERE x.sdt = y.sdt)
        GROUP BY sdt
    ),
    
    -- number each of the 15 days by order
    c AS
    (
        SELECT sdt, ROW_NUMBER()OVER(ORDER BY sdt) rn
        FROM b
    ),
    
    -- filter out those who started submitting since the first day
    e AS
    (
        SELECT hid, sdt, rn FROM
        (SELECT hid, a.sdt, rn, MIN(a.sdt)OVER(PARTITION BY hid) mm
         FROM a JOIN c ON a.sdt = c.sdt
         ) AS d
        WHERE mm = "2016-03-01"
    ),
    
    -- renumber each row by hacker id and date - for each person, when the row number equal to the day number, it is a consecutive submission.
    
    g AS
    (
    SELECT * FROM
    (SELECT *,
           CASE WHEN rn = nrn THEN "COOL"
                ELSE "TRASH"
           END AS tag
    FROM(
        SELECT *,
               ROW_NUMBER()OVER(PARTITION BY hid ORDER BY hid, sdt) nrn
        FROM e) AS f) AS ff
    WHERE tag = "COOL"
    ),
    
    -- count number of hackers of each day after filtering out COOL rows
    i AS
    (
    SELECT sdt, COUNT(hid) cc
    FROM g
    GROUP BY sdt
    )
    
    -- combine table b and table i
    SELECT i.sdt, i.cc, b.mhid, h.name
    FROM i JOIN b ON b.sdt = i.sdt
           JOIN Hackers h ON h.hacker_id = b.mhid
    ORDER BY i.sdt
    
    -1|
    Permalink
  • imranansari9836
    3 weeks ago+ 0 comments

    SELECT SUBMISSION_DATE, (SELECT COUNT(DISTINCT HACKER_ID)
    FROM SUBMISSIONS S2
    WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE AND
    (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) FROM SUBMISSIONS S3 WHERE S3.HACKER_ID = S2.HACKER_ID AND S3.SUBMISSION_DATE < S1.SUBMISSION_DATE) = DATEDIFF(S1.SUBMISSION_DATE , '2016-03-01')), (SELECT HACKER_ID FROM SUBMISSIONS S2 WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS TMP, (SELECT NAME FROM HACKERS WHERE HACKER_ID = TMP) FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1 GROUP BY SUBMISSION_DATE;

    0|
    Permalink
Load more conversations

Need Help?


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