You are viewing a single comment's thread. Return to all comments →
Another solution in oracle more intuitive but more verbose
with R(submission_date, hacker_id) as ( select distinct submission_date, hacker_id from Submissions where submission_date = to_date('2016-03-01') union all select child.submission_date, child.hacker_id from R parent, Submissions child where parent.submission_date + 1 = child.submission_date and parent.hacker_id = child.hacker_id ), Total as (select submission_date, count(distinct hacker_id) as total from R group by submission_date), Counter as (select submission_date, hacker_id, count(hacker_id) as n from Submissions group by submission_date, hacker_id), MaxPerDay as (select C.submission_date, min(C.hacker_id) as hacker_id from Counter C where C.n = (select max(K.n) from Counter K where C.submission_date = K.submission_date) group by submission_date) select Total.submission_date, Total.total, Hackers.hacker_id, Hackers.name from Total join MaxPerDay on Total.submission_date = MaxPerDay.submission_date join Hackers on MaxPerDay.hacker_id = Hackers.hacker_id order by Total.submission_date;
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →
Another solution in oracle more intuitive but more verbose