15 Days of Learning SQL

Sort by

recency

|

1187 Discussions

|

  • + 0 comments

    This code is logically 100% but it sends me an error, can any one help ?!!?!!

     
    
    
    with daily_test as (
        select submission_date ,
            hacker_id ,
            count(submission_id) "subs_by_day" 
        from Submissions 
        group by 
            submission_date ,
            hacker_id
     ),
     
     unique_hackers as (
        select  submission_date,
                count(distinct hacker_id) "c_unique_hackers"
        from daily_test 
        group by submission_date
    ),
    
    rnkd_tests as (
        select * ,
                row_number() over ( partition by submission_date order by subs_by_day desc , hacker_id asc  ) "rnk"
        from daily_test 
    
    )
    ,max_subs as ( 
        select submission_date , 
             hacker_id    
      from rnkd_tests
      where rnk = 1
    
    )
    
    select distinct uh.submission_date,
            uh.c_unique_hackers,
            ms.hacker_id,
            h.name
    from unique_hackers uh
    join max_subs ms on uh.submission_date = ms.submission_date
    join Hackers h on ms.hacker_id = h.hacker_id
    order by uh.submission_date
    
  • + 0 comments

    Ended up working it out using 2 CTEs with window functions and subqueries.

    WITH evd_submissions AS(
        SELECT submission_date,
            hacker_id,
            COUNT(submission_date) OVER(PARTITION BY hacker_id ORDER BY submission_date) AS hacker_days,
            DENSE_RANK() OVER(ORDER BY submission_date) AS n_rank
        FROM submissions
        GROUP BY submission_date, hacker_id
        ),
        max_submissions AS (
        SELECT submission_date,
            MIN(hacker_id) AS hacker_id,
            submissions_count
            FROM(
                SELECT submission_date,
                    hacker_id,
                    submissions_count,
                    MAX(submissions_count) OVER(PARTITION BY submission_date) AS max_subs_count
                    FROM(
                        SELECT submission_date,
                            hacker_id,
                            COUNT(hacker_id) AS submissions_count
                        FROM submissions
                        GROUP BY submission_date, hacker_id
                        ) AS max_submissions
                    ) AS max_subs_days
                WHERE submissions_count = max_subs_count
                GROUP BY submission_date, submissions_count)
    
    SELECT t1.submission_date,
        COUNT(t1.hacker_id) AS hackers_count,
        t2.hacker_id,
        t3.name
    FROM evd_submissions AS t1
    INNER JOIN max_submissions AS t2
    ON t1.submission_date = t2.submission_date
    INNER JOIN hackers AS t3
    ON t2.hacker_id = t3.hacker_id
    WHERE hacker_days = n_rank
    GROUP BY t1.submission_date, t2.hacker_id, t3.name
    ORDER BY t1.submission_date
    ;
    
  • + 0 comments

    with cons_submission as ( select h.hacker_id,h.name,s.submission_date, dense_rank() over (partition by submission_date,h.hacker_id order by submission_date asc) as cons_sub from hacker h inner join submissions s on h.hacker_id = s.hacker_id),

    hacker_XV as ( select hacker_id,name from cons_submission group by hacker_id,name having max(cons_sub) = 15),

    hackers_fltrd as ( select cs.hacker_id,cs.name,cs.submission_date, count(*) as submitted_count from cons_submission cs inner join hacker_XV hxv on cs.hacker_id = hxv.hacker_id group by hacker_id,name,submission_date ),

    cte as ( select hacker_id,name,submission_date,submitted_count, row_number() over (partition by submission_date order by submitted_count desc, hacker_id asc ) as row_no from hackers_fltrd )

    select submission_date,submitted_count,hacker_id,name from cte where row_no = 1;

    OUTPUT: 2016-03-01 112 81314 Denise 2016-03-02 59 39091 Ruby 2016-03-03 51 18105 Roy 2016-03-04 49 533 Patrick 2016-03-05 49 7891 Stephanie 2016-03-06 49 84307 Evelyn 2016-03-07 35 80682 Deborah 2016-03-08 35 10985 Timothy 2016-03-09 35 31221 Susan 2016-03-10 35 43192 Bobby 2016-03-11 35 3178 Melissa 2016-03-12 35 54967 Kenneth 2016-03-13 35 30061 Julia 2016-03-14 35 32353 Rose 2016-03-15 35 27789 Helen still wrong

  • + 0 comments

    The original version I wrote was very long, but after looking at other answers, I simplified it using the condition in the WHERE clause. Thank you very much!

    SELECT
        daily_continuous.submission_date,
        daily_continuous.continuous_hackers_count,
        daily_max.hacker_id,
        h.name
    FROM (
        SELECT
            s1.submission_date,
            COUNT(DISTINCT s1.hacker_id) AS continuous_hackers_count
        FROM Submissions AS s1
        WHERE (
            SELECT
                COUNT(DISTINCT s2.submission_date)
            FROM Submissions AS s2
            WHERE s2.hacker_id = s1.hacker_id
            AND s2.submission_date < s1.submission_date
        ) = DATEDIFF(s1.submission_date, '2016-03-01')
        GROUP BY s1.submission_date
    ) AS daily_continuous
    
    JOIN (
        SELECT
            daily_max_hackers.submission_date,
            MIN(daily_max_hackers.hacker_id) AS hacker_id
        FROM (
            SELECT
                daily_hacker_data.submission_date,
                daily_hacker_data.hacker_id
            FROM (
                SELECT
                    submission_date,
                    hacker_id,
                    COUNT(*) AS sub_count
                FROM Submissions
                GROUP BY submission_date, hacker_id
            ) AS daily_hacker_data
            JOIN (
                SELECT
                    daily_hacker_data.submission_date,
                    MAX(daily_hacker_data.sub_count) AS sub_max
                FROM (
                    SELECT
                        submission_date,
                        hacker_id,
                        COUNT(*) AS sub_count
                    FROM Submissions
                    GROUP BY submission_date, hacker_id
                ) AS daily_hacker_data
                GROUP BY submission_date
            ) AS daily_sub_max
            ON daily_hacker_data.submission_date = daily_sub_max.submission_date
            AND daily_hacker_data.sub_count = daily_sub_max.sub_max
        ) AS daily_max_hackers
        GROUP BY daily_max_hackers.submission_date
    ) AS daily_max
    ON daily_continuous.submission_date = daily_max.submission_date
    JOIN Hackers AS h ON h.hacker_id = daily_max.hacker_id
    ORDER BY daily_continuous.submission_date;
    
  • + 0 comments

    Correct working solution broken down biy by bit; \n with daily_count as (select submission_date,hacker_id,count() as num_sub from submissions where submission_date between to_date('March 01, 2016','month dd, yyyy') and to_date('March 15, 2016','month dd, yyyy') group by submission_date,hacker_id), daily_user as (select submission_date,hacker_id,to_number(to_char(submission_date, 'dd')) as day,row_number() over (partition by hacker_id order by submission_date) as rn from daily_count), daily_user_count as (select submission_date,count() as user_count from daily_user where day=rn group by submission_date), max_user as (select submission_date,hacker_id,rank() over (partition by submission_date order by num_sub desc,hacker_id asc) as rank from daily_count), daily_max_user as (select max_user.submission_date,hackers.hacker_id,hackers.name from max_user join hackers on max_user.hacker_id=hackers.hacker_id where rank=1) select daily_user_count.submission_date,daily_user_count.user_count,daily_max_user.hacker_id,daily_max_user.name from daily_user_count join daily_max_user on daily_user_count.submission_date=daily_max_user.submission_date;