In this HackerRank 15 Days of Learning SQL problem solution, 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.
Problem solution MS SQL.
/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
with a as (
select distinct hacker_id, submission_date from submissions
), b as (
select hacker_id, submission_date, count(*) over (partition by hacker_id order by submission_date rows UNBOUNDED PRECEDING) cc
from a
) , c as (
select * from b
where day(submission_date) = cc
) , p1 as (
select c.submission_date, count(*) cc
from c
group by c.submission_date
), d as (
select s.hacker_id, s.submission_date, count(*) num_sub
from submissions s
/* join c on c.submission_date=s.submission_date and c.hacker_id=s.hacker_id */
group by s.hacker_id, s.submission_date
) , p2 as (
select distinct submission_date, first_value(hacker_id) over (partition by submission_date order by num_sub desc, hacker_id) fv
from d
), f as (
select p1.submission_date, cc, fv, name
from p1
join p2 on p1.submission_date=p2.submission_date
join hackers h on h.hacker_id=p2.fv
)
select submission_date, cc, fv, name from f
order by submission_date
Problem solution in Oracle.
select cont.submission_date, dis, hacker_id, NAME from
(SELECT submission_date, COUNT(DISTINCT hacker_id) dis
FROM (SELECT submission_date, hacker_id
FROM (SELECT h.hacker_id, h.name, submission_date
FROM hackers h, submissions s
WHERE h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name, submission_date) h
CONNECT BY PRIOR hacker_id = hacker_id
AND PRIOR submission_date + 1 = submission_date
start with to_char(submission_date,'DD-MON-YYYY') = '01-MAR-2016')
group by submission_date) cont,
(SELECT submission_date, hacker_id, NAME
FROM (SELECT submission_date, MIN(hacker_id) hid
FROM (SELECT submission_date, hacker_id
FROM (SELECT submission_date, hacker_id, sub,
MAX(sub) over(PARTITION BY submission_date) mx
FROM (SELECT submission_date, hacker_id, COUNT(1) sub
FROM submissions
GROUP BY submission_date, hacker_id))
WHERE sub = mx)
group by submission_date) f, hackers h WHERE f.hid = h.hacker_id) topr
where cont.submission_date = topr.submission_date
order by cont.submission_date;
Problem solution in DB2.
/*
Enter your query here and follow these instructions:
1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"
3. Type your code immediately after comment. Don't leave any blank line.
*/
select su.SUBMISSION_DATE,
(select count(distinct(su1.HACKER_ID))
from SUBMISSIONS su1
where su1.SUBMISSION_DATE=su.SUBMISSION_DATE
and (
select count(distinct(su2.SUBMISSION_DATE))
from SUBMISSIONS su2
where su2.SUBMISSION_DATE<=su1.SUBMISSION_DATE
and su2.HACKER_ID=su1.HACKER_ID
)-1 = days(su1.SUBMISSION_DATE)-days('2016-03-01')
),
(select su1.HACKER_ID
from SUBMISSIONS su1
where su1.SUBMISSION_DATE=su.SUBMISSION_DATE
group by su1.HACKER_ID
order by count(1) desc, su1.HACKER_ID asc
fetch first 1 rows only
),
(select max(ha.NAME)
from SUBMISSIONS su1
left join HACKERS ha on ha.HACKER_ID=su1.HACKER_ID
where su1.SUBMISSION_DATE=su.SUBMISSION_DATE
group by su1.HACKER_ID
order by count(1) desc, su1.HACKER_ID asc
fetch first 1 rows only
)
from SUBMISSIONS su
group by su.SUBMISSION_DATE;
0 Comments