Hackerrank《15天SQL学习》题目：计数模块卡壳求助

我在做Hackerrank的《15天SQL学习》题目，卡在了统计从2016-03-01开始每日都有提交记录的hacker_id数量这一步。

我写的max_submissions公共表表达式（CTE）能正确获取每日提交数最多且hacker_id最小的记录，但最终查询的计数结果存在问题：要么所有日期的计数均为35，要么结果与预期不符，尽管逻辑看似合理。以下是我的SQL代码：

with max_submissions
as 
(
Select t.submission_date,t.hacker_id,t.cnt,h.name From
(Select * from 
(Select submission_date, hacker_id, cnt, dense_rank() over (partition by submission_date order by cnt desc,hacker_id asc) as rn
from
(Select 
submission_date, hacker_id, count(submission_id) cnt
from 
submissions
where submission_date between '2016-03-01' and '2016-03-15' 
group by submission_date, hacker_id
) 
)where rn =1
) t join 
hackers h on t.hacker_id=h.hacker_id
),
t1
as
(
select  hacker_id
from
(
Select 
 hacker_id, lead(submission_date) over ( order by hacker_id,submission_date)
 -submission_date cnt
from 
submissions
where submission_date between '2016-03-01' and '2016-03-15' 
order by hacker_id asc, submission_date asc)
group by hacker_id having sum(case when cnt=1 then 1 else 0 end) =14)
select s.submission_date,count( t1.hacker_id)
from submissions s
join 
t1 on
s.hacker_id=t1.hacker_id
group by s.submission_date;

内容的提问来源于stack exchange，提问作者Himanshu