SQL 학습

리트코드 SQL 문제 풀이

✅ 1164. Product Price at a Given Date.

product_id, change_date is the primary key of the below table.
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+

Write a sol. 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.

# 2019-08-16 이전 최근 날짜 기준 id, price 갖고오기
SELECT product_id, new_price as price
FROM Products
WHERE (product_id, change_date) IN (
    SELECT product_id, MAX(change_date) as date
    FROM Products
    WHERE DATE_FORMAT(change_date, '%Y-%m-%d') <= '2019-08-16'
    GROUP BY product_id
)

UNION

# 2019-08-16 이후는 price 10으로 처리하여 중복제거 병합
SELECT DISTINCT product_id, 10 as price
FROM Products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM Products 
    WHERE DATE_FORMAT(change_date, '%Y-%m-%d') <= '2019-08-16'
)

✅ 1204. Last Person to Fit in the Bus.

+-------------+---------+
| 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.
Note that only one person can board the bus at any given turn.

SELECT person_name 
FROM(
	# 버스를 탑승하는 인원의 이름, 무게, 누적 무게 테이블 생성
    SELECT person_name, weight, sum(weight) OVER (ORDER BY turn) AS cum_weight
    FROM Queue 
    ORDER BY turn) 
WHERE cum_weight <=1000 
ORDER BY cum_weight DESC LIMIT 1;