MySQL Scripts

Top Competitors challenge from Hacker Rank

Methods: Inner Join and Subqueries

Task: Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

Tables: Hackers, Difficulty, Challenges and Submissions

SELECT h.hacker_id, h.name
FROM Hackers h
    INNER JOIN
        (SELECT s.hacker_id AS hack_id, COUNT(s.hacker_id) AS counter
         FROM Submissions s 
             INNER JOIN (SELECT c.challenge_id AS id, c.difficulty_level, d.score AS max_score 
                        FROM Challenges c INNER JOIN Difficulty d 
                        ON c.difficulty_level = d.difficulty_level) AS p
             ON s.challenge_id = p.id
         WHERE s.score = p.max_score
         GROUP BY 1) AS t
    ON h.hacker_id = t.hack_id
WHERE t.counter > 1
ORDER BY t.counter DESC, h.hacker_id ASC

Placements challenge from Hacker Rank

Methods: Inner Join and Subqueries

Task: Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

Example Tables:

SELECT name
FROM (SELECT s.id AS student_id, s.name, salary
      FROM Students s INNER JOIN packages p ON s.id = p.id) AS g
      INNER JOIN
     (SELECT f.id AS student_id, p.id AS friend_id, salary
      FROM friends f INNER JOIN packages p ON f.friend_id = p.id) AS h
      ON g.student_id = h.student_id
WHERE h.salary > g.salary
ORDER BY h.salary