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

|

1187 Discussions

|

  • mohabdev25
     + 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

  • j_ibarralemp
     + 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
;

  • antsrubenecia
     + 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

  • BlurFeng
     + 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;

  • sandeep_baradi
     + 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;