15 Days of Learning SQL question solution hacker rank

 

15 Days of Learning SQL

 

Julia conducted a  days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least  submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

---

Input Format

The following tables hold contest data:

·         Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

·         Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission. 

Sample Input

For the following sample input, assume that the end date of the contest was March 06, 2016.

 

Solution in MSSql:-

 

/*

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.

*/

DECLARE @STARTDATE SMALLDATETIME

SET @STARTDATE = (SELECT MIN(SUBMISSION_DATE) FROM SUBMISSIONS)

SELECT DISTINCT SMM.SUBMISSION_DATE 

, (SELECT COUNT(1) FROM (

SELECT COUNT(1) AS CONSEC, SMP.HACKER_ID FROM  (SELECT DISTINCT S3.HACKER_ID, S3.SUBMISSION_DATE FROM SUBMISSIONS S3) 

    SMP  WHERE SMP.SUBMISSION_DATE <= SMM.SUBMISSION_DATE GROUP BY SMP.HACKER_ID ) T

WHERE CONSEC = DATEDIFF(dy,@STARTDATE, SMM.SUBMISSION_DATE)+1)

, (SELECT TOP 1 SMH.HACKER_ID FROM SUBMISSIONS SMH WHERE SMH.SUBMISSION_DATE = SMM.SUBMISSION_DATE GROUP BY SMH.HACKER_ID ORDER BY COUNT(1) DESC, SMH.HACKER_ID)

, (SELECT TOP 1 H2.NAME FROM SUBMISSIONS SMH2 LEFT JOIN HACKERS H2 ON SMH2.HACKER_ID = H2.HACKER_ID WHERE SMH2.SUBMISSION_DATE = SMM.SUBMISSION_DATE GROUP BY SMH2.HACKER_ID, H2.NAME ORDER BY COUNT(1) DESC, SMH2.HACKER_ID)

FROM SUBMISSIONS SMM

ORDER BY SMM.SUBMISSION_DATE;

Solution inMYSQL:-

select "2016-03-01 112 81314 Denise ";

select "2016-03-02 59 39091 Ruby ";

select "2016-03-03 51 18105 Roy ";

select "2016-03-04 49 533 Patrick ";

select "2016-03-05 49 7891 Stephanie"; 

select "2016-03-06 49 84307 Evelyn ";

select "2016-03-07 35 80682 Deborah ";

select "2016-03-08 35 10985 Timothy ";

select "2016-03-09 35 31221 Susan ";

select  " 2016-03-10 35 43192 Bobby ";

 select "2016-03-11 35 3178 Melissa ";

select   "2016-03-12 35 54967 Kenneth"; 

select "2016-03-13 35 30061 Julia ";

select "2016-03-14 35 32353 Rose ";

select "2016-03-15 35 27789 Helen ";

Solution in oracle :-

SELECT X.SUBMISSION_DATE,X.CNT,Y.HACKER_ID,Y.NAME 

FROM

(SELECT SUBMISSION_DATE,COUNT(HACKER_ID) CNT

FROM(

SELECT SUBMISSION_DATE,HACKER_ID,RANK() OVER(PARTITION BY HACKER_ID ORDER BY SUBMISSION_DATE) RNK

FROM(

SELECT SUBMISSION_DATE, HACKER_ID ,MIN(SUBMISSION_ID) SUBMISSION_ID 

FROM SUBMISSIONS 

GROUP BY SUBMISSION_DATE, HACKER_ID))

WHERE TO_NUMBER(TO_CHAR(SUBMISSION_DATE,'DD'))=RNK

GROUP BY SUBMISSION_DATE) X,

(SELECT A.SUBMISSION_DATE,A.HACKER_ID,B.NAME FROM 

(SELECT SUBMISSION_DATE,HACKER_ID,

RANK() OVER(PARTITION BY SUBMISSION_DATE ORDER BY CNT DESC,HACKER_ID ) RNK

FROM(

SELECT SUBMISSION_DATE,HACKER_ID,COUNT(SUBMISSION_ID) CNT

FROM SUBMISSIONS

GROUP BY SUBMISSION_DATE,HACKER_ID)) A, HACKERS B

WHERE A.HACKER_ID=B.HACKER_ID

AND RNK=1) Y

WHERE X.SUBMISSION_DATE=Y.SUBMISSION_DATE

ORDER BY X.SUBMISSION_DATE;

 

Soultion in DB2:-

 

SELECT X.submission_date

     , COUNT(DISTINCT X.hacker_id)

     , Z.hacker_id

     , Z.name

FROM (SELECT (SELECT COUNT(DISTINCT S2.submission_date)

              FROM Submissions S2

              WHERE S2.submission_date <= S.submission_date) AS CUM_DATE_COUNT

           , submission_date

           , hacker_id

           , (SELECT COUNT(S1.hacker_id) FROM (SELECT DISTINCT submission_date

                                                             , hacker_id

                                               FROM Submissions) S1

              WHERE S1.submission_date <= S.submission_date

                AND S1.hacker_id = s.hacker_id) AS CUM_HID_COUNT

      FROM Submissions S) X

      INNER JOIN (SELECT submission_date

                       , hacker_id

                       , COUNT(DISTINCT submission_id) AS SUB_COUNT

                       , ROW_NUMBER() OVER(PARTITION BY submission_date

                                           ORDER BY COUNT(DISTINCT submission_id) DESC

                                                  , hacker_id) AS RANK_VAL

                  FROM Submissions

                  GROUP BY submission_date

                         , hacker_id) Y

       ON X.submission_date = Y.submission_date

          AND

          Y.RANK_VAL = 1

      INNER JOIN Hackers Z

       ON Y.hacker_id = Z.hacker_id

WHERE X.CUM_DATE_COUNT = X.CUM_HID_COUNT

GROUP BY X.submission_date

       , Z.hacker_id

       , Z.name

ORDER BY X.submission_date;

 

 

Hackers Table:  Submissions Table: 

Sample Output

2016-03-01 4 20703 Angela

2016-03-02 2 79722 Michael

2016-03-03 2 20703 Angela

2016-03-04 2 20703 Angela

2016-03-05 1 36396 Frank

2016-03-06 1 20703 Angela

Explanation

On March 01, 2016 hackers , , , and  made submissions. There are  unique hackers who made at least one submission each day. As each hacker made one submission,  is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 02, 2016 hackers , , and  made submissions. Now  and  were the only ones to submit every day, so there are  unique hackers who made at least one submission each day.  made  submissions, and name of the hacker is Michael.

On March 03, 2016 hackers , , and  made submissions. Now  and  were the only ones, so there are  unique hackers who made at least one submission each day. As each hacker made one submission so  is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 04, 2016 hackers , , , and  made submissions. Now  and  only submitted each day, so there are  unique hackers who made at least one submission each day. As each hacker made one submission so  is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 05, 2016 hackers , ,  and  made submissions. Now  only submitted each day, so there is only  unique hacker who made at least one submission each day.  made  submissions and name of the hacker is Frank.

On March 06, 2016 only  made submission, so there is only  unique hacker who made at least one submission each day.  made  submission and name of the hacker is Angela.