I have been trying to solve the below problem https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true but looks like stuck at finding the count of hacker_ids who have submissions for every date in the order by given start date following. Below is the 2 versions of solution max_submissions which gives max count of submission per date with lowest id if multiple max dates that is coming as correct but in the final query for count I am unable to get proper counts it is giving count as 35 for all dates with submissions on every day per hacker_id. Only 2nd column which is unique hackers count in the output that I am unable to get either I get 35 as count value for all or other values which seems to be different from expected output but by logic seems correct
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;
After digging in the Recursive Query was able to solve this problem. Below is the code for it
with dat as
(
select a.*,h.name
from
(
select submission_date,min(hacker_id) mn from
(
select submission_date,hacker_id,cnt, rank() over ( partition by submission_date order by submission_date asc, cnt desc) rn from
(
select submission_date,hacker_id,count(distinct submission_id) cnt
from
submissions
group by submission_date,hacker_id
)
) where rn=1
group by submission_date
order by submission_date
) a join
hackers h
on a.mn=h.hacker_id
)
,
dat1( submission_date, hacker_id,lev) as
(
select submission_date, hacker_id, 0 as lev
from submissions
WHERE submission_date='2016-03-01'
Union all
select s.submission_date, s.hacker_id, lev+1
from submissions s
join Dat1 d on
s.hacker_id =d.hacker_id
and s.submission_date=d.submission_date+1
)
,
dat2 as
(
select submission_date,count(distinct hacker_id) cnt from dat1
group by submission_date order by submission_date)
Select d.submission_date,d1.cnt,d.mn,d.name
from
(Select * from dat) d join
(Select * From dat2) d1
on d.submission_date=d1.submission_date
;