SQL | Last Person to Fit in the Bus, Count Salary Categories

https://leetcode.com/problems/last-person-to-fit-in-the-bus/description/

---

Last Person to Fit in the Bus

1) 탄 순서 기준으로 몸무게 누적합

# 1000kg까지 탑승
# 초과하지 않고 탑승할 수 있는 마지막 사람
SELECT
    *,
    SUM(Weight) OVER (ORDER BY Turn) AS total_weight
FROM
    Queue

2) 전체 무게가  1000 이하까지만 조회한 후 내림차순으로 정렬, 1개만 추출

# Write your MySQL query statement below
# 1000kg까지 탑승
# 초과하지 않고 탑승할 수 있는 마지막 사람
WITH a AS(
    SELECT
        *,
        SUM(Weight) OVER (ORDER BY Turn) AS total_weight
    FROM
        Queue)
SELECT
    person_name
FROM
    a
WHERE total_weight <= 1000
ORDER BY total_weight DESC
LIMIT 1 ;

---

 

https://leetcode.com/problems/count-salary-categories/description/

---

Count Salary Categories

처음 시도 : 

# 20000 < : Low Salary
# 20000 <= <=50000 : Average Salary
# 50000 > : Hifh Salary
# 결과에 아무것도 없으면 0으로
WITH a AS(
    SELECT
        CASE WHEN income < 20000 THEN 'Low Salary'
             WHEN income >= 20000 AND income < 50000 THEN 'Average Salary'
            ELSE 'High Salary' END AS category,
        COALESCE(income,0) AS income
    FROM
        Accounts)
SELECT
    category,
    COALESCE(COUNT(income),0) AS accounts_count
FROM
    a
GROUP BY 1

 

내 의도 : 조건 걸어서 분류한 다음 COALESCE로 0 지정 → 이미 조건에서 0인 카테고리는 제외

→ 다시 풀어보기

사람들 솔루션에서 힌트 얻고 푼 답 : 

SELECT 
	'High Salary' AS category,
	COUNT(*) AS accounts_count
FROM Accounts
WHERE income > 50000

UNION ALL

SELECT 
	'Low Salary' AS category,
	COUNT(*) AS accounts_count
FROM Accounts
WHERE income < 20000

UNION ALL

SELECT 
	'Average Salary' AS category,
	COUNT(*) AS accounts_count
FROM Accounts
WHERE income >= 20000 AND income <= 50000 ;

💡 SELECT '이름 지정' AS 칼럼명