Discussion on 15 Days of Learning SQL Challenge

Sort by

recency

|

1135 Discussions

|

1 day ago+ 0 comments

WITH DailySubmissions AS ( -- Step 1: Unique hackers who submitted on each submission date SELECT DISTINCT s.submission_date, s.hacker_id FROM Submissions s ), SubmissionDates AS ( -- Step 2: Get all submission dates SELECT DISTINCT submission_date FROM Submissions ), UniqueHackersList AS ( -- Step 3: Count cumulative unique hackers who submitted on current & previous days SELECT sd.submission_date, ds.hacker_id, COUNT(*) AS hackers, (sd.submission_date - DATE '2016-03-01' + 1) AS days_past FROM SubmissionDates sd LEFT JOIN DailySubmissions ds ON sd.submission_date >= ds.submission_date GROUP BY sd.submission_date, ds.hacker_id ), AggSubmissions AS ( -- Step 4: Find the hacker with the most submissions each day SELECT s.submission_date, s.hacker_id, h.name, COUNT(s.submission_id) AS submissions, ROW_NUMBER() OVER (PARTITION BY s.submission_date ORDER BY COUNT(s.submission_id) DESC, s.hacker_id ASC) AS subs_rank FROM Submissions s JOIN Hackers h ON s.hacker_id = h.hacker_id GROUP BY s.submission_date, s.hacker_id, h.name ), UniqueHackers AS ( -- Step 5: Find the count of unique hackers who submitted every day from 2016-03-01 to the given date SELECT submission_date, COUNT(hacker_id) AS Unique_Hackers FROM UniqueHackersList WHERE hackers = days_past GROUP BY submission_date ) -- Final Selection SELECT uh.submission_date, uh.Unique_Hackers, subs.hacker_id, subs.name FROM UniqueHackers uh LEFT JOIN ( SELECT submission_date, hacker_id, name FROM AggSubmissions WHERE subs_rank = 1 ) subs ON uh.submission_date = subs.submission_date ORDER BY uh.submission_date;

2 days ago+ 0 comments

with cte as (
select submission_date,hacker_id,count(*) as day_cnt from Submissions group by submission_date,hacker_id 
    ),
cte2 as (
    select submission_date,hacker_id,count(hacker_id) over (partition by hacker_id order by submission_date asc rows between unbounded preceding and current row) cnt, datediff(day,'2016-02-29',submission_date) diff from cte
),
first_part as (
    select submission_date,count(hacker_id) cnt_dis from cte2 where cnt = diff
    group by submission_date
),
cte3 as (
    select submission_date,c.hacker_id,day_cnt,name,row_number() over (partition by submission_date order by day_cnt desc, c.hacker_id asc ) rw_num from 
    cte c join Hackers h on  c.hacker_id = h.hacker_id
)
select f.submission_date,f.cnt_dis,c.hacker_id,c.name from first_part f join cte3 c
on f.submission_date = c.submission_date
where rw_num = 1
order by submission_date``

5 days ago+ 0 comments

The key aspect of the task was to calculate eligible hackers who made submissions every day up to and including each specific day (i.e., cumulative submissions). We should check eligible on each day..not for whole range ... so if we have a 2016-03-05 date -> we should check if that hackers submitted from 2016-03-01 to 2016-03-05....e.t.c

5 days ago+ 0 comments

This exercise is extremely frustrating because of lack of compatibility. MS Sql does not allow CTEs and then the MS SQL sever has way too many requirements for a simple Rank(). This is definitely not compabitle with modern day scripting. The answer is a few lines of code with Postgre which they are not offering as a language to use. People here are running 4 CTEs, or 5 subqueries for a question which otherwise is simple.

5 days ago+ 0 comments

how come this problem has to be run with this version 5.7.27-0ubuntu0.18.04.1 . there is even no CTE and row_number.

Sort by

|

1135 Discussions

|

Cookie support is required to access HackerRank

Please signup or login in order to view this challenge