15 Days of Learning SQL
15 Days of Learning SQL
+ 0 comments with t as ( select submission_date, h.hacker_id, name, count( submission_id) as br, dense_rank() over (partition by h.hacker_id order by submission_date) as rbr, dense_rank() over (partition by submission_date order by count(*) desc,h.hacker_id ) as rk1 from hackers h inner join submissions s on h.hacker_id=s.hacker_id group by submission_date,h.hacker_id,name ), tt as ( select t.*, dense_rank() over (partition by submission_date order by br desc,hacker_id ) as rk, count( hacker_id) over (partition by submission_date) as broj from t where rbr=datediff(day,'2016-03-01',submission_date)+1 ) select t.submission_date,tt.broj,t.hacker_id,t.name from t inner join tt on t.submission_date=tt.submission_date where tt.rk=1 and t.rk1=1 order by 1
+ 0 comments MySQL
SELECT b.submission_date, COALESCE(a.contSub,0), b.id, c.name FROM ( SELECT o.submission_date, MIN(o.hacker_id) AS id FROM ( SELECT n.submission_date, n.hacker_id,COUNT(n.submission_id) FROM Submissions n GROUP BY submission_date, hacker_id HAVING COUNT(submission_id)>= ALL( SELECT COUNT(m.submission_id) FROM Submissions m GROUP BY m.submission_date, m.hacker_id HAVING m.submission_date=n.submission_date ) ) AS o GROUP BY submission_date ) AS b LEFT JOIN ( SELECT aa.submission_date, COUNT(DISTINCT aa.hacker_id) AS contSub FROM Submissions AS aa WHERE aa.submission_date='2016-03-01' OR submission_date<ALL( SELECT bb.submission_date FROM ( SELECT tdy.hacker_id, tdy.submission_date, ytd.submission_date AS SubmittedYtd FROM Submissions tdy LEFT JOIN Submissions ytd ON DATE_ADD(tdy.submission_date, INTERVAL -1 DAY)= ytd.submission_date AND ytd.hacker_id=tdy.hacker_id ) AS bb WHERE aa.hacker_id=bb.hacker_id AND bb.submission_date<> STR_TO_DATE('2016-03-01', '%Y-%m-%d') AND bb.SubmittedYtd IS NULL ) GROUP BY aa.submission_date ) AS a ON a.submission_date=b.submission_date JOIN Hackers c ON b.id=c.hacker_id ORDER BY a.submission_date ASC
+ 0 comments /* 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. / / 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 t1 as( SELECT s.submission_date,s.hacker_id,h.name, COUNT(submission_id) as cnt from submissions s inner join hackers h on h.hacker_id=s.hacker_id group by s.hacker_id,h.name,s.submission_date ), t2 as( select submission_date, hacker_id, row_number() over(partition by hacker_id order by submission_date) rn, dense_rank() over(order by submission_date) dr from t1 ), t3 as( select t2.submission_date,count() as cnt1 from t2 where t2.rn=t2.dr group by t2.submission_date ), t4 as( Select submission_date,hacker_id,name from (select submission_date,hacker_id,name, row_number() over(partition by submission_date order by cnt desc,hacker_id) rn from t1)as t where t.rn=1 ) select t3.submission_date, t3.cnt1,t4.hacker_id,t4.name from t3 inner join t4 on t3.submission_date=t4.submission_date order by t3.submission_date
https://)
+ 0 comments I love these kinds of questions that practice your logical thinking. You have to find out a way to filter out the rows you need. Once you have a practical idea, the rest is easy because no complex techques are required.
My code in MS SQL:
WITH -- count each hacker's number of submissions a AS ( SELECT DISTINCT s.hacker_id hid, s.submission_date sdt, COUNT(s.submission_id) csid FROM Submissions s GROUP BY s.submission_date, s.hacker_id ), -- find out hackers who submitted the most by date and select the one with smallest id b AS ( SELECT sdt, MIN(hid) mhid FROM a x WHERE csid = (SELECT MAX(csid) FROM a y WHERE x.sdt = y.sdt) GROUP BY sdt ), -- number each of the 15 days by order c AS ( SELECT sdt, ROW_NUMBER()OVER(ORDER BY sdt) rn FROM b ), -- filter out those who started submitting since the first day e AS ( SELECT hid, sdt, rn FROM (SELECT hid, a.sdt, rn, MIN(a.sdt)OVER(PARTITION BY hid) mm FROM a JOIN c ON a.sdt = c.sdt ) AS d WHERE mm = "2016-03-01" ), -- renumber each row by hacker id and date - for each person, when the row number equal to the day number, it is a consecutive submission. g AS ( SELECT * FROM (SELECT *, CASE WHEN rn = nrn THEN "COOL" ELSE "TRASH" END AS tag FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY hid ORDER BY hid, sdt) nrn FROM e) AS f) AS ff WHERE tag = "COOL" ), -- count number of hackers of each day after filtering out COOL rows i AS ( SELECT sdt, COUNT(hid) cc FROM g GROUP BY sdt ) -- combine table b and table i SELECT i.sdt, i.cc, b.mhid, h.name FROM i JOIN b ON b.sdt = i.sdt JOIN Hackers h ON h.hacker_id = b.mhid ORDER BY i.sdt
+ 0 comments 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 TMP, (SELECT NAME FROM HACKERS WHERE HACKER_ID = TMP) FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1 GROUP BY SUBMISSION_DATE;
Sort 700 Discussions, By:
Please Login in order to post a comment