SQL Project Planning,Placements,Symmetric Pairs

 

SQL Project Planning 📝 

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true 

➡️ projects 테이블 

• 출력 : start_date,end_date 출력
• 단계 1️⃣ : end_date가 연이어 있을 경우에는 똑같은 프로젝트라고 간주한다는 조건 
  • start_date 중 end_date에 없는 개별 프로젝트의 시작 날짜 구하기
  • end_date 중 start_date에 없는 개별 프로젝트의 끝나는 날짜 구하기
• 단계 2️⃣ : end_date 날짜들은 start_date 날짜보다는 앞에 있을 수 없으므로 조건 넣어서 나열 
• 단계 3️⃣ : start_date로 그룹핑 시킨 후에 end_date의 min 값 출력

 

You are given a table, Projects, containing three columns: Task_ID, Start_Dateand 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.

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), start_date


-- MySQL 5.7버전 이상부터 추가된 sql_mode의 only_full_group_by모드를 해제해주면 
-- 시작날짜를 기준으로 그룹핑했음에도 불구하고 
-- 종료날짜를 최솟값이 아닌 end_date 명시해주어도 원하는 결과 출력
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

 

---

 

Placements📝 -> 오랜만의...쉬운 문제가 아니었나,,

https://www.hackerrank.com/challenges/placements/problem?isFullScreen=true 

➡️ students,friends,packages 테이블 

• 출력 : name 출력 
• 조건 : 아래의 예시에 나온 표처럼 나오게 하기 위해서 테이블 조인 시키기 
   1️⃣ : students id 와 packages id 조인시키기 ( s.id = p.id)
   2️⃣ : students id와 friends id 조인시키기 ( s.id = f.id )
   3️⃣ : friends friend_id와 packages id 조인시키기 ( f.friend_id = p2.id) -> friend salary를 알기 위함 
  
  
• 조건 2 : salary의 값이 나보다 친구의 값이 높은 name을 출력하기 ( p1.salary < p2.salary )
• 조건 3 : 친구의 salary 오름차순으로 출력 ( ORDER BY p2.salary )

 

 

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

 

SELECT s.name
FROM students s
    LEFT JOIN packages p ON s.id = p.id
    LEFT JOIN friends f ON s.id = f.id
    LEFT JOIN packages p2 ON f.friend_id = p2.id
WHERE p.salary < p2.salary
ORDER BY p2.salary;

 

---

 

Symmetric Pairs📝

https://www.hackerrank.com/challenges/symmetric-pairs/problem?isFullScreen=true 

➡️ functions 테이블 

• 출력 : x, y 출력 
• 조건 1 : functions f1의 f1. x = f2.y ,functions f2의  f1.y = f2.x  
  => 즉  x = 1,y = 2인 경우 x = 2, y = 1 형태가 있으면 된다.
• 조건 2 : x1 < = y1 인 값을 찾는다. 하지만 x1 = y1이 같은 경우에는 한쌍이 더 있는 경우에만 가능하다. 
• 조건 3 : X의 오름차순 ( ascending order by the value of X ) 

 

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1. 

Sample Input

Sample Output

20 20
20 21
22 23

 

--방법 1
SELECT f1.x, f1.y
FROM functions f1, functions f2
WHERE 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;


--방법2
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;