15 Days to learn SQL Hard SQL(Advanced)- Solution SQL, Best and Optimal Solutions, All you need.
Solutions of SQL all HackerRank:
Solution – 15 Days of Learning SQL
MySQL
SELECT t1.submission_date, hkr_cnt, t2.hacker_id, name FROM (SELECT p1.submission_date, COUNT(DISTINCT p1.hacker_id) AS hkr_cnt FROM (SELECT submission_date, hacker_id, @h_rnk := CASE WHEN @h_grp != hacker_id THEN 1 ELSE @h_rnk+1 END AS hacker_rank, @h_grp := hacker_id AS hacker_group FROM (SELECT DISTINCT submission_date, hacker_id FROM submissions ORDER BY hacker_id, submission_date) AS a, (SELECT @h_rnk := 1, @h_grp := 0) AS r) AS p1 JOIN (SELECT submission_date, @d_rnk := @d_rnk + 1 AS date_rank FROM (SELECT DISTINCT submission_date FROM submissions ORDER BY submission_date) AS b, (SELECT @d_rnk := 0) r) AS p2 ON p1.submission_date = p2.submission_date AND hacker_rank = date_rank GROUP BY p1.submission_Date) AS t1 JOIN (SELECT submission_date, hacker_id, sub_cnt, @s_rnk := CASE WHEN @d_grp != submission_date THEN 1 ELSE @s_rnk+1 END AS max_rnk, @d_grp := submission_date AS date_group FROM (SELECT submission_date, hacker_id, COUNT(*) AS sub_cnt FROM submissions AS s GROUP BY submission_date, hacker_id ORDER BY submission_date, sub_cnt DESC, hacker_id) AS c, (SELECT @s_rnk := 1, @d_grp := 0) AS r) AS t2 ON t1.submission_date = t2.submission_date AND max_rnk = 1 JOIN hackers AS h ON h.hacker_id = t2.hacker_id ORDER BY t1.submission_date ;
Here are all the Solutions of All SQL of Hacker Rank, Leave a comment for similar posts
SQL 15 Days to learn SQL HardSQL (Advanced) HackerRank Solution
with Sub1 as (
select s1.submission_date, s1.hacker_id,
count (distinct s1.submission_id) as date_submissions,
1 + datediff(day, 'March 1, 2016', s1.submission_date) as contest_day,
count (distinct s2.submission_date) as submission_days
from Submissions s1 join Submissions s2
on s1.hacker_id = s2.hacker_id and s1.submission_date >= s2.submission_date
group by s1.submission_date, s1.hacker_id
),
Sub2 as (
select submission_date, hacker_id, date_submissions
from Sub1 where submission_days = contest_day
),
Sub3a as (
select submission_date, count(hacker_id) as hackers
from Sub2 group by submission_date
),
Sub3b as (
select submission_date, max(date_submissions) as max_submissions
from Sub1 group by submission_date
),
Sub4 as (
select s1.submission_date, s3.hackers,
(select top 1 s2.hacker_id from Sub1 s2
where s1.submission_date = s2.submission_date and s1.max_submissions = s2.date_submissions
order by s2.hacker_id) as hacker_id
from Sub3b s1
join Sub3a s3 on s1.submission_date = s3.submission_date
)
select s.*, h.name from Sub4 s join Hackers h on s.hacker_id = h.hacker_id order by submission_date;
SQL (Basic)
SQL (Intermediate)
SQL (Advanced)
Basic Select
Advanced Select
Aggregation
Basic Join
Advanced Join
Alternative Queries
Leave a comment below