-- select h.hacker_id,h.name -- from Hackers h

-- select s.submission_date, s.submission_id, s.hacker_id -- from Submissions s

with cte1 as ( select submission_date,count(distinct hacker_id) unique_hacker_ids from ( select submission_date,hacker_id, dense_rank() over(order by submission_date) date_r, dense_rank() over(partition by hacker_id order by submission_date) hacker_r from Submissions )a where date_r=hacker_r group by submission_date ), cte2 as ( select submission_date, hacker_id from ( select submission_date,hacker_id, row_number() over(partition by submission_date order by count(hacker_id) desc, hacker_id) hacker_r from Submissions group by submission_date,hacker_id )a where 1=hacker_r ) select cte1.submission_date,unique_hacker_ids,cte2.hacker_id,h.name from cte1 join cte2 on cte1.submission_date=cte2.submission_date join Hackers h on h.hacker_id=cte2.hacker_id order by submission_date;

wait bro's ! i have send you correct MySQL code for that Que (Say Thank me Later) :

SELECT dates.submission_date, IFNULL(consistent_counts.hacker_count, 0) AS distinct_hacker_count, top_hacker.hacker_id, top_hacker.name FROM (SELECT DISTINCT submission_date FROM submissions) AS dates LEFT JOIN ( SELECT s1.submission_date, COUNT(s1.hacker_id) AS hacker_count FROM (SELECT DISTINCT submission_date, hacker_id FROM submissions) AS s1 WHERE (SELECT COUNT(DISTINCT s2.submission_date) FROM submissions s2 WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date <= s1.submission_date ) = DATEDIFF(s1.submission_date, '2016-03-01') + 1 GROUP BY s1.submission_date ) AS consistent_counts ON dates.submission_date = consistent_counts.submission_date LEFT JOIN ( SELECT daily_counts.submission_date, MIN(daily_counts.hacker_id) AS hacker_id, (SELECT name FROM hackers WHERE hacker_id = MIN(daily_counts.hacker_id)) AS name FROM ( SELECT submission_date, hacker_id, COUNT() AS subs_count FROM submissions GROUP BY submission_date, hacker_id ) AS daily_counts JOIN ( SELECT submission_date, MAX(subs_count) AS max_subs FROM ( SELECT submission_date, hacker_id, COUNT() AS subs_count FROM submissions GROUP BY submission_date, hacker_id ) AS inner_counts GROUP BY submission_date ) AS max_counts ON daily_counts.submission_date = max_counts.submission_date AND daily_counts.subs_count = max_counts.max_subs GROUP BY daily_counts.submission_date ) AS top_hacker ON dates.submission_date = top_hacker.submission_date ORDER BY dates.submission_date;

The question is VERY misleading. (1) here "each day" basically means from day1 (2016-03-01), but can end at any date, continuously, not necessarily to be 2016-03-15.
(2) the final hacker ID is NOT based on users who submit each day, instead, it is based on the RAW data only. The examples given are WRONGLY explained.

My Oracle code worked: WITH each_day_hacker_ranked AS ( SELECT submission_date, hacker_id, submission_date - TO_DATE('2016-03-01', 'YYYY-MM-DD') AS date_diff, DENSE_RANK() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS dense_ranked FROM submissions ), part1_eachday_hackers as ( SELECT submission_date, COUNT(DISTINCT hacker_id) AS cnt_hackers FROM each_day_hacker_ranked WHERE dense_ranked = date_diff + 1 group by submission_date ), per_hacker_per_day as ( select submission_date,hacker_id, count(submission_id) cnt_submission from submissions group by submission_date,hacker_id ), most_submission as ( select submission_date, hacker_id, row_number() over (partition by submission_date order by cnt_submission desc, hacker_id) as row_num from per_hacker_per_day ), part2_most_submission as (select * from most_submission where row_num=1) --final select a.*, b.hacker_id, c.name from part1_eachday_hackers a left join part2_most_submission b on a.submission_date=b.submission_date left join hackers c on b.hacker_id=c.hacker_id;

show me any wrong from this solution :

with A as ( select submission_date, A.hacker_id, count(submission_id) n_submit from Submissions A group by submission_date, A.hacker_id ) , B as ( select submission_date, hacker_id, n_submit , rank () over (partition by submission_date order by n_submit desc) rn from A ), C as ( select submission_date, count(distinct hacker_id) num_hacker from Submissions group by submission_date ) , D as (select *, row_number () over (partition by submission_date order by hacker_id) rn2 from B where rn = 1 ) -- select * from D select C.submission_date, C.num_hacker, D.hacker_id, E.name from C join D on C.submission_date = D.submission_date and rn2 = 1 join Hackers E on D.hacker_id = E.hacker_id order by submission_date

Can you help why this doesn't work?

WITH one_sub AS ( SELECT hacker_id FROM submissions GROUP BY hacker_id HAVING COUNT(DISTINCT submission_date)=15 ), active_hackers AS ( SELECT s.submission_date, COUNT(DISTINCT hacker_id) AS total_active FROM submissions s JOIN one_sub os ON s.hacker_id=os.hacker_id GROUP BY s.submission_date ), hacker_daily_subs AS ( SELECT submission_date, hacker_id, COUNT(*) AS total_subs FROM submissions GROUP BY submission_date, hacker_id ), ranked_subs AS ( SELECT *, RANK() OVER (PARTITION BY submission_date ORDER BY total_subs DESC, hacker_id) AS rnk FROM hacker_daily_subs)

SELECT r.submission_date, a.total_active, r.hacker_id, h.name FROM ranked_subs r JOIN hackers h ON r.hacker_id = h.hacker_id JOIN active_hackers a ON r.submission_date = a.submission_date WHERE r.rnk = 1 ORDER BY r.submission_date;