rajesh_38_paul 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.
y_starikovich [deleted]
Harshil93 For 1st day, you only need to check submissions for that particular day while starting from 2nd day, you need to check whether they had submissions on all previous days also or not. If not then those hacker_ids doesn't count!
svdubl Well if you look into 2016-03-02 there is hacker with id 15758, who did not submit before, and his submission is not counted in unique ids for that day
dursunaliozer I strongly agree with you im sure that data, question or explanation is not valid. @Harshil93 you can list details with the hacker id 18105 you will see that it does not have any summission for some previous days but its on the list any way.
Harshil93 On any particular day, you need to compare max num of submissions among 2 types of hackers: 1) who have at least one submission since the day they submitted for the 1st time and 2) hackers who started to submit for the 1st time on that particular day (see explaination for March 5)
In this test case, for 2016-03-03, there are hackers who has submiited on 1st, 2nd and 3rd march, but max submissions done by hacker_id 18105 who has done his 1st submission on that day only.
Hence 18105 made to the list !!
I hope u got it now!!
dursunaliozer so explanation is not valid
Harshil93 It's valid but a little ambigous!!
jhli_potomac I don't understande the question either.
The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
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),
So the first day is March 01, 2016, right?
gtang31 this explanaation does not make sense
bk000 I agree with you too. The count for unique hacker_id who submitted everyday should be the same everyday.
Harshil93 No, that's not true because you need to take in account to those hackers who started doing submission on a particular day. Not every hacker started it on the first day and that's the trick in the given problem.
bk000 OK I think I got what the problem was looking for from the explanation. What confused me was this part of problem: "Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest)". It made me think it meant each day of the contest. If it stated "(starting on the first day of the contest to the submission_date)" it would be more clear.
jason_goemaat1 Yeah, or 'who have made a submission every day up to that point'
coolkaur94 I agree with your all comments but one thing i can not understand. If you see the number of uniq hacker_id for first day is 112 and the second day is 59. How it is possible if consider the hacker have atleast one submission each day. As per your explation there should be increase in submissions. Please correct me if i am wrong or you can not understand the explanation.
lackova_cngroup There might be hackers who made submission on the first day, but skipped the second day. These ones are not considered to fulfill the condition of having at least one submission per day from the second day on. Another hackers might have joined the contest any day after the first day and these ones do never fulfill the condition (of a submission per day). The condition is something you have to consider in your calculation; it does not define the rules for input table records.
ravi_shankar_bm1 where is that mentioned ? that's your assumption completely and probably they thought the same way. But this explaination isnt correct. By this logic, all the new people who submitted the last day, should be accounted for even if they never submitted it earlier and hence doesn't goes by question.
jainanupam is it really a "...trick in the given problem"??
Er_Le0 hard means bad explanation I guess
fengli97 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
ryan_wanglan Could you please explain why you used 'group by' in s1? Why cannot I replace last three line to 'from submissions s1', and add 'distinct' in the first line? Thank you.
fengli97 i guess your version may be overtime ; suppose the size of submission_date is m and the size of s1 is n , obviously n << m ; due to the order of sql query , your version will cost m/n times the time .
ryan_wanglan Thank you very much, that's exactly the problem.
hxbai I think you may not need to use the last group by clause cause your select distinct already done the work.
But this is really a great solution! Thank you.
rituraj_kumar can you explain this line in the query this full line
s3.submission_date <s1.submission_date) = dateDIFF(s1.submission_date , '2016-03-01'))
rituraj_kumar [deleted]
bicepjai good job, coulf you explain how you got
total number of unique hackers who made at least submission each day (starting on the first day of the contest)(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'))
jmichaelmedina Thanks for sharing @fengli97 - distinct hacker id count in second field is not trivial. I renamed subqueries to learn more. Figured I'd share it.
/* submission_date hackerCount,submissionCount,dates used to get number of unique submissions each day topHack - hacker_id with most submissions for each day dates - list unique contest dates */ SELECT submission_date , ( SELECT COUNT(distinct hacker_id) FROM Submissions hackerCount WHERE hackerCount.submission_date = dates.submission_date AND (SELECT COUNT(distinct submissionCount.submission_date) FROM Submissions submissionCount WHERE submissionCount.hacker_id = hackerCount.hacker_id AND submissionCount.submission_date < dates.submission_date) = dateDIFF(dates.submission_date , '2016-03-01') ) , ( SELECT hacker_id FROM submissions hackerList WHERE hackerList.submission_date = dates.submission_date GROUP BY hacker_id ORDER BY count(submission_id) DESC , hacker_id limit 1) as topHack, (SELECT name FROM hackers WHERE hacker_id = topHack) FROM (SELECT distinct submission_date from submissions) dates GROUP BY submission_date
jyang4 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:
truchisoft Excellent! thank you
aagnihotri 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');
badhansaha444 which platform use?
aagnihotri mysql
eric_brandt_2064 Works as written for MS SQL as well. Thanks to the OP.
rodbourn The data is incorrect, note the second insert is repeated twice?
biskvitoj Here's the correct data:
CREATE TABLE submissions (submission_date date, submission_id integer, hacker_id integer, score integer); INSERT INTO "submissions" VALUES('2016-03-01',8494,20703,0); INSERT INTO "submissions" VALUES('2016-03-01',22403,53473,15); INSERT INTO "submissions" VALUES('2016-03-01',23965,79722,60); INSERT INTO "submissions" VALUES('2016-03-01',30173,36396,70); INSERT INTO "submissions" VALUES('2016-03-02',34928,20703,0); INSERT INTO "submissions" VALUES('2016-03-02',38740,15758,60); INSERT INTO "submissions" VALUES('2016-03-02',42769,79722,60); INSERT INTO "submissions" VALUES('2016-03-02',44364,79722,60); INSERT INTO "submissions" VALUES('2016-03-03',45440,20703,0); INSERT INTO "submissions" VALUES('2016-03-03',49050,36396,70); INSERT INTO "submissions" VALUES('2016-03-03',50273,79722,5); INSERT INTO "submissions" VALUES('2016-03-04',50344,20703,0); INSERT INTO "submissions" VALUES('2016-03-04',51360,44065,90); INSERT INTO "submissions" VALUES('2016-03-04',54404,53473,65); INSERT INTO "submissions" VALUES('2016-03-04',61533,79722,45); INSERT INTO "submissions" VALUES('2016-03-05',72852,20703,0); INSERT INTO "submissions" VALUES('2016-03-05',74546,38289,0); INSERT INTO "submissions" VALUES('2016-03-05',76487,62529,0); INSERT INTO "submissions" VALUES('2016-03-05',82439,36396,10); INSERT INTO "submissions" VALUES('2016-03-05',9006,36396,40); INSERT INTO "submissions" VALUES('2016-03-06',90404,20703,0); CREATE TABLE hackers (hacker_id integer, name string); INSERT INTO "hackers" VALUES(15758,'Rose'); INSERT INTO "hackers" VALUES(20703,'Angela'); INSERT INTO "hackers" VALUES(36396,'Frank'); INSERT INTO "hackers" VALUES(38289,'Patrick'); INSERT INTO "hackers" VALUES(44065,'Lisa'); INSERT INTO "hackers" VALUES(53473,'Kimberly'); INSERT INTO "hackers" VALUES(62529,'Bonnie'); INSERT INTO "hackers" VALUES(79722,'Michael');
fhf_ata Replace the last 2016-03-05 by: INSERT INTO "submissions" VALUES('2016-03-05',9006,36396,40);
9006 to 9006
rajj206 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;
pgsanka Great!!! this is working awesome Are there any steps to follow on how to approach this question? It would be highly helpful.
b0kater I really like this one. I converted it to MS SQL. My original solution was very inefficient, with subqueries in where and having clauses. Reviewing this solution and understanding how it works definitely improved my understanding of SQL.
ianlee313 Could you please explain this to me? Thanks in advance!
hkrishnan91 Same here! Awesome solution.
arun_dakua Amazing solution!!!
alexescalonafer1 Another solution in oracle more intuitive but more verbose
with R(submission_date, hacker_id) as ( select distinct submission_date, hacker_id from Submissions where submission_date = to_date('2016-03-01') union all select child.submission_date, child.hacker_id from R parent, Submissions child where parent.submission_date + 1 = child.submission_date and parent.hacker_id = child.hacker_id ), Total as (select submission_date, count(distinct hacker_id) as total from R group by submission_date), Counter as (select submission_date, hacker_id, count(hacker_id) as n from Submissions group by submission_date, hacker_id), MaxPerDay as (select C.submission_date, min(C.hacker_id) as hacker_id from Counter C where C.n = (select max(K.n) from Counter K where C.submission_date = K.submission_date) group by submission_date) select Total.submission_date, Total.total, Hackers.hacker_id, Hackers.name from Total join MaxPerDay on Total.submission_date = MaxPerDay.submission_date join Hackers on MaxPerDay.hacker_id = Hackers.hacker_id order by Total.submission_date;
rvs3129 This very nice, but doesn't this assume that all dates are from 2016-03-01 to 2016-03-15? How would you have done it differently had there been dates from all competitions?
shzhang_us Very nice. R can also be written using "connect by": with R as (select submission_date, hacker_id from submissions s start with s.submission_date=to_date('2016-03-01','YYYY-MM-DD') connect by s.hacker_id= prior hacker_id and s.submission_date = prior submission_date +1),
and MaxPerDay can be written using "rank()" function MaxPerDay as (select submission_date, hacker_id, n, rank() over (partition by submission_date order by n desc, hacker_id) rank from counter)
in the final query add "MaxPerDay.rank=1" into where clause
elevenal SQL Server Version for this code.
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) as 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 ) a ) b where rn=1 ) table1, ( SELECT submission_date, COUNT(DISTINCT hacker_id) Unique_Count from ( SELECT d.submission_date, v.hacker_id, COUNT(DISTINCT v.submission_date) cnt fROM (SELECT DISTINCT submission_date FROM Submissions --ORDER BY submission_date ) d, submissions v where v.submission_date <= d.submission_date GROUP BY d.submission_date, v.hacker_id ) e WHERE Convert(Varchar(Max),day(submission_date)) = cnt GROUP BY submission_date --ORDER BY submission_date ) table2 where hackers.hacker_id = table1.hacker_id and table1.submission_date = table2.submission_date order by table1.submission_date ;
prith24 This will not work if the starting day of the challenge is anything other than the first day of the month. You would probably have to add another column to show the starting day of the challenge and compare the cnt with that column
rajasekhar10k Please explain, logic behind day(submission_date) = cnt
pragmatique submission dates run consecutively, starting with day 1...hence, for a count of consecutive days started on 3/1 for all hackers, this will account for hackers who didn't start to submit until after the first day.
sorinpetcu Why
Convert(Varchar(Max),day(submission_date)) = cnt
Instead, you could write simple:
day(submission_date) = cnt
And which is the logic behind this?
rustagiprakhar92 This will only work if the minimum date of dataset starts with "1" as we compare Day from count of hacker_id occurence, a more end to end version is this: select table1.submission_date,table2.unique_count,table1.hacker_id,table1.name from (select c.submission_date,c.hacker_id,d.name from (select submission_date,hacker_id,count_hack, row_number() over (partition by submission_date order by submission_date,count_hack desc,hacker_id) R_N from (select submission_date,hacker_id,count(hacker_id) as count_hack from submissions group by submission_date,hacker_id order by submission_date,count(hacker_id))) c LEFT JOIN HACKERS d ON c.hacker_id = d.hacker_id where R_N = 1) table1 INNER JOIN (select submission_date,count(distinct hacker_id) as unique_count from (select a.submission_date,b.hacker_id,count(DIStINCt b.submission_date) as cnt from (select distinct submission_date from submissions order by submission_date) a ,submissions b where b.submission_date <= a.submission_date group by a.submission_date,b.hacker_id order by a.submission_date) where cnt = (submission_date - (select min(submission_date) from submissions))+1 group by submission_date order by submission_date) table2 on table1.submission_date = table2.submission_date ;
lackova_cngroup Nice one. :) Mine has similar idea; it was the most difficult select on these pages so far.
with date_hacker_counts as ( select distinct s.submission_date as d, count(distinct s1.submission_date) over (partition by s.submission_date, s.hacker_id) as cs, s.hacker_id as h, h.name as n, count(distinct s.submission_id) over (partition by s.submission_date, s.hacker_id) as c from submissions s inner join hackers h on s.hacker_id = h.hacker_id inner join submissions s1 on s1.hacker_id = s.hacker_id and s1.submission_date <= s.submission_date order by s.submission_date, s.hacker_id) select dt.d, dt.cs, dt.h, dt.n from ( select dhc.d, sum(floor(dhc.cs/extract(day from dhc.d))) over (partition by dhc.d) cs, row_number() over (partition by dhc.d order by dhc.c desc, dhc.h asc) rn, dhc.h, dhc.n from date_hacker_counts dhc) dt where rn = 1 order by dt.d;
xvsvgt 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.
kp_jyothish I found difficult to understand with the sample. I amconfused with different value for unique hackers each day. :-(
lelcat 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_nameAnd 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');
Prabesh Good use of connect by prior here.
hulyacelikuysal I think that it is a simple and clear solution, thanks for sharing
shBomberman 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
abhi_b This is the accpeted answer. thanks for the query so that we all know this is. but the question seems wrong.
the third row in accepted result is 2016-03-03 51 18105 Roy 18105 does not have a submission on the two days before 3/3
shBomberman Hey there. The question is a bit tricky. We have to realize that the person's name who is listed in the final results set is considered to be the one with the highest number of submissions for that given day, regardless of wether or not he/she is a consistent hacker:
;WITH 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 * FROM SubmissionsSummary WHERE ranking = 1
siran [deleted]MichaelCretu Mine is almost identical to yours :) Although it took me a while to realize in the second CTE I need to COUNT(DISTINCT hacker_id). My recursive CTE is slightly different:
with CTE as ( select submission_date, hacker_id from Submissions where submission_date = '2016-03-01' UNION ALL select S.submission_date, S.hacker_id from Submissions S inner join CTE on DATEDIFF(Day, CTE.submission_date,S.submission_date) = 1 and S.hacker_id = CTE.hacker_id )
dsgandhi94 Can you please explain the above solution?
My solution is below:(in MySql)
select t1.submission_date, t1.un_hack, t2.hacker_id, name, total_sub from (select submission_date, count(distinct hacker_id) as un_hack from submissions group by submission_date) as t1, (select submission_date, hacker_id, count(submission_id) as total_sub from submissions group by submission_date, hacker_id) as t2, hackers h where t1.submission_date=t2.submission_date and h.hacker_id=t2.hacker_id order by t1.submission_date
But now i am not able to extract the single row(hacker with max submission) for each date.
Could you please help.
rs_anantmishra [deleted]
ywz3030 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
xiaoqiuy a really simple and brilliant solution!
TaylorRogers The solution has the potential to fail on other datasets as rn is expected to match the date of the month.
TaylorRogers Your where clause below will cause your solution to fail if anything other the the first of the month is used for the start date.
where rn = datepart(day, submission_date)
sanjay_dev 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 hack, (select name from hackers where hacker_id = hack) from (select distinct submission_date from submissions) s1 group by submission_date;
LearnCrazy Hi, could you please explain your logic behind the query. It's giving me a hard time understanding the logic. Thank you and hope to hear back !
Sort 171 Discussions, By:
Please Login in order to post a comment