SQL Contest – 14 days hackathon

Started on 1^st July, 2019 – 2 weeks long hackathon was conducted, which concluded on 14^th 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.

---

 

---

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
;