[MySQL] LeetCode 문제 연습 3

577. Employee Bonus (Easy)

• Table: Employee
  
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | empId | int |
  | name | varchar |
  | supervisor | int |
  | salary | int |
  +-------------+---------+
  empId is the column with unique values for this table.
  Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.
  
  Table: Bonus
  +-------------+------+
  | Column Name | Type |
  +-------------+------+
  | empId | int |
  | bonus | int |
  +-------------+------+
  empId is the column of unique values for this table.
  empId is a foreign key (reference column) to empId from the Employee table.
  Each row of this table contains the id of an employee and their respective bonus.
  
  Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT name
	   , bonus
FROM employee AS e
LEFT JOIN bonus AS b
ON e.empID = b.empID
WHERE bonus < 1000
    OR b.bonus IS NULL;

---

1280. Students and Examinations (Easy)

• Table: Students

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table. Each row of this table contains the ID and the name of one student in the school.

Table: Subjects

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table. Each row of this table contains the name of one subject in the school.

Table: Examinations

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| student_id | int |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

제출한 쿼리 :

SELECT s.student_id
	   , s.student_name
	   , j.subject_name
	   , COUNT(e.student_id) AS "attended_exams"
FROM Students s
CROSS JOIN Subjects j
LEFT JOIN Examinations e 
ON s.student_id = e.student_id 
	AND j.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, j.subject_name
ORDER BY s.student_id, j.subject_name;

---

570. Managers with at Least 5 Direct Reports (Medium)

• Table: Employee

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.

Write a solution to find managers with at least five direct reports.

Return the result table in any order.

제출한 쿼리 :

WITH ManagerReports AS (
	SELECT managerID
		   , COUNT(*) AS "num_reports"
	FROM Employee
	WHERE managerID IS NOT NULL
	GROUP BY managerID
	)
SELECT e.name
FROM Employee e
JOIN ManagerReports m 
ON e.id = m.managerId
WHERE m.num_reports >= 5;

---

1934. Confirmation Rate (Medium)

• Table: Signups

+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.

Table: Confirmations

+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
| action | ENUM |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

제출한 쿼리 :

SELECT s.user_id
	   , ROUND(AVG(IF(c.`action` = 'confirmed', 1, 0)), 2) AS "confirmation_rate"
FROM Signups AS s
LEFT JOIN Confirmations AS C 
ON s.user_id = c.user_id 
GROUP BY user_id;

---

620. Not Boring Movies (Easy)

• Table: Cinema
  +----------------+----------+
  | Column Name | Type |
  +----------------+----------+
  | id | int |
  | movie | varchar |
  | description | varchar |
  | rating | float |
  +----------------+----------+
  id is the primary key (column with unique values) for this table.
  Each row contains information about the name of a movie, its genre, and its rating.
  rating is a 2 decimal places float in the range [0, 10]
  
  Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".
  Return the result table ordered by rating in descending order.

제출한 쿼리 :

SELECT id
	   , movie
	   , description
	   , rating
FROM Cinema
WHERE id % 2 = 1
	AND description != 'boring'
ORDER BY rating DESC;

---