15 Days of Learning SQL

  • + 2 comments

    MYSQL, huge pain this problem was, from understanding the question itself, to not being able to use CTE or window functions like row_number(), I still managed to do this with just subqueries. I solved the problem in 2 steps and joined these 2 steps in the end. I can give further explaination of my solution if anyone wants or cares.

    select subq1.submission_date, cnt, subq2.hacker_id, subq2.name
    from
    (select submission_date, count(distinct hacker_id) as cnt
    from
    (select s.submission_date, s.hacker_id from submissions s where s.hacker_id in
    (select hacker_id from submissions where submission_date <= s.submission_date group by hacker_id having count(distinct submission_date) = DATE_FORMAT(s.submission_date, "%e"))) as subq
    group by submission_date) subq1
    join 
    (select s.submission_date, min(s.hacker_id) as hacker_id, h.name
    from submissions s
    join hackers h on s.hacker_id = h.hacker_id
    where s.hacker_id = 
    (select hacker_id from submissions 
     where submission_date = s.submission_date
     group by hacker_id
     order by count(hacker_id) desc, hacker_id
     limit 1)
    group by s.submission_date, h.name) subq2
    on subq1.submission_date = subq2.submission_date;