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.
15 Days of Learning SQL
15 Days of Learning SQL
Sort by
recency
|
954 Discussions
|
Please Login in order to post a comment
MS SQL Server:
with Principal as (Select hacker_id, submission_date, Count(hacker_id) Over (Partition by L.hacker_id Order by submission_date) as CountP From (Select Distinct hacker_id, submission_date from Submissions) L)
Select Z.submission_date, Final_Sub, hacker_id, Name From
(Select X.submission_date, Count(X.hacker_id) as Final_Sub From (SELECT S1.hacker_id, S1.submission_date FROM ((Select hacker_id, submission_date, CountP from Principal) S1 Inner JOIN (Select hacker_id, submission_date from Principal) S2 ON S1.hacker_id = S2.hacker_id And S1.submission_date = DATEADD(day, 1, S2.submission_date) and S1.CountP = Datepart(Day,S1.submission_date)) Union (Select hacker_id, submission_date from Submissions where DATEPART(Day, submission_date) = 1)) X Group by X.submission_date) Z Inner Join (Select Detail_Sub.submission_date, Detail_Sub.hacker_id, Detail_Sub.Count_Sub, Hackers.name From (Select submission_date, hacker_id, Count(submission_id) as Count_Sub, Row_Number() Over(Partition by submission_date Order by submission_date, Count(submission_id) Desc, hacker_id) as RowS From Submissions
Group by submission_date, hacker_id) Detail_Sub Inner join Hackers on Detail_Sub.hacker_id = Hackers.hacker_id Where RowS =1) Y on Z.submission_date = Y.submission_date Order by Z.submission_date
can anyone explain ? on the explantion of 1 : how 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.
also on the explanation 2 : on March 2 there were 3 unique users who submitted but the explanation shows its 2.. so I am confused.
can anyone explain ? on the explantion of 1 : how 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.
This problem is horribly worded, but the explanation does make it clear that it's asking for two pieces of information: 1) how many hackers consecutively submistted for each day 2) the hacker that has most submits each day (they don't need to submit consecutively)
WITH p1 AS ( SELECT submission_date, COUNT(DISTINCT hacker_id) AS cnt FROM ( SELECT submission_date, hacker_id, DENSE_RANK() OVER (PARTITION BY hacker_id ORDER BY submission_date ASC) AS sub_day FROM submissions ) s1 WHERE sub_day = DAY(submission_date) GROUP BY submission_date ),
p2 AS ( SELECT s3.submission_date, s3.hacker_id, h.name FROM ( SELECT submission_date, hacker_id, RANK() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id ASC) AS rnk FROM ( SELECT submission_date, hacker_id, COUNT(*) AS cnt FROM submissions GROUP BY submission_date, hacker_id ) s2 ) s3 INNER JOIN hackers h ON s3.hacker_id = h.hacker_id WHERE s3.rnk = 1 )
SELECT p1.submission_date, p1.cnt, p2.hacker_id, p2.name FROM p1 INNER JOIN p2 ON p1.submission_date = p2.submission_date ORDER BY p1.submission_date;