SET NOCOUNT ON;

WITH DATA_TANGGAL AS ( SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15'

), DATA_HACKERS_MAX AS ( SELECT a.HACKER_ID, a.NAME, MIN(a.SUBMISSION_DATE) AS MIN_SUBMISSION_DATE FROM (SELECT DISTINCT SUBMISSION_DATE, HACKER_ID, NAME FROM HACKERS CROSS JOIN DATA_TANGGAL) A LEFT JOIN (select distinct SUBMISSION_DATE, HACKER_ID from SUBMISSIONS
WHERE SUBMISSION_DATE >= '2016-03-01' AND SUBMISSION_DATE <= '2016-03-15' ) B ON A.HACKER_ID = B.HACKER_ID AND A.SUBMISSION_DATE = B.SUBMISSION_DATE WHERE B.HACKER_ID IS NULL GROUP BY a.HACKER_ID, a.NAME ) , COUNT_DATA AS (

SELECT SUBMISSION_DATE,COUNT(*) AS TOTAL FROM (
SELECT DISTINCT A.SUBMISSION_DATE ,A.HACKER_ID FROM SUBMISSIONS A 
INNER JOIN DATA_TANGGAL B 
ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
LEFT JOIN DATA_HACKERS_MAX C 
ON A.HACKER_ID = C.HACKER_ID 
WHERE C.HACKER_ID IS NULL OR A.SUBMISSION_DATE <= C.MIN_SUBMISSION_DATE
) A 
GROUP BY SUBMISSION_DATE

), MAX_DATA AS (

SELECT SUBMISSION_DATE,HACKER_ID,NAME,COUNT(SCORE) AS TOTAL FROM (
SELECT DISTINCT A.SUBMISSION_DATE ,SUBMISSION_ID,NAME,A.HACKER_ID, SCORE FROM SUBMISSIONS A 
INNER JOIN DATA_TANGGAL B 
ON A.SUBMISSION_DATE = B.SUBMISSION_DATE
INNER JOIN HACKERS C 
ON A.HACKER_ID = C.HACKER_ID
) A 
GROUP BY SUBMISSION_DATE, HACKER_ID, NAME

), MAX_DATA_FINAL AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY SUBMISSION_DATE ORDER BY TOTAL DESC, HACKER_ID ASC) AS RN FROM MAX_DATA )

SELECT A.SUBMISSION_DATE, B. TOTAL, C.HACKER_ID, C.NAME

FROM DATA_TANGGAL A LEFT JOIN COUNT_DATA B ON A.SUBMISSION_DATE = B.SUBMISSION_DATE LEFT JOIN MAX_DATA_FINAL C ON A.SUBMISSION_DATE = C.SUBMISSION_DATE WHERE RN = 1 ORDER BY A.SUBMISSION_DATE ASC

go

--Using SQL Server-- declare @consistent_hackers table( hacker_id int, submission_date date );

declare @count_uniqhackers table( hacker_count int, submission_date date );

insert into @consistent_hackers select hacker_id, submission_date from submissions where submission_date like '2016-03-01' declare @submssn_date date declare @remaining_date date

set @submssn_date = '2016-03-01' set @remaining_date = '2016-03-01'

while @submssn_date < '2016-03-15' begin set @submssn_date = dateadd(day, 1, @submssn_date) insert into @consistent_hackers select s.hacker_id, s.submission_date from submissions s join @consistent_hackers c on s.hacker_id = c.hacker_id and c.submission_date like @remaining_date where s.submission_date like @submssn_date set @remaining_date = dateadd(day, 1, @remaining_date); end; insert into @count_uniqhackers select count(distinct hacker_id), submission_date from @consistent_hackers group by submission_date ;

with max_hacker as ( select row_number() over( partition by s.submission_date order by count(s.hacker_id) desc, s.hacker_id asc ) as rn, s.hacker_id, s.submission_date, name from submissions s inner join hackers h on s.hacker_id = h.hacker_id group by s.hacker_id, s.submission_date, name ) select s.submission_date, c.hacker_count, m.hacker_id, m.name from submissions s join max_hacker m on s.submission_date = m.submission_date and m.rn = 1 join @count_uniqhackers c on s.submission_date = c.submission_date --where --s.submission_date like '2016-03-01' group by s.submission_date, c.hacker_count, m.hacker_id, m.name order by s.submission_date

go

WITH daily_submissions AS ( SELECT submission_date, hacker_id, COUNT(*) AS num_subs FROM Submissions GROUP BY submission_date, hacker_id ), max_daily AS ( SELECT ds.submission_date, ds.hacker_id, ds.num_subs, RANK() OVER (PARTITION BY ds.submission_date ORDER BY ds.num_subs DESC, ds.hacker_id ASC) AS rnk FROM daily_submissions ds ), cumulative_hackers AS ( SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS total_hackers FROM Submissions s WHERE NOT EXISTS ( SELECT 1 FROM ( SELECT DISTINCT submission_date FROM Submissions ) d WHERE d.submission_date <= s.submission_date AND NOT EXISTS ( SELECT 1 FROM Submissions s2 WHERE s2.hacker_id = s.hacker_id AND s2.submission_date = d.submission_date ) ) GROUP BY s.submission_date ) SELECT ch.submission_date, ch.total_hackers, m.hacker_id, h.name FROM cumulative_hackers ch JOIN max_daily m ON ch.submission_date = m.submission_date AND m.rnk = 1 JOIN Hackers h ON m.hacker_id = h.hacker_id ORDER BY ch.submission_date;

why is my query failing test cases, can someone pls review my query?

WITH total_submissions_per_day AS ( SELECT s.submission_date, COUNT(DISTINCT h.hacker_id) AS total_submissions FROM submissions s INNER JOIN hackers h ON h.hacker_id = s.hacker_id GROUP BY s.submission_date ), hackers_submitting_everyday AS ( SELECT h.hacker_id FROM submissions s INNER JOIN hackers h ON h.hacker_id = s.hacker_id WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY h.hacker_id HAVING count(distinct s.submission_date) = 15 ), hacker_submission_counts AS ( SELECT s.submission_date, h.hacker_id, h.name, COUNT(s.submission_id) AS max_sub_by_hacker FROM submissions s INNER JOIN hackers h ON h.hacker_id = s.hacker_id AND h.hacker_id IN (select hacker_id from hackers_submitting_everyday) GROUP BY s.submission_date, h.hacker_id, h.name ), ranked_hackers AS ( SELECT submission_date, hacker_id, name, ROW_NUMBER() OVER ( PARTITION BY submission_date ORDER BY max_sub_by_hacker DESC, hacker_id ASC ) AS rnk FROM hacker_submission_counts ), top_hackers_per_day AS ( SELECT submission_date, hacker_id, name FROM ranked_hackers WHERE rnk = 1 ) SELECT a.submission_date, a.total_submissions, b.hacker_id, b.name FROM total_submissions_per_day a INNER JOIN top_hackers_per_day b ON a.submission_date = b.submission_date ORDER BY a.submission_date;

SET NOCOUNT ON;

/* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. */ with cte as ( SELECT s.submission_date, COUNT(distinct s.hacker_id) as Count_hacker_id,max(s.score) as max_score FROM Submissions s GROUP by s.submission_date)

select t.submission_date ,t.Count_hacker_id, (select top 1 s.hacker_id from Submissions s where s.score=t.max_score and t.submission_date=s.submission_date order by s.score desc ) as hacker_id, (select name from Hackers as h where hacker_id=(select top 1 s.hacker_id from Submissions s where s.score=t.max_score and t.submission_date=s.submission_date order by s.score desc )) as name from cte as t order by t.submission_date

--s.hacker_id--,h.name --, --and s.hacker_id=h.hacker_id

go