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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. 15 Days of Learning SQL
  5. Discussions

15 Days of Learning SQL

Sort by

recency

|

1117 Discussions

|

  • joaopaulosouzam1
     + 0 comments

    Good solution using recursion: 

    with funnel as (
    select
        submission_date as cur,
        hacker_id
    from 
        submissions
    where 
        1=1
        and submission_date = (select min(submission_date) from submissions)
    
    union all 
    
    select
        dateadd(day, 1, f.cur) as cur,
        s.hacker_id
    from   
        funnel f
    join 
        submissions s 
        on s.hacker_id = f.hacker_id 
        and s.submission_date = dateadd(day, 1, f.cur)
),

funnel_counting as (
    select
        cur as submission_date,
        count(distinct hacker_id) as hackers_funnel
    from 
        funnel
    group by cur
),

submissions_hacking as (
    select
        submission_date,
        hacker_id,
        count(submission_id) as submissions
    from 
        submissions 
    group by submission_date, hacker_id
),

submissions_rn as (
    select
        submission_date,
        hacker_id,
        row_number() over (partition by submission_date order by submissions desc, hacker_id asc) as rn 
    from    
        submissions_hacking
)

select
    fc.submission_date,
    fc.hackers_funnel,
    sr.hacker_id,
    h.name as hacker_name
from 
    funnel_counting fc
join 
    submissions_rn sr
    on fc.submission_date = sr.submission_date
join 
    hackers h
    on sr.hacker_id = h.hacker_id
where
    1=1
    and sr.rn = 1
order by fc.submission_date
option(maxrecursion 0);

  • sohyanzhang24
     + 0 comments

    -- Using MS SQL Server

    -- get daily submissions per hacker with cte as ( SELECT submission_date, hacker_id, count(distinct submission_id) as submissions FROM submissions GROUP BY submission_date, hacker_id )

    -- get submission days per hacker and rank the hackers by submissions made each day in descending order , cte2 as ( SELECT submission_date, hacker_id, submissions, count(submission_date) OVER (PARTITION BY hacker_id order by submission_date rows between unbounded preceding and current row) AS submission_days, datediff(day, cast('2016-03-01' as date), submission_date) + 1 as days_elapsed, rank() over (partition by submission_date order by submissions desc, hacker_id) as max_submissions_rank FROM cte )

    -- get the number of users who have been submitting daily since the start of the challenge and the hackerid with the maximum submissions per day , cte3 as ( select submission_date, count(distinct case when days_elapsed = submission_days then hacker_id end) as daily_submit_hacker_cnt, max(case when max_submissions_rank = 1 then hacker_id end) as max_submissions_hacker_id from cte2 group by submission_date )

    -- get the name of the hacker who has maximum submissions per day select submission_date, daily_submit_hacker_cnt, max_submissions_hacker_id, name as max_submissions_hacker_name from cte3 left join hackers on cte3.max_submissions_hacker_id = hackers.hacker_id order by submission_date;

  • shinthantaun99
     + 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.
## */
## WITH 
## data AS(
##     SELECT * 
##     FROM Submissions
##     WHERE submission_date BETWEEN
##     '2016-03-01' AND '2016-03-15'
## ),
## daily_subm_cnt AS(
##     SELECT hacker_id, submission_date,
##     COUNT(*) subm_cnt
##     FROM data
##     GROUP BY hacker_id, submission_date
## ),
## accum_subm_cnt AS(
##     SELECT submission_date,
##     hacker_id,
##     subm_cnt,
##     SUM(1) OVER
##     (PARTITION BY hacker_id 
##      ORDER BY submission_date) accum_cnt
##     FROM daily_subm_cnt
## ),
## atl_one AS(
##     SELECT submission_date, subm_cnt, hacker_id, accum_cnt
##     FROM accum_subm_cnt
##     WHERE accum_cnt > DATEDIFF(DAY, '2016-03-01', submission_date)
## ),
## ranked_cnt AS(
##     SELECT submission_date, subm_cnt, hacker_id, accum_cnt,
##     RANK() OVER(
##         PARTITION BY submission_date 
##         ORDER BY subm_cnt DESC) cnt_rnk
##     FROM accum_subm_cnt
## ),
## ranked_hacker AS(
##     SELECT submission_date, subm_cnt, hacker_id, cnt_rnk,
##     RANK() OVER(
##         PARTITION BY submission_date
##         ORDER BY hacker_id ASC) id_rnk
##     FROM ranked_cnt
##     WHERE cnt_rnk = 1
## ),
## daily_hacker_cnt AS(
##     SELECT submission_date, COUNT(DISTINCT(hacker_id)) hacker_cnt
##     FROM atl_one
##     GROUP BY submission_date
## )
## 
## SELECT dhc.submission_date, dhc.hacker_cnt,
## rh.hacker_id, h.name
## FROM daily_hacker_cnt dhc
## JOIN ranked_hacker rh
## ON rh.submission_date = dhc.submission_date
## AND rh.id_rnk = 1
## JOIN Hackers h
## ON h.hacker_id = rh.hacker_id;**

  • hp2911
     + 1 comment

    With MySql, window functions do not work in this one. Am I right?. I could not make them work in this one.

  • timrpalmer
     + 0 comments

    This one was difficult. I found a discussion that answered a lot of problems I had with this one. This is what they are really looking for. pretty easy except for the "tracking the people that did it everyday" part. I had to look for help and then ended up creating a buffer to count the everyday people. I had to look at it several times to figure out a workable solution. You have to iterate every day sequentially, and update the everyday people buffer. Once you can nest that in there, it's not so hard.

    "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