[SQL 코드카타] 110 ~ 112번(LeetCode)

110번 product-price-at-a-given-date

> 해답 참고(쿼리를 만들어 조합)

with manu_pro as (
    select
        product_id,
        new_price,
        row_number() over(partition by product_id order by change_date desc) as rn
    from 
        products p1
    where
        change_date <= '2019-08-16'
), 
cho_pro as (
    select 
        distinct product_id
    from 
        products
)
select
    cp.product_id,
    ifnull(mp.new_price, 10) as price
from
    cho_pro cp
    left join manu_pro mp
    on mp.product_id = cp.product_id and rn = 1

> 다른 방법

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)

111번 last-person-to-fit-in-the-bus

-- 1) 누계를 구한 열 추가
with manu_queue as(
select
    person_id,
    person_name,
    weight,
    turn,
    sum(weight) over(order by turn asc) as sum_weight
from
    Queue
)
2) 1000 이하에 해당하는 사람 중 turn이 가장 높은 사람 조회
select
    person_name
from
    manu_queue
where
    sum_weight <= 1000
order by
    turn desc
limit 1;

> 셀프 조인을 활용하여 깔끔한 답 ..!👏👏

SELECT 
    q1.person_name
FROM
	Queue q1 
    JOIN Queue q2 
    ON q1.turn >= q2.turn
GROUP BY 
	q1.turn
HAVING 
	SUM(q2.weight) <= 1000
ORDER BY 
	SUM(q2.weight) DESC
LIMIT 1

 

112번 count-salary-categories

> union 없이 풀고 싶었는데 그러지 못했다..

SELECT 
    'Low Salary' AS category,
    SUM(income < 20000) AS accounts_count
FROM 
    Accounts
UNION 
SELECT 
    'Average Salary' AS category,
     SUM(income BETWEEN 20000 AND 50000 ) AS accounts_count
FROM 
     Accounts
UNION
SELECT 
    'High Salary' AS category,
     SUM(income > 50000) AS accounts_count
FROM 
    Accounts;

> union없이 푼 해답

WITH CTE_A AS(
SELECT account_id,
    CASE 
        WHEN income < 20000 THEN "Low Salary"
        WHEN income > 50000 THEN "High Salary"
        ELSE "Average Salary"
    END AS category
FROM Accounts),
CTE_B AS(        
SELECT "Low Salary" AS Category
UNION 
SELECT "Average Salary" AS Category
UNION 
SELECT "High Salary" AS Category
)
SELECT
    B.category,
    count(A.category) as accounts_count
FROM
    CTE_B B
    LEFT JOIN CTE_A A
    ON B.category = A.category
GROUP BY
    1