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

|

1089 Discussions

|

  • wkowalcz
     + 0 comments

    simple, clean solution:

    SELECT Submission_Date
     , Submission_Count
     , BestHacker_Id
     , (SELECT Name
          FROM Hackers
         WHERE Hacker_Id = BestHacker_Id) Name
  FROM (SELECT Submission_Date
             , SUM(Submission_Count) Submission_Count
             , MIN(BestHacker_Id) BestHacker_Id
          FROM (SELECT Hacker_Id
                     , Submission_Date
                     , CASE WHEN MAX(Submission_No) OVER (PARTITION BY Submission_Date) = Submission_No
                         THEN Hacker_Id END BestHacker_Id
                     , CASE WHEN COUNT(*) OVER (PARTITION BY Hacker_Id ORDER BY Submission_Date) + DATE'2016-03-01' - 1 = Submission_Date 
                         THEN 1 ELSE 0 END Submission_Count
                  FROM (SELECT h.Hacker_Id
                             , s.Submission_Date
                             , COUNT(*) Submission_No
                          FROM Hackers h
                             , Submissions s
                         WHERE h.Hacker_Id = s.Hacker_Id
                         GROUP BY h.Hacker_Id
                             , s.Submission_Date))
         GROUP BY Submission_Date)
 ORDER BY Submission_Date;

  • shreeyanshi0109
     + 0 comments

    What is wrong with the following?

    with cte as( select submission_date, hacker_id, count(*) as no_of_submissions, dense_rank() over(partition by submission_date order by submission_date) as day_number from Submissions group by submission_date, hacker_id), cte2 as( select , count() over(partition by hacker_id order by submission_date) as till_date_submission, case when day_number = count(*) over(partition by hacker_id order by submission_date) then 1 else 0 end as unique_flag from cte ),cte3 as( select *, sum(unique_flag) over(partition by submission_date) as unique_count, row_number() over(partition by submission_date order by no_of_submissions desc, hacker_id) as rn from cte2 ) select submission_date,unique_count,c.hacker_id,name from cte3 c join Hackers h on c.hacker_id = h.hacker_id where rn = 1 order by submission_date;

  • cesarvizo_viz
     + 0 comments

    What is wrong with the following?

    WITH tot_subm AS (``
    SELECT 
        s.submission_date,
        COUNT(s.submission_id) AS total_submissions,
        h.hacker_id,
        h.name,
        ROW_NUMBER() OVER (
            PARTITION BY s.submission_date 
            ORDER BY COUNT(s.submission_id) DESC, h.hacker_id ASC
        ) AS r_numb
    FROM Hackers h
    INNER JOIN Submissions s ON s.hacker_id = h.hacker_id
    WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15'
    GROUP BY s.submission_date, h.hacker_id, h.name
)
SELECT 
    submission_date, 
    total_submissions, 
    hacker_id, 
    name        
FROM tot_subm    
WHERE r_numb = 1
ORDER BY submission_date;

  • phoenix007saha
     + 0 comments

    whats wrong in this?

    With daily_submission AS (select submission_date,hacker_id,COUNT(*) as submissions from Submissions where submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY submission_date,hacker_id), Daily_top_hacker AS (select submission_date,hacker_id,RANK()OVER(PARTITION BY submission_date ORDER BY submissions DESC,hacker_id) AS rank FROM daily_submission) SELECT dts.submission_date, COUNT(DISTINCT dts.hacker_id) AS unq_hackers, dth.hacker_id, h.name from daily_submission dts INNER JOIN Daily_top_hacker dth ON dts.submission_date = dth.submission_date AND dts.hacker_id=dth.hacker_id AND dth.rank=1 INNER JOIN Hackers h ON dth.hacker_id=h.hacker_id GROUP BY dts.submission_date,dth.hacker_id,h.name ORDER BY dts.submission_date;

  • debjyoti_ghosh
     + 0 comments
    with 
    cte_01 as
(
    select 
        submission_date,
        hacker_id,
        count(*) no_of_submissions,
        dense_rank() over (order by submission_date) day_count
    from
        Submissions
    group by 
        submission_date,
        hacker_id    
),
    cte_02 as
(
    select 
        *,
        count(*) over (partition by hacker_id order by submission_date) till_date_submissions,
        case when day_count = count(*) over (partition by hacker_id order by submission_date) then 1 else 0 end as unique_flag
    from cte_01
),
    cte_03 as
(
    select
        *,
        sum(unique_flag) over (partition by submission_date) unique_count,
        row_number() over (partition by submission_date order by no_of_submissions desc, hacker_id) rn
    from cte_02
),
    cte_04 as
(
select * from cte_03
where rn = 1
)
select 
    ct.submission_date,
    ct.unique_count,
    ct.hacker_id,
    hck.name
from
    cte_04 ct
left join Hackers hck on ct.hacker_id = hck.hacker_id
order by
    ct.submission_date,
    ct.hacker_id;