HackerRank SQL - Advanced Join (MySQL)

 

Solve SQL Code Challenges

 

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)) AS s,
(SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) AS e
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date;

 

 

Placements

SELECT SP.Name FROM
(SELECT S.ID, S.Name, P.Salary FROM Students S JOIN Packages P ON S.ID = P.ID) AS SP
JOIN
(SELECT F.ID, F.Friend_ID, P.Salary FROM Friends F JOIN Packages P ON F.Friend_ID = P.ID) AS FP
ON SP.ID = FP.ID
WHERE SP.Salary < FP.Salary
ORDER BY FP.Salary;

 

 

Symmetric Pairs

SELECT F1.X, F1.Y FROM
Functions F1 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 ct.contest_id, 
       ct.hacker_id,
       ct.name,
       SUM(total_submissions) AS ts,
       SUM(total_accepted_submissions) AS tas,
       SUM(total_views) AS tv,
       SUM(total_unique_views) AS tuv
FROM Contests ct 
   LEFT JOIN Colleges c ON ct.contest_id = c.contest_id
   LEFT JOIN Challenges ch ON c.college_id = ch.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) AS vs ON ch.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) AS ss ON ch.challenge_id = ss.challenge_id
GROUP BY ct.contest_id, ct.hacker_id, ct.name
HAVING ts + tas + tv + tuv > 0
ORDER BY ct.contest_id;

 

 

15 Days of Learning SQL

SELECT submission_date,
       (SELECT COUNT(DISTINCT hacker_id)
        FROM Submissions as S1
        WHERE S1.submission_date = S.submission_date AND
        (SELECT COUNT(DISTINCT S2.submission_date) FROM
         Submissions as S2
         WHERE S2.hacker_id = S1.hacker_id AND S2.submission_date < S.submission_date) = 
        DATEDIFF(S.submission_date, '2016-03-01')
        ),
        (SELECT hacker_id 
         FROM Submissions AS S1
         WHERE S1.submission_date = S.submission_date
         GROUP BY hacker_id
         ORDER BY COUNT(submission_id) DESC, hacker_id LIMIT 1) AS id,
         (SELECT name FROM Hackers WHERE hacker_id = id)
FROM
    (SELECT DISTINCT submission_date FROM submissions) AS S
GROUP BY submission_date;