We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. 15 Days of Learning SQL
  5. Discussions

15 Days of Learning SQL

  • danaila95andrei
     + 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;