with cons_submission as ( select h.hacker_id,h.name,s.submission_date, dense_rank() over (partition by submission_date,h.hacker_id order by submission_date asc) as cons_sub from hacker h inner join submissions s on h.hacker_id = s.hacker_id),

hacker_XV as ( select hacker_id,name from cons_submission group by hacker_id,name having max(cons_sub) = 15),

hackers_fltrd as ( select cs.hacker_id,cs.name,cs.submission_date, count(*) as submitted_count from cons_submission cs inner join hacker_XV hxv on cs.hacker_id = hxv.hacker_id group by hacker_id,name,submission_date ),

cte as ( select hacker_id,name,submission_date,submitted_count, row_number() over (partition by submission_date order by submitted_count desc, hacker_id asc ) as row_no from hackers_fltrd )

select submission_date,submitted_count,hacker_id,name from cte where row_no = 1;

OUTPUT: 2016-03-01 112 81314 Denise 2016-03-02 59 39091 Ruby 2016-03-03 51 18105 Roy 2016-03-04 49 533 Patrick 2016-03-05 49 7891 Stephanie 2016-03-06 49 84307 Evelyn 2016-03-07 35 80682 Deborah 2016-03-08 35 10985 Timothy 2016-03-09 35 31221 Susan 2016-03-10 35 43192 Bobby 2016-03-11 35 3178 Melissa 2016-03-12 35 54967 Kenneth 2016-03-13 35 30061 Julia 2016-03-14 35 32353 Rose 2016-03-15 35 27789 Helen still wrong

The original version I wrote was very long, but after looking at other answers, I simplified it using the condition in the WHERE clause. Thank you very much!

SELECT
    daily_continuous.submission_date,
    daily_continuous.continuous_hackers_count,
    daily_max.hacker_id,
    h.name
FROM (
    SELECT
        s1.submission_date,
        COUNT(DISTINCT s1.hacker_id) AS continuous_hackers_count
    FROM Submissions AS s1
    WHERE (
        SELECT
            COUNT(DISTINCT s2.submission_date)
        FROM Submissions AS s2
        WHERE s2.hacker_id = s1.hacker_id
        AND s2.submission_date < s1.submission_date
    ) = DATEDIFF(s1.submission_date, '2016-03-01')
    GROUP BY s1.submission_date
) AS daily_continuous

JOIN (
    SELECT
        daily_max_hackers.submission_date,
        MIN(daily_max_hackers.hacker_id) AS hacker_id
    FROM (
        SELECT
            daily_hacker_data.submission_date,
            daily_hacker_data.hacker_id
        FROM (
            SELECT
                submission_date,
                hacker_id,
                COUNT(*) AS sub_count
            FROM Submissions
            GROUP BY submission_date, hacker_id
        ) AS daily_hacker_data
        JOIN (
            SELECT
                daily_hacker_data.submission_date,
                MAX(daily_hacker_data.sub_count) AS sub_max
            FROM (
                SELECT
                    submission_date,
                    hacker_id,
                    COUNT(*) AS sub_count
                FROM Submissions
                GROUP BY submission_date, hacker_id
            ) AS daily_hacker_data
            GROUP BY submission_date
        ) AS daily_sub_max
        ON daily_hacker_data.submission_date = daily_sub_max.submission_date
        AND daily_hacker_data.sub_count = daily_sub_max.sub_max
    ) AS daily_max_hackers
    GROUP BY daily_max_hackers.submission_date
) AS daily_max
ON daily_continuous.submission_date = daily_max.submission_date
JOIN Hackers AS h ON h.hacker_id = daily_max.hacker_id
ORDER BY daily_continuous.submission_date;

Correct working solution broken down biy by bit; \n with daily_count as (select submission_date,hacker_id,count() as num_sub from submissions where submission_date between to_date('March 01, 2016','month dd, yyyy') and to_date('March 15, 2016','month dd, yyyy') group by submission_date,hacker_id), daily_user as (select submission_date,hacker_id,to_number(to_char(submission_date, 'dd')) as day,row_number() over (partition by hacker_id order by submission_date) as rn from daily_count), daily_user_count as (select submission_date,count() as user_count from daily_user where day=rn group by submission_date), max_user as (select submission_date,hacker_id,rank() over (partition by submission_date order by num_sub desc,hacker_id asc) as rank from daily_count), daily_max_user as (select max_user.submission_date,hackers.hacker_id,hackers.name from max_user join hackers on max_user.hacker_id=hackers.hacker_id where rank=1) select daily_user_count.submission_date,daily_user_count.user_count,daily_max_user.hacker_id,daily_max_user.name from daily_user_count join daily_max_user on daily_user_count.submission_date=daily_max_user.submission_date;

SELECT streaks.submission_date, streaks.active_hackers_count, top_h.hacker_id, h.name FROM ( SELECT s1.submission_date, COUNT(DISTINCT s1.hacker_id) AS active_hackers_count FROM Submissions AS s1 WHERE ( SELECT COUNT(DISTINCT s2.submission_date) FROM Submissions AS s2 WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date < s1.submission_date ) = DATEDIFF(s1.submission_date, '2016-03-01') GROUP BY s1.submission_date ) AS streaks JOIN ( SELECT Ties.submission_date, MIN(Ties.hacker_id) AS hacker_id FROM ( SELECT dc.submission_date, dc.hacker_id FROM ( SELECT submission_date, hacker_id, COUNT(1) AS subs_count FROM Submissions GROUP BY submission_date, hacker_id ) AS dc JOIN ( SELECT submission_date, MAX(subs_count) AS max_s FROM ( SELECT submission_date, hacker_id, COUNT(1) AS subs_count FROM Submissions GROUP BY submission_date, hacker_id ) AS dc2 GROUP BY submission_date ) AS mc ON dc.submission_date = mc.submission_date AND dc.subs_count = mc.max_s ) AS Ties GROUP BY Ties.submission_date ) AS top_h ON streaks.submission_date = top_h.submission_date JOIN Hackers h ON top_h.hacker_id = h.hacker_id ORDER BY streaks.submission_date;

with a as (select submissionDate, hackerid , count() as submissiontimes from submissions group by submissionDate, hackerid ), b as (select ,Rownumber()over(partition by submissionDate order by submissiontimes desc, hackerid) as seq from a), e as (select ,day(submissionDate) as submissionDay,Denserank()over(partition by hackerid order by submissionDate) as seq from submissions) select c.submissionDate, f.num ,c.hackerid,d.name from (select from b where seq=1 ) c left join hackers d on c.hackerid=d.hackerid left join (select submissionDate,count(distinct hackerid) as num from e where submissionDay=seq group by submissionDate) f on c.submissionDate = f.submissionDate order by submissionDate;