Hacker Rank SQL

15 Days of Learning SQL Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | 15 Days of Learning SQL Solution


Also Visit:ย  Interviews Solution


 

Problem

Julia conducted aย 15ย 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ย 1ย 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.

  • 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

 

Solution โ€“ 15 Days of Learning SQL

MySQL Code
SELECTย t1.submission_date,ย hkr_cnt,ย t2.hacker_id,ย name
FROMย (SELECTย p1.submission_date,ย 
ย ย ย ย ย ย ย ย ย ย ย ย ย COUNT(DISTINCTย p1.hacker_id)ย ASย hkr_cnt
ย ย ย ย ย ย FROMย (SELECTย submission_date,ย hacker_id,ย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย @h_rnkย :=ย CASEย WHENย @h_grpย !=ย hacker_idย THENย 1ย ELSEย @h_rnk+1ย ENDย ASย hacker_rank,
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย @h_grpย :=ย hacker_idย ASย hacker_group
ย ย ย ย ย ย ย ย ย ย ย ย FROMย (SELECTย DISTINCTย submission_date,ย hacker_idย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย FROMย submissions
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ORDERย BYย hacker_id,ย submission_date)ย ASย a,ย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย (SELECTย @h_rnkย :=ย 1,ย @h_grpย :=ย 0)ย ASย r)ย ASย p1
ย ย ย ย ย ย JOINย (SELECTย submission_date,ย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย @d_rnkย :=ย @d_rnkย +ย 1ย ASย date_rank
ย ย ย ย ย ย ย ย ย ย ย ย FROMย (SELECTย DISTINCTย submission_date
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย FROMย submissionsย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ORDERย BYย submission_date)ย ASย b,ย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย (SELECTย @d_rnkย :=ย 0)ย r)ย ASย p2
ย ย ย ย ย ย ONย p1.submission_dateย =ย p2.submission_dateย 
ย ย ย ย ย ย ย ย ย ANDย hacker_rankย =ย date_rank
ย ย ย ย ย ย GROUPย BYย p1.submission_Date)ย ASย t1
JOINย (SELECTย submission_date,ย hacker_id,ย sub_cnt,
ย ย ย ย ย ย ย ย ย ย ย ย ย @s_rnkย :=ย CASEย WHENย @d_grpย !=ย submission_dateย THENย 1ย ELSEย @s_rnk+1ย ENDย ASย max_rnk,
ย ย ย ย ย ย ย ย ย ย ย ย ย @d_grpย :=ย submission_dateย ASย date_group
ย ย ย ย ย ย FROMย (SELECTย submission_date,ย hacker_id,ย COUNT(*)ย ASย sub_cnt
ย ย ย ย ย ย ย ย ย ย ย ย FROMย submissionsย ASย s
ย ย ย ย ย ย ย ย ย ย ย ย GROUPย BYย submission_date,ย hacker_id
ย ย ย ย ย ย ย ย ย ย ย ย ORDERย BYย submission_date,ย sub_cntย DESC,ย hacker_id)ย ASย c,
ย ย ย ย ย ย ย ย ย ย ย (SELECTย @s_rnkย :=ย 1,ย @d_grpย :=ย 0)ย ASย r)ย ASย t2ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย 
ONย t1.submission_dateย =ย t2.submission_dateย ANDย max_rnkย =ย 1
JOINย hackersย ASย hย ONย h.hacker_idย =ย t2.hacker_idย ย ย ย ย ย ย ย ย ย ย ย 
ORDERย BYย t1.submission_date
;

 

 

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.