on first day hacker_id: 36396 has submitted but he hasn't submitted the second day, then why is he considered to have submitted each day? Either the question is invalid or the explanation is. It's actually a hard question in a way that you are never going to understand what is being asked for. Such a waste of time.

the problem description not clear , waste me alot time

select 
submission_date ,

( SELECT COUNT(distinct hacker_id)  
 FROM Submissions s2  
 WHERE s2.submission_date = s1.submission_date AND    (SELECT COUNT(distinct s3.submission_date) FROM      Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) = dateDIFF(s1.submission_date , '2016-03-01')) ,

(select hacker_id  from submissions s2 where s2.submission_date = s1.submission_date 
group by hacker_id order by count(submission_id) desc , hacker_id limit 1) as shit,
(select name from hackers where hacker_id = shit)
from 
(select distinct submission_date from submissions) s1
group by submission_date

Someone made a SQL fiddle for the previous problem and it was a great help. So seeing that no one put one up for this problem, here's a sqlfiddle with the example data:

http://sqlfiddle.com/#!9/844928

DDL for the problem set:

create table submissions ( submission_date date, submission_id int, hacker_id int, score int );

insert into submissions values ('2016-03-01', 8494, 20703, 0), ('2016-03-01', 23965, 79722, 60), ('2016-03-01', 23965, 79722, 60), ('2016-03-01', 30173, 36396, 70), ('2016-03-02', 34928, 20703, 0), ('2016-03-02', 38740, 15758, 60), ('2016-03-02', 42769, 79722, 60), ('2016-03-02', 44364, 79722, 60), ('2016-03-03', 45440, 20703, 0), ('2016-03-03', 49050, 36396, 70), ('2016-03-03', 50273, 79722, 5), ('2016-03-04', 50344, 20703, 0), ('2016-03-04', 51360, 44065, 90), ('2016-03-04', 54404, 53473, 65), ('2016-03-04', 61533, 79722, 45), ('2016-03-05', 72852, 20703,0), ('2016-03-05',74546, 38289, 0), ('2016-03-05', 76487, 62529, 0), ('2016-03-05',82439, 36396, 10), ('2016-03-05', 9006, 36396, 40), ('2016-03-06', 90404, 20703, 0); create table hackers ( hacker_id int, name varchar(255) ); insert into hackers values (15758, 'Rose'), (20703, 'Angela'), (36396, 'Frank'), (38289, 'Patrick'), (44065, 'Lisa'), (53473, 'Kimberly'), (62529, 'Bonnie'), (79722, 'Michael');

The below query is working fine for me in oracle

select table2.submission_date, table2.Unique_Count, table1.hacker_id , hackers.name from hackers, ( select * from ( select submission_date, hacker_id ,row_number() over (partition by submission_date order by count desc, hacker_id asc) RN from ( select submission_date, hacker_id, count(*) as count from submissions group by submission_date,hacker_id having count(*) >= 1 order by submission_date)) where RN = 1) table1, ( select * from (SELECT submission_date, COUNT(DISTINCT hacker_id) Unique_Count FROM (SELECT a.submission_date, v.hacker_id, COUNT(DISTINCT v.submission_date) cnt FROM ( SELECT DISTINCT submission_date FROM Submissions ORDER BY 1 )A, submissions v WHERE v.submission_date <= a.submission_date GROUP BY a.submission_date, v.hacker_id ) WHERE TO_CHAR(submission_date,'DD') = cnt GROUP BY submission_date ORDER BY 1)) table2 where hackers.hacker_id = table1.hacker_id and table1.submission_date = table2.submission_date order by table1.submission_date;

I managed to create a query corresponding to the expected output. The description of the problem should be as follow: Write a query to print total number of unique hackers who made at least submission each day (starting on the first day of the contest) until that day and find the hacker_id and name of the hacker who made maximum number of submissions each day (without considering if they made submissions the days before or after) If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

MS SQL solution I have used two cte in this query

with

cte1 as ( select hacker_id, submission_date, dense_rank() over (PARTITION BY hacker_id order by submission_date) as RN from submissions ),

cte2 as

( select s.submission_date, s.hacker_id, h.name, row_number() over (partition by s.submission_date order by count(s.submission_id)desc, s.hacker_id asc) as Number from submissions as s join hackers as h on s.hacker_id = h.hacker_id group by s.submission_date, s.hacker_id,h.name )

select a1.submission_date, total, hacker_id, name from

(select submission_date,count(distinct(hacker_id)) as Total from cte1 where rn = datepart(day, submission_date) group by submission_date)

as A1

join

(select submission_date, hacker_id, name from cte2 where number = 1 )

As A2

on A1.submission_date = A2.submission_date

I found the usage of a recursive CTE to be most helpful in this scenario.

DECLARE @startDate DATE = '03/01/2016'
;WITH ConsistentHackers AS (
    SELECT
        s.submission_date
        ,s.hacker_id
    FROM Submissions s
    WHERE s.submission_date = @startDate
    UNION ALL
    SELECT
        DATEADD(dd, 1, ch.submission_date) AS submission_date
        ,s.hacker_id
    FROM Submissions s
    JOIN ConsistentHackers ch
        ON s.hacker_id = ch.hacker_id
        AND s.submission_date = DATEADD(dd, 1, ch.submission_date) 
), ConsistencyCounts AS (
    SELECT
        ch.submission_date
        ,COUNT(DISTINCT ch.hacker_id) AS ConsistentHackers
    FROM ConsistentHackers ch
    GROUP BY ch.submission_date
), SubmissionsSummary AS (
    SELECT
        s.submission_date
        ,s.hacker_id
        ,ROW_NUMBER() OVER (
            PARTITION BY s.submission_date
            ORDER BY COUNT(*) DESC, s.hacker_id ASC
        ) AS ranking
    FROM Submissions s
    GROUP BY
        s.submission_date
        ,s.hacker_id
)
SELECT
    ss.submission_date
    ,cc.ConsistentHackers
    ,h.hacker_id
    ,h.name
FROM SubmissionsSummary ss
JOIN ConsistencyCounts cc
    ON ss.submission_date = cc.submission_date
    AND ss.ranking = 1
JOIN Hackers h
    ON ss.hacker_id = h.hacker_id
ORDER BY ss.submission_date ASC

I found this pretty impossible until reading the discussions, so for anyone looking for hints, this is what they are asking for:

select
    Submission_date, 
    Number of hackers who have submitted every day so far,
    Hacker_id of the hacker with the most submits on this day,
      (regardless of which other days they submitted)
    Their_name

And here is my Oracle solution...

/*  tree through submissions starting with first day,  */
/*    following hacker_ids from day to day             */
/*returns: Submission_date, numberOfHackersStillSubmittingEveryDay */
with DAYSASLEVELS as 
	(
	select Submission_date, 
		count(distinct Hacker_id) as NumHackers
	 from SUBMISSIONS
	 group by Submission_date
	 start with Submission_date='2016-03-01'
	 connect by 
		 prior to_date(Submission_date,'yyyy-mm-dd') 
			 = to_date(Submission_date,'yyyy-mm-dd')-1 
		and        prior Hacker_id=Hacker_id 
		and to_date(Submission_date,'yyyy-mm-dd')
			 <= to_date('2016-03-15','yyyy-mm-dd')
	),
/* returns: Submission_date, Hacker_id, numberOfHacks */
/*           for each hacker on each day */
	MULTIHACKERS as
	(
	select Submission_date, 
		Hacker_id, 
		count(Submission_id) as NumHacks
	 from SUBMISSIONS
	 where to_date(Submission_date,'yyyy-mm-dd') 
		between to_date('2016-03-01','yyyy-mm-dd') 
		 and to_date('2016-03-15','yyyy-mm-dd')
	 group by Submission_date, Hacker_id
	 having count(Submission_id)>0
	),
/* returns: Submission_date, maxNumberOfHacks  on that day   */
	MOSTHACKS as
	(
	select Submission_date as Subdate, 
		max(NumHacks) as MaxHacks
	 from MULTIHACKERS
	 group by Submission_date
	),
/* returns: date, numHackersWithSubEveryDaySoFar,  */
/*          smallestHackerIdWithMaxhacks           */
	SOLNBYID as
	(
	select MH1.Submission_date as Sdate,
		NumHackers,
		min(MH2.Hacker_id) as WinnerId
	 from MULTIHACKERS MH1 
	  inner join MULTIHACKERS MH2 
		on MH1.Submission_date=MH2.Submission_date
	  inner join MOSTHACKS 
		on MH2.NumHacks=maxHacks 
		 and MH2.Submission_date=MOSTHACKS.Subdate
	  inner join DAYSASLEVELS 
		on MH1.Submission_date=DAYSASLEVELS.Submission_date
	 group by MH1.Submission_date, NumHackers
	)
/*  tack on the Name of the winning Hacker_id */
 select Sdate, NumHackers, H.Hacker_id, H.Name
  from (SOLNBYID inner join HACKERS H on H.Hacker_id=WinnerId)
  order by to_date(Sdate,'yyyy-mm-dd');