[MySQL] LeetCode 문제 연습 7

1789. Primary Department for Each Employee (Easy)

• Table: Employee
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | employee_id | int |
  | department_id | int |
  | primary_flag | varchar |
  +---------------+---------+
  (employee_id, department_id) is the primary key (combination of columns with unique values) for this table.
  employee_id is the id of the employee.
  department_id is the id of the department to which the employee belongs.
  primary_flag is an ENUM (category) of type ('Y', 'N'). If the flag is 'Y', the department is the primary department for the employee. If the flag is 'N', the department is not the primary.
  
  Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is 'N'.
  
  Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT employee_id
	   , department_id
FROM Employee 
WHERE primary_flag = 'Y'

UNION

SELECT employee_id
	   , department_id
FROM Employee 
GROUP BY employee_id
HAVING COUNT(employee_id) = 1
ORDER BY employee_id;

---

610. Triangle Judgement (Easy)

• Table: Triangle
  +-------------+------+
  | Column Name | Type |
  +-------------+------+
  | x | int |
  | y | int |
  | z | int |
  +-------------+------+
  In SQL, (x, y, z) is the primary key column for this table.
  Each row of this table contains the lengths of three line segments.
  
  Report for every three line segments whether they can form a triangle.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT *
	   , IF(x + y > z AND y + z > x AND z + x > y, "Yes", "No") AS "triangle" 
FROM Triangle;

---

180. Consecutive Numbers (Medium)

• Table: Logs
  
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | id | int |
  | num | varchar |
  +-------------+---------+
  In SQL, id is the primary key for this table.
  id is an autoincrement column.
  
  Find all numbers that appear at least three times consecutively.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE 
    l1.id = l2.id-1 AND
    l2.id = l3.id-1 AND
    l1.num = l2.num AND
    l2.num = l3.num;

---

1164. Product Price at a Given Date (Medium)

• Table: Products
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | product_id | int |
  | new_price | int |
  | change_date | date |
  +---------------+---------+
  (product_id, change_date) is the primary key (combination of columns with unique values) of this table.
  Each row of this table indicates that the price of some product was changed to a new price at some date.
  
  Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
  
  Return the result table in any order.

제출한 쿼리 :

WITH cte AS (SELECT *
                    , RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS "r" 
            FROM Products
            WHERE change_date<= '2019-08-16')
SELECT product_id, new_price AS "price"
FROM cte
WHERE r = 1
UNION
SELECT product_id
       , 10 AS "price"
FROM Products
WHERE product_id NOT IN (SELECT product_id FROM cte);

---

1204. Last Person to Fit in the Bus (Medium)

• Table: Queue
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | person_id | int |
  | person_name | varchar |
  | weight | int |
  | turn | int |
  +-------------+---------+
  person_id column contains unique values.
  This table has the information about all people waiting for a bus.
  The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table.
  turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board.
  weight is the weight of the person in kilograms.
  
  There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.
  
  Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit.
  
  The test cases are generated such that the first person does not exceed the weight limit.

제출한 쿼리 :

WITH OrderedQueue AS (
    SELECT person_id
    	   , person_name
    	   , weight
    	   , turn
    FROM Queue
    ORDER BY turn
)

, CumulativeWeights AS (
    SELECT person_id
    	   , person_name
    	   , weight
    	   , turn
    	   , SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
    FROM OrderedQueue
)
SELECT person_name
FROM CumulativeWeights
WHERE cumulative_weight <= 1000
ORDER BY turn DESC
LIMIT 1;

---