Q2_h

 avatar
kuber
plain_text
2 years ago
1.8 kB
3
Indexable
Never
select s1.submission_date, /* for each submission date in s1 */
        /* get the number of distinct hackers making submission till that day from s2 - s2 has the              record of all hackers who have submitted till that day since the start date */
        (select count(distinct s2.hacker_id)
            from submissions s2  
             /* to get the table s2, counting the number of distinct submission dates a hacker has                    made submission on, from start date to each s1 submission date which must be                      equal to the date diff between start date and s1 submission date for a hacker                      to be added to s2. As equality means, they have made submission on each day                        since start date to that day */
            where s2.submission_date = s1.submission_date and    
                (select count(distinct s3.submission_date)
                from submissions s3 where s3.hacker_id = s2.hacker_id and s3.submission_date <                      s1.submission_date) = datediff(s1.submission_date , '2016-03-01')),
        /* getting the hacker ids and their number of submissions in descending order for each                  submission date s1 from s2, then extracting the topmost hacker with the most                    submissions and lower hacker_id */
        (select s2.hacker_id from submissions s2 where s2.submission_date = s1.submission_date
        group by s2.hacker_id order by count(s2.submission_id) desc, s2.hacker_id limit 1)                  max_hacker,
        /* getting the name of hacker_id chosen in the previous selection*/
        (select name from hackers where hacker_id = max_hacker)
        /* getting distinct submission dates as s1*/
from (select distinct submission_date from submissions) s1;