Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
1.4 kB
1
Indexable
Never
select label2.submission_date, label2.count, label5.hacker, label6.name
from (select submission_date, count(distinct hacker_id) count
    from (select s1.submission_date, group_concat(distinct s2.submission_date order by s2.submission_date separator ' ') dates
        from Submissions s1 join Submissions s2 on s2.submission_date <= s1.submission_date
        group by s1.submission_date) label1
    left join (select hacker_id, group_concat(distinct submission_date order by submission_date separator ' ') dates
        from Submissions
        group by hacker_id) label2
    on label2.dates like concat('%', label1.dates, '%')
    group by submission_date) label2
join (select label3.submission_date, min(label3.hacker_id) hacker
    from (select submission_date, hacker_id, count(*) count
        from Submissions 
        group by submission_date, hacker_id) label3
    left join (select submission_date, hacker_id, count(*) count
        from Submissions 
        group by submission_date, hacker_id) label4
    on label3.submission_date = label4.submission_date && label3.hacker_id != label4.hacker_id && label3.count < label4.count
    where label4.count is null
    group by label3.submission_date) label5
on label2.submission_date = label5.submission_date
join Hackers label6 on label6.hacker_id = label5.hacker
order by submission_date