Hackerrank-sql

995 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 as C,
                                 Lead_Manager as L,
                                 Senior_Manager as S,
                                 Manager as M,
                                 Employee as E 
                            WHERE E.manager_code = M.manager_code 
                                  AND M.senior_manager_code = S.senior_manager_code
                                  AND L.lead_manager_code = S.lead_manager_code
                                  AND C.company_code = L.company_code
                            group by C.company_code, C.founder
                            order by C.company_code
                            
                            
                   -----------------------------OR -----------------------------
                   
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 20:     
 select round(s.lat_n, 4) from station as s where ( (select count(lat_n) from station where s.lat_n >= lat_n) - (select count(lat_n) % 2 from station) = (select count(lat_n) from station where s.lat_n < lat_n) )


SQL Project Planning :

SET sql_mode = '';
select start_date , 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(end_date,start_date),start_date
                        
Contest Leaderboard:                       
select h.hacker_id, name, sum(score) as total_score
from
hackers as h inner join
/* find max_score*/
(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

/* don't accept hackers with total_score=0 */
having total_score > 0

/* finally order as required */
order by total_score desc, h.hacker_id
;


create table challenges(challenge_id integer, hacker_id integer);

insert into challenges values(61654, 5077);
insert into challenges values(58302, 21283);
insert into challenges values(40587, 88255);
insert into challenges values(29477, 5077);
insert into challenges values(1220, 21283);
insert into challenges values(69514, 21283);
insert into challenges values(46561, 62743);
insert into challenges values(58077, 62743);
insert into challenges values(18483, 88255);
insert into challenges values(76766, 21283);
insert into challenges values(52382, 5077);
insert into challenges values(74467, 21283);
insert into challenges values(33625, 96196);
insert into challenges values(26053, 88255);
insert into challenges values(42665, 62743);
insert into challenges values(12859, 62743);
insert into challenges values(70094, 21283);
insert into challenges values(34599, 88255);
insert into challenges values(54680, 88255);
insert into challenges values(61881, 5077);

create table hackers(hacker_id integer, name varchar(50));
INSERT INTO `hackers` (`hacker_id`,`name`) VALUES (5077,'Rose');
INSERT INTO `hackers` (`hacker_id`,`name`) VALUES (21283,'Angela');
INSERT INTO `hackers` (`hacker_id`,`name`) VALUES (62743,'Frank');
INSERT INTO `hackers` (`hacker_id`,`name`) VALUES (88255,'Patrick');
INSERT INTO `hackers` (`hacker_id`,`name`) VALUES (96196,'Lisa');


/* count total submissions of challenges of each user */
WITH data AS
(
SELECT c.hacker_id as id, h.name as name, count(c.hacker_id) as counter
FROM Hackers h
JOIN Challenges c on c.hacker_id = h.hacker_id
GROUP BY c.hacker_id, h.name
)
/* select records from above */
SELECT id,name,counter
FROM data
WHERE
counter=(SELECT max(counter) FROM data) /*select user that has max count submission*/
OR
counter in (SELECT counter FROM data
GROUP BY counter
HAVING count(counter)=1 ) /*filter out the submission count which is unique*/
ORDER BY counter desc, id



SELECT C.HACKER_ID, H.NAME, COUNT(C.HACKER_ID) AS CREATED
FROM HACKERS AS H
JOIN CHALLENGES AS C
ON H.HACKER_ID = C.HACKER_ID

/* This one line created the difference */
GROUP BY C.HACKER_ID, H.NAME
/* As soon as I included H.NAME in the group by statement, everything started running properly */

HAVING CREATED = (SELECT COUNT(C1.CHALLENGE_ID) AS CREATED1
                  FROM CHALLENGES AS C1
                  GROUP BY C1.HACKER_ID
                  ORDER BY CREATED1 DESC
                  LIMIT 1)
OR
       CREATED NOT IN (SELECT COUNT(C2.CHALLENGE_ID)
                       FROM CHALLENGES AS C2
                       GROUP BY C2.HACKER_ID
                       HAVING C.HACKER_ID != C2.HACKER_ID)

ORDER BY CREATED DESC, C.HACKER_ID