museyib

15 Days of Learning SQL (Submission)

Mar 13th, 2021 (edited)
1,162
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with dates as (
  2.     select '2016-03-01' challenge_date union
  3.     select '2016-03-02' challenge_date union
  4.     select '2016-03-03' challenge_date union
  5.     select '2016-03-04' challenge_date union
  6.     select '2016-03-05' challenge_date union
  7.     select '2016-03-06' challenge_date union
  8.     select '2016-03-07' challenge_date union
  9.     select '2016-03-08' challenge_date union
  10.     select '2016-03-09' challenge_date union
  11.     select '2016-03-10' challenge_date union
  12.     select '2016-03-11' challenge_date union
  13.     select '2016-03-12' challenge_date union
  14.     select '2016-03-13' challenge_date union
  15.     select '2016-03-14' challenge_date union
  16.     select '2016-03-15' challenge_date
  17. )
  18. select d.challenge_date, s.hacker_count, h1.hacker_id, h.name from dates d
  19. left join (
  20.     select
  21.         s_in.submission_date,
  22.         count(distinct s_in.hacker_id) hacker_count
  23.     from Submissions s_in
  24.     join (
  25.         select hacker_id,submission_date from(
  26.             select
  27.                 submission_date,
  28.                 hacker_id,
  29.                 row_number() over(partition by hacker_id order by submission_date) row_num
  30.             from (select distinct hacker_id, submission_date from Submissions) T) T
  31.         where row_num=cast(right(cast(submission_date as varchar),2) as int)
  32.     ) uniq_h on s_in.hacker_id=uniq_h.hacker_id and s_in.submission_date=uniq_h.submission_date
  33.     group by s_in.submission_date
  34. ) s on d.challenge_date=s.submission_date
  35. left join (
  36.     select * from (
  37.         select
  38.             submission_date,
  39.             hacker_id,
  40.             row_number() over(partition by submission_date order by submission_count desc, hacker_id) row_num
  41.         from(
  42.             select submission_date,
  43.                     hacker_id,
  44.                     count(*) submission_count
  45.             from Submissions
  46.             group by submission_date, hacker_id
  47.         ) T
  48.     ) T
  49. )h1 on d.challenge_date=h1.submission_date and h1.row_num=1
  50. join Hackers h on h1.hacker_id=h.hacker_id
  51. order by d.challenge_date
RAW Paste Data