SQL Contest – 14 days hackathon

Started on 1st July, 2019 – 2 weeks long hackathon was conducted, which concluded on 14th July, 2019.

Data was collected for each participant and you are expected to write a query to print four columns as below :

  1. Contest Date
  2. Total number of unique participants who made at least 1 submission every day ( starting from first day of contest) . Select 0 in case none is found.
  3. participant_id who made maximum number of submissions each day. In case of tie choose lowest participant_id
  4. participant_name corresponding to participant_id selected above.

Output should be sorted by date, and should print information for each day of contest.


 

Input Table – Participants

participant_id participant_name
1234 Jack
2345 Scarlett
3456 Tom
4567 Kate
5678 John
6789 Morgan
7890 Kevin
8901 Ian

Input Table – Submissions

submission_date submission_id participant_id
2019-07-01 2915 2345
2019-07-01 23657 6789
2019-07-01 24578 8901
2019-07-01 25679 3456
2019-07-02 26345 2345
2019-07-02 27654 1234
2019-07-02 28125 8901
2019-07-02 29165 8901
2019-07-03 30184 2345
2019-07-03 31634 3456
2019-07-03 32470 8901
2019-07-04 33459 2345
2019-07-04 34128 5678
2019-07-04 35173 6789
2019-07-04 36585 8901
2019-07-05 37765 2345
2019-07-05 38284 4567
2019-07-05 39334 7890
2019-07-05 42856 3456
2019-07-05 43812 3456
2019-07-06 44781 2345

 


Output:

Submission Date Consistent Participants Count Max Submissions by participant_id Participant Name
2019-07-01 4 2345 Scarlett
2019-07-02 2 8901 Ian
2019-07-03 2 2345 Scarlett
2019-07-04 2 2345 Scarlett
2019-07-05 1 3456 Tom
2019-07-06 1 2345 Scarlett

Explanation:

On July 01, 2019 participants 2345, 3456, 6789 and 8901 made submissions. There are 4 unique participants who made at least one submission each day. As each participant made one submission, 2345 is considered to be the participant who made maximum number of submissions on this day as its participant_id is lowest amontst all. The name of the participant is Scarlett.

On July 02, 2019 participants 2345, 1234, and 8901 made submissions. Now 2345 and 8901 were the only ones to submit every day, so there are 2 unique participants who made at least one submission each day. 8901 made 2 submissions, and name of the participant is Ian.

On July 03, 2019 participants 2345, 3456, and 8901 made submissions. Now 2345 and 8901 were the only ones, so there are 2 unique participants who made at least one submission each day. As each participant made one submission so 2345 is considered to be the participant who made maximum number of submissions on this day. The name of the participant is Scarlett.

On July 04, 2016 participants 2345, 5678, 6789, and 8901 made submissions. Now 2345 and 8901 only submitted each day, so there are 2 unique participants who made at least one submission each day. As each participant made one submission so 2345 is considered to be the participant who made maximum number of submissions on this day. The name of the participant is Scarlett.

On July 05, 2016 participants 2345, 4567, 7890 and 3456 made submissions. Now 2345 only submitted each day, so there is only 1 unique participant who made at least one submission each day. 3456 made 2 submissions and name of the participant is Tom.

On July 06, 2016 only 2345 made submission, so there is only 1 unique participant who made at least one submission each day. 2345 made 1 submission and name of the participant is Scarlett.


DDLs:

CREATE TABLE participants ( participant_id INT , participant_name VARCHAR(20));
INSERT INTO participants VALUES (1234, "Jack");
INSERT INTO participants VALUES (2345, "Scarlett");
INSERT INTO participants VALUES (3456, "Tom");
INSERT INTO participants VALUES (4567, "Kate");
INSERT INTO participants VALUES (5678, "John");
INSERT INTO participants VALUES (6789, "Morgan");
INSERT INTO participants VALUES (7890, "Kevin");
INSERT INTO participants VALUES (8901, "Ian");


CREATE TABLE submissions ( submission_date VARCHAR(20), submission_id INT , participant_id INT);
INSERT INTO submissions VALUES("2019-07-01",25679,2345);
INSERT INTO submissions VALUES("2019-07-01",23657,6789);
INSERT INTO submissions VALUES("2019-07-01",24578,8901);
INSERT INTO submissions VALUES("2019-07-01",25679,3456);
INSERT INTO submissions VALUES("2019-07-02",26345,2345);
INSERT INTO submissions VALUES("2019-07-02",27654,1234);
INSERT INTO submissions VALUES("2019-07-02",28125,8901);
INSERT INTO submissions VALUES("2019-07-02",29165,8901);
INSERT INTO submissions VALUES("2019-07-03",30184,2345);
INSERT INTO submissions VALUES("2019-07-03",31634,3456);
INSERT INTO submissions VALUES("2019-07-03",32470,8901);
INSERT INTO submissions VALUES("2019-07-04",33459,2345);
INSERT INTO submissions VALUES("2019-07-04",34128,5678);
INSERT INTO submissions VALUES("2019-07-04",35173,6789);
INSERT INTO submissions VALUES("2019-07-04",36585,8901);
INSERT INTO submissions VALUES("2019-07-05",37765,2345);
INSERT INTO submissions VALUES("2019-07-05",38284,4567);
INSERT INTO submissions VALUES("2019-07-05",39334,7890);
INSERT INTO submissions VALUES("2019-07-05",42856,3456);
INSERT INTO submissions VALUES("2019-07-05",43812,3456);
INSERT INTO submissions VALUES("2019-07-06",44781,2345);

Solution:

SELECT 
A.submission_date    AS "Submission Date", 
B.participant_counts AS "Consistent Participants Count" ,
A.participant_id     AS "Max Submissions by participant_id" ,  
C.participant_name   AS "Participant Name"
FROM
(
SELECT
submission_date,
participant_id,
DENSE_RANK() OVER (PARTITION BY submission_date ORDER BY COUNT(*) DESC , participant_id ASC ) AS rnk
FROM
submissions
GROUP BY submission_date,participant_id
) A
JOIN
( SELECT TMP.submission_date , COUNT(DISTINCT TMP.participant_id) AS participant_counts 
   FROM 
      ( SELECT 
         participant_id,
         submission_date, 
         ROW_NUMBER() OVER (PARTITION BY participant_id ORDER BY submission_date) numbering
        FROM submissions
        GROUP BY 
        participant_id, submission_date
      ) TMP
  WHERE 
  TMP.numbering = DATEDIFF(STR_TO_DATE(TMP.submission_date,'%Y-%m-%d') , STR_TO_DATE("2019-07-01",'%Y-%m-%d') ) + 1
  GROUP BY TMP.submission_date
) B
ON A.submission_date = B.submission_date
JOIN
participants C
ON A.participant_id = C.participant_id
WHERE A.rnk=1
ORDER BY A.submission_date
;

1 thought on “SQL Contest – 14 days hackathon”

  1. I have been browsing online more than 2 hours today, yet I never found any interesting article like yours.
    It’s pretty worth enough for me. Personally, if all website
    owners and bloggers made good content as you
    did, the net will be a lot more useful than ever before.

    Reply

Leave a Comment