SQL Advanced Join - Symmetric Pairs

Problem

You are given a table, Functions, containing two columns: X and Y. Y is the value of some function F at X -- i.e. Y = F(X).

Two pairs (X₁, Y₁) and (X₂, Y₂) are said to be symmetric pairs if X₁ = Y₂ and X₂ = Y₁.
Write a query to output all such symmetric pairs in ascending order by the value of X.
Sample Input

Sample Output

20 20
20 21
22 23

My Answer
SELECT f1.x AS x,
       f1.y AS y
FROM   functions f1,
       functions f2
WHERE  f1.x = f2.y
       AND f1.y = f2.x
       AND f1.x < f1.y
UNION
SELECT x,
       y
FROM   functions
WHERE  x = y
GROUP  BY x,
          y
HAVING Count(x) > 1
ORDER  BY x ASC 

SQL Advanced Join - Placements

 Problem
You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

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.
Sample Input

Sample Output

Samantha
Julia
Scarlet

Explanation
See the following table:

Now,

• Samantha's best friend got offered a higher salary than her at 11.55
• Julia's best friend got offered a higher salary than her at 12.12
• Scarlet's best friend got offered a higher salary than her at 15.2
• Ashley's best friend did NOT get offered a higher salary than her

The name output, when ordered by the salary offered to their friends, will be:

• Samantha
• Julia
• Scarlet

My Answer
select s.name
    from Packages p1
    inner join Friends f on p1.ID = f.ID
    inner join Students s on f.ID = s.ID
    inner join Packages p2 on p2.ID = f.Friend_ID
    where p1.Salary < p2.Salary
order by p2.Salary

SQL Advanced Join - Projects

Problem
You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.
Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.
Sample Input

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

Explanation
The example describes following four projects:

• Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project. Thus start date of project is 2015-10-01 and end date is 2015-10-04, so it took 3 days to complete the project.
• Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project. Thus, the start date of project is 2015-10-13 and end date is 2015-10-15, so it took 2 days to complete the project.
• Project 3: Only task 6 is part of the project. Thus, the start date of project is 2015-10-28 and end date is 2015-10-29, so it took 1 day to complete the project.
• Project 4: Only task 7 is part of the project. Thus, the start date of project is 2015-10-30 and end date is 2015-10-31, so it took 1 day to complete the project.

Answer from an other
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 

SQL Basic join - Contest Leaderboard

Problem
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.

Sample Input

Hackers Table:
Submissions Table:

Sample Output

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43

Explanation

Hacker 4071 submitted solutions for challenges 19797 and 49593, so the total score .
Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score
Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score .
The total scors for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.

My Answer
SELECT h.hacker_id,
       h.name,
       Sum(top) AS total
FROM   hackers AS h
       INNER JOIN(SELECT hacker_id,
                         challenge_id,
                         Max(score) AS top
                  FROM   submissions
                  GROUP  BY hacker_id,
                            challenge_id
                  ORDER  BY top DESC) AS s
               ON h.hacker_id = s.hacker_id
GROUP  BY h.hacker_id,
          h.name
HAVING total > 0
ORDER  BY total DESC,
          h.hacker_id 

SQL Basic Join - Challenges

 Problem
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

---

Sample Input 0
Hackers Table: Challenges Table:
Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

Sample Input 1
Hackers Table: Challenges Table:
Sample Output 1

12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1

Explanation

For Sample Case 0, we can get the following details:

Students and both created challenges, but the maximum number of challenges created is so these students are excluded from the result.
For Sample Case 1, we can get the following details:

Students and both created challenges. Because is the maximum number of challenges created, these students are included in the result.

Answer
SELECT h.hacker_id,
       h.NAME,
       Count(challenge_id) AS c_count
FROM   hackers h
       LEFT JOIN challenges c
              ON h.hacker_id = c.hacker_id
GROUP  BY h.hacker_id,
          h.NAME
HAVING c_count = (SELECT Max(t_count)
                  FROM   (SELECT Count(challenge_id) AS t_count
                          FROM   challenges
                          GROUP  BY hacker_id) AS tbl)
        OR c_count IN (SELECT t_count
                       FROM   (SELECT Count(challenge_id) AS t_count
                               FROM   challenges
                               GROUP  BY hacker_id) AS tbl
                       GROUP  BY t_count
                       HAVING Count(t_count) = 1)
ORDER  BY c_count DESC,
          h.hacker_id ASC 

SQL Basic Join - Top Competitors

Problem
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! 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.

---

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.
• Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission.

---

Sample Input

Hackers Table: Difficulty Table: Challenges Table: Submissions Table:

Sample Output

90411 Joe

Explanation

Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.
Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.
Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.
Only hacker 90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as space-separated values.

My Answer
set sql_mode='';
SELECT h.hacker_id,h.name
       FROM   submissions s
              inner join challenges c
                      ON c.challenge_id = s.challenge_id
              inner join difficulty d
                      ON d.difficulty_level = c.difficulty_level
              inner join hackers h
                      ON h.hacker_id = s. hacker_id
       WHERE  s.score = d.score
       GROUP  BY h.hacker_id
        HAVING Count(s.submission_id)>1
       ORDER  BY Count(s.submission_id) DESC, hacker_id ASC
An other answer
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

SQL Advanced Select - New Companies

Problem
Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:
Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.
Note:

• The tables may contain duplicate records.
• The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

---

Input Format

The following tables contain company data:

• Company: The company_code is the code of the company and founder is the founder of the company.
• Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.
• Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
• Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
• Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.

---

Sample Input

Company Table: Lead_Manager Table: Senior_Manager Table: Manager Table: Employee Table:

Sample Output

C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2

Explanation

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.
In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

My Answer
SET sql_mode='';  
SELECT DISTINCT
  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
LEFT JOIN Lead_Manager AS L
  ON C.company_code = L.company_code
LEFT JOIN Senior_Manager AS S
  ON L.lead_manager_code = S.lead_manager_code
LEFT JOIN Manager AS M
  ON S.senior_manager_code = M.senior_manager_code
LEFT JOIN Employee AS E
  ON M.manager_code = E.manager_code
GROUP BY C.company_code
ORDER BY C.company_code

Answer from others
set sql_mode='';
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
ORDER BY c.company_code;

The below query cannot work right!
 SELECT C.company_code,C.founder,NumL,NumS,NumM,NumE
    FROM(select NumE,NumM,NumS,count(distinct L.lead_manager_code) as NumL,L.company_code   
        FROM(select NumE,NumM,count(distinct S.senior_manager_code) as NumS, S.lead_manager_code   
            FROM(SELECT NumE,COUNT(distinct M.manager_code) AS NumM,M.senior_manager_code
                FROM (SELECT COUNT(DISTINCT employee_code) AS NumE,manager_code
                        FROM Employee
                        GROUP BY manager_code) AS E
                RIGHT JOIN Manager AS M
                ON M.manager_code = E.manager_code
                GROUP BY M.senior_manager_code) AS EM
            RIGHT JOIN Senior_Manager as S
            on S.senior_manager_code = EM.senior_manager_code
            GROUP BY S.lead_manager_code) as EMS
        RIGHT JOIN Lead_Manager as L
        on L.lead_manager_code = EMS.lead_manager_code
        group by L.company_code) AS EMSL
    RIGHT JOIN Company as C
    on C.company_code = EMSL.company_code
    order by company_code;