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 :
- Contest Date
- 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.
- participant_id who made maximum number of submissions each day. In case of tie choose lowest participant_id
- 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 ;
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.