HackerRank 15 Days of Learning SQL.sql

set search_path to data_sci


create table hackers(
hacker_id integer generated by default as identity,
name TEXT)

insert into hackers
(name)
(select concat('Name', num::text) FROM
 	generate_series(1,10) as num)
	
create TABLE submissions(
sub_id integer generated by default as identity,
hacker_id integer,
submission_date date)


insert into submissions
(hacker_id, submission_date)
(select trunc(random()*10+1),
	('2016-03-01'::date + concat((trunc(random()*15))::text, ' day')::interval)::date
	FROM
	generate_series(1,400,1)
 )


select hacker_id, count(submission_date) 
from submissions
group by hacker_id
order  by hacker_id

select submission_date, count(hacker_id) 
from submissions
group by submission_date
order  by submission_date

select hacker_id, count(hacker_id) 
from submissions
where submission_date ='2016-03-08'
group by hacker_id
order  by 2 desc, 1 ASC
--6,2,5,1,1,5,1,6
-- From this group that completed 15 days
-- 1,2,5,6,9

-- Julia conducted a 15 days of learning SQL contest. The start date of the contest was 
-- March 01, 2016 and the end date was March 15, 2016.
-- Write a query to print total number of unique hackers who made at least 1 submission 
-- each day (starting on the first day of the contest), and find the hacker_id and 
-- name of the hacker who made maximum number of submissions each day. If more than one 
-- such hacker has a maximum number of submissions, print the lowest hacker_id. 
--The query should print this information for each day of the contest, sorted by the date.


with hacker_15 as(
	SELECT hacker_id
	--, count(distinct submission_date) as distinct_date_count
	from submissions
	group by hacker_id
	having count(distinct submission_date) = 15
	order by hacker_id
	),	
top_hacker_for_day as (
	select distinct s.submission_date as sub_date,
	first_value(s.hacker_id) over 
	  (partition by s.submission_date order by count(s.hacker_id) desc, s.hacker_id ASC) as h1
	from submissions s
	inner join hacker_15 h15 on s.hacker_id = h15.hacker_id
	group by s.submission_date, s.hacker_id
)
select ts.sub_date, ts.h1, h.name
from top_hacker_for_day ts
inner join hackers h on h.hacker_id = ts.h1
order by ts.sub_date