WITH 
cte1 AS (
        SELECT DISTINCT submission_date
        FROM submissions),
cte2 AS (
        SELECT submission_date,
                ROW_NUMBER() OVER(ORDER BY submission_date) AS rank_date
        FROM cte1),
cte3 AS (
        SELECT DISTINCT submission_date,
                hacker_id
        FROM submissions),
cte4 AS (
        SELECT DISTINCT hacker_id,
                submission_date,
        ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS rank_hacker
        FROM cte3),
cte5 AS (
        SELECT DISTINCT cte2.submission_date,
        COUNT(cte4.hacker_id) OVER(PARTITION BY cte4.submission_date) AS cnt_id
        FROM cte2
        JOIN cte4 ON cte2.submission_date = cte4.submission_date
        WHERE rank_date = rank_hacker),
cte6 AS (
        SELECT submission_date, 
                hacker_id, 
                COUNT(submission_id) AS cnt_sub
        FROM submissions
        GROUP BY submission_date,
                 hacker_id),
cte7 AS (
        SELECT submission_date, 
               hacker_id, 
               ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY cnt_sub DESC, hacker_id) AS rank_hacker_daily
        FROM cte6)
SELECT cte5.submission_date,
       cte5.cnt_id,
       cte7.hacker_id,
       h.name
FROM cte5
JOIN cte7 ON cte5.submission_date = cte7.submission_date
JOIN hackers h ON cte7.hacker_id = h.hacker_id
WHERE cte7.rank_hacker_daily = 1;

with cte as (SELECT Submission_Date,hacker_id,count(hacker_id) as cnt,
ROW_NUMBER() OVER (PARTITION BY Submission_Date ORDER BY cnt desc, hacker_id asc) AS row_num
FROM Submissions
GROUP by Submission_Date,hacker_id),

t1 as (SELECT Submission_Date,count(DISTINCT(hacker_id))uniq FROM `Submissions` group BY Submission_Date),

t2 as(select cte.Submission_Date,h.hacker_id,h.Name  from cte join Hackers h on h.hacker_id=cte.hacker_id where cte.row_num=1)

select t1.Submission_Date,t1.uniq,t2.hacker_id,t2.Name from t1 join t2 on t1.Submission_Date=t2.Submission_Date;

with first_output as
(Select submission_date,x.hacker_id,name from (Select submission_date,hacker_id,count(submission_id) cou,
dense_rank() over(partition by submission_date order by count(submission_id) desc , hacker_id) r  from submissions
group by submission_date,hacker_id) x join hackers h
on h.hacker_id = x.hacker_id
where r = 1  ),

cte as (Select distinct submission_date,hacker_id,dense_rank() over(order by submission_date) as rs from submissions),

second_output as(Select c1.submission_date,count(hacker_id) users from cte c1
where hacker_id in
(Select c2.hacker_id from cte c2
where c2.submission_Date  < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end
 group by c2.hacker_id
having count(hacker_id) = 
(select max(rs) from cte c3 
 where c3.submission_date < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end))
 group by c1.submission_date
)


Select fo.submission_date,users,hacker_id,name from first_output fo,second_output so
where fo.submission_date = so.submission_Date
order by fo.submission_date