Question 2 (15 Days of Learning SQL)

 avatar
user_6227789376
mysql
2 years ago
728 B
1
Indexable
Never
Hard:

Question 2 (15 Days of Learning SQL):


SELECT submission_date, (SELECT COUNT(distinct hacker_id) FROM Submissions temp2  
 WHERE temp2.submission_date = temp1.submission_date AND (SELECT COUNT(DISTINCT temp3.submission_date) FROM Submissions temp3 WHERE temp3.hacker_id = temp2.hacker_id 
 AND temp3.submission_date < temp1.submission_date) = dateDIFF(temp1.submission_date , '2016-03-01')),
 
(SELECT hacker_id FROM submissions temp2 WHERE temp2.submission_date = temp1.submission_date group by hacker_id order by count(submission_id) DESC , hacker_id limit 1) as temporary,
(SELECT name FROM hackers WHERE hacker_id = temporary) FROM (SELECT DISTINCT submission_date FROM submissions) temp1 

group by submission_date;