Hackerrank(medium and hard)

15 Days of Learning SQL

SELECT SUBMISSION_DATE,
(SELECT COUNT(DISTINCT HACKER_ID)  
 FROM SUBMISSIONS S2  
 WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE AND    
(SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
 FROM SUBMISSIONS S3 WHERE S3.HACKER_ID = S2.HACKER_ID AND S3.SUBMISSION_DATE < S1.SUBMISSION_DATE) = DATEDIFF(S1.SUBMISSION_DATE , '2016-03-01')),
(SELECT HACKER_ID FROM SUBMISSIONS S2 WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE 
GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS TMP,
(SELECT NAME FROM HACKERS WHERE HACKER_ID = TMP)
FROM
(SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1
GROUP BY SUBMISSION_DATE;

The PADS

SELECT concat(NAME,concat("(",concat(substr(OCCUPATION,1,1),")"))) FROM OCCUPATIONS ORDER BY NAME ASC;

SELECT "There are a total of ", count(OCCUPATION), concat(lower(occupation),"s.") FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY count(OCCUPATION), OCCUPATION ASC

OCCUPATIONS

select 
    min(doctor) d, min(professor) p, min(singer) s, min(actor) a
    from 
    (
        SELECT 
        CASE WHEN Occupation = 'Doctor' THEN name END AS Doctor , 
        CASE WHEN Occupation = 'Professor' THEN name END AS Professor , 
        CASE WHEN Occupation = 'Singer' THEN name END AS Singer , 
        CASE WHEN Occupation = 'Actor' THEN name END AS Actor , 
        RANK() OVER (PARTITION BY Occupation order by name) AS row_rank 
        FROM 
        Occupations 
    )x 
    group by row_rank
    order by row_rank;

Binary Tree Nodes

SELECT N, IF(P IS NULL,'Root',IF((SELECT COUNT(*) FROM BST WHERE P=B.N)>0,'Inner','Leaf')) FROM BST AS B ORDER BY N;

New Companies

select c.company_code, c.founder, 
    count(distinct l.lead_manager_code), count(distinct s.senior_manager_code), 
    count(distinct m.manager_code),count(distinct e.employee_code) 
from Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e 
where c.company_code = l.company_code 
    and l.lead_manager_code=s.lead_manager_code 
    and s.senior_manager_code=m.senior_manager_code 
    and m.manager_code=e.manager_code 
group by c.company_code,c.founder order by c.company_code;

Weather Observation Station 18

select round(max(lat_n)- min(lat_n) + max(long_w)-min(long_w),4) from station;

Weather Observation Station 19

select round(sqrt(pow(max(LAT_N)-min(LAT_N),2)+pow(max(LONG_W)-min(LONG_W),2)),4) from STATION;

Weather Observation Station 20

SELECT ROUND(LAT_N,4) FROM STATION s
WHERE (SELECT COUNT(LAT_N) FROM STATION WHERE LAT_N < s.LAT_N) = (SELECT COUNT(LAT_N) FROM STATION WHERE LAT_N > s.LAT_N) 

The Report

select if(grades.grade > 7, students.name, null), grades.grade, students.marks 
from students, grades 
where marks between min_mark and max_mark 
order by grade desc, name;

Top Competitors

select h.hacker_id, h.name
from submissions s
inner join challenges c
on s.challenge_id = c.challenge_id
inner join difficulty d
on c.difficulty_level = d.difficulty_level 
inner join hackers h
on s.hacker_id = h.hacker_id
where s.score = d.score and c.difficulty_level = d.difficulty_level
group by h.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

Ollivander’s Inventory

SELECT A.myid,A.age,A.coins_needed,A.power from 
    (SELECT w1.id as myid,age,coins_needed,power,
            row_number() OVER(PARTITION BY age,power 
                             ORDER BY coins_needed asc) as rn
    FROM wands w1 inner join 
    wands_property w2 on w1.code = w2.code
    where is_evil = 0) A
where A.rn = 1
order by power desc, age desc;

Challenges

select c.hacker_id, h.name ,count(c.hacker_id) as c_count
from Hackers as h
inner join Challenges as c on c.hacker_id = h.hacker_id
group by c.hacker_id,h.name
having 
    c_count = 
        (SELECT MAX(temp1.cnt)
         from (SELECT COUNT(hacker_id) as cnt
             from Challenges
             group by hacker_id
             order by hacker_id) temp1)
    or c_count in 
        (select t.cnt
         from (select count(*) as cnt 
               from challenges
               group by hacker_id) t
         group by t.cnt
         having count(t.cnt) = 1)
order by c_count DESC, c.hacker_id
;

Contest Leaderboard

select h.hacker_id, name, sum(score) as total_score
from hackers as h inner join
(select hacker_id,  max(score) as score from submissions group by challenge_id, hacker_id) max_score
on h.hacker_id=max_score.hacker_id
group by h.hacker_id, name
having total_score > 0
order by total_score desc, h.hacker_id;

SQL Project Planning

SELECT Start_Date, MIN(End_Date)
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date) ASC, Start_Date ASC;

Placements

select s.name from students s, friends f, packages p, packages p2 
where s.id = f.id and
 f.friend_id = p2.id and 
s.id = p.id and
 p.salary < p2.salary
 order by p2.salary;

Symmetric Pairs

SELECT f1.X, f1.Y FROM Functions f1
INNER JOIN Functions f2 ON f1.X=f2.Y AND f1.Y=f2.X
GROUP BY f1.X, f1.Y
HAVING COUNT(f1.X)>1 or f1.X<f1.Y
ORDER BY f1.X 

Interviews

select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_id 
left join
(select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
from view_stats group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left join
(select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions from submission_stats group by challenge_id) ss on cha.challenge_id = ss.challenge_id
    group by con.contest_id, con.hacker_id, con.name
        having sum(total_submissions)!=0 or 
                sum(total_accepted_submissions)!=0 or
                sum(total_views)!=0 or
                sum(total_unique_views)!=0
            order by contest_id;