We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Solution in sql server
with sub (hacker_id,name,score,submission_date)
as( select a.hacker_id,b.name,count() as score,a.submission_date from submissions as a
join hackers as b on a.hacker_id=b.hacker_id
group by a.hacker_id,a.submission_date,b.name),
/----------------------------------------------------------------------------------------*/
maxi (submission_date,score) as(select distinct submission_date,max(score) from sub
group by submission_date),
minimax (hacker_id,submission_date,score) as(select min(a.hacker_id) ,b.submission_date,b.score from sub a
join maxi b on b.submission_date=a.submission_date and b.score=a.score
group by b.submission_date,b.score),
/----------------------------------------------------------------------------------------/
fechas(hacker_id,submission_date) as(
select hacker_id,
convert(date, '2016-03-01') as submission_date
from submissions as s
where submission_date='2016-03-01'
union all
select s.hacker_id,
dateadd(day,1,f.submission_date)
from submissions s
join fechas f on f.hacker_id=s.hacker_id
where s.submission_date=dateadd(day,1,f.submission_date) and f.submission_date-----------------------------------------------------------------------------------------/
id(submission_date,cantidad) as(
select submission_date,count(distinct hacker_id)from fechas
group by submission_date
)
/-----------------------------------------------------------------------------------------/
select a.submission_date,id.cantidad, b.hacker_id,a.name
from sub as a
join minimax as b on a.submission_date=b.submission_date and a.hacker_id=b.hacker_id
join id on id.submission_date=a.submission_date
order by a.submission_date;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →
Solution in sql server with sub (hacker_id,name,score,submission_date) as( select a.hacker_id,b.name,count() as score,a.submission_date from submissions as a join hackers as b on a.hacker_id=b.hacker_id
group by a.hacker_id,a.submission_date,b.name), /----------------------------------------------------------------------------------------*/ maxi (submission_date,score) as(select distinct submission_date,max(score) from sub group by submission_date), minimax (hacker_id,submission_date,score) as(select min(a.hacker_id) ,b.submission_date,b.score from sub a join maxi b on b.submission_date=a.submission_date and b.score=a.score group by b.submission_date,b.score),
/----------------------------------------------------------------------------------------/ fechas(hacker_id,submission_date) as( select hacker_id,
convert(date, '2016-03-01') as submission_date from submissions as s where submission_date='2016-03-01' union all select s.hacker_id, dateadd(day,1,f.submission_date) from submissions s join fechas f on f.hacker_id=s.hacker_id where s.submission_date=dateadd(day,1,f.submission_date) and f.submission_date-----------------------------------------------------------------------------------------/ id(submission_date,cantidad) as( select submission_date,count(distinct hacker_id)from fechas
group by submission_date
) /-----------------------------------------------------------------------------------------/
select a.submission_date,id.cantidad, b.hacker_id,a.name from sub as a join minimax as b on a.submission_date=b.submission_date and a.hacker_id=b.hacker_id join id on id.submission_date=a.submission_date
order by a.submission_date;