๐ข ๋ณธ ํฌ์คํ ์ ํ์ฉ๋๋ ๊ธฐ๋ณธ ๋ฌธ์ ๋ฐ ์๋ฃ ์ถ์ฒ๋
๋ฆฌํธ์ฝ๋ Problems, https://leetcode.com/problemset/all/์์ ๋ฐํ๋๋ค.
โ๋ฌธ์
https://leetcode.com/problems/count-salary-categories/description/
LeetCode - The World's Leading Online Programming Learning Platform
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
leetcode.com
Table: Accounts
Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:
- "Low Salary": All the salaries strictly less than $20000.
- "Average Salary": All the salaries in the inclusive range [$20000, $50000].
- "High Salary": All the salaries strictly greater than $50000.
The result table must contain all three categories. If there are no accounts in a category, return 0.
Return the result table in any order.
โ ํ์ด
๋ฌธ์ ์๊ตฌ์ฌํญ
Accounts ํ ์ด๋ธ์์ ๋ฌธ์ ์ ์ ์ํ๋ ์นดํ ๊ณ ๋ฆฌ ๋ฒ์ฃผ์ ๋ฐ๋ฅธ ๊ณ์ข์๋ฅผ ๋ณด์ฌ์ฃผ๋ ํ ์ด๋ธ์ ์ถ๋ ฅํ๋, ๋ฒ์ฃผ์ ํด๋นํ๋ ๊ณ์ข๊ฐ ์๋ค๋ฉด 0 ์ผ๋ก ํ๊ธฐํด์ผ ํ๋ค.
โ ๊ฐ ์์
์ด ํด๋นํ๋ ๊ธ์ฌ ์นดํ
๊ณ ๋ฆฌ ๋ฒ์ฃผ๋ฅผ ์์ฑํ๊ณ ๋ฒ์ฃผ๋ณ ๊ณ์ข ์๋ฅผ group by ๋ก ๊ตฌํด์ค๋ค.
โ ์ต์ข ์ถ๋ ฅ์์๋ <โ > ์ง๊ณ๊ฐ์์ ํน์ ๋ฒ์ฃผ ์นดํ ๊ณ ๋ฆฌ์ ํด๋นํ๋ ๊ณ์ข์๊ฐ ์๋ค๋ฉด ํด๋น ์นดํ ๊ณ ๋ฆฌ ๋ฒ์ฃผ๋ ์ถ๋ ฅ๋์ง ์๋ ๊ฒ์ ๊ณ ๋ คํด์ผ ํ๋ค.
โก ๋ชจ๋ ์นดํ ๊ณ ๋ฆฌ(salary) ๋ฒ์ฃผ๋ฅผ ์ถ๋ ฅํ๊ธฐ ์ํด ์นดํ ๊ณ ๋ฆฌ ๋ฒ์ฃผ ์ ์ฒด๋ฅผ ๊ฐ์ผ๋ก ๊ฐ๋ category ์ปฌ๋ผ์ ์ถ๋ ฅํ๋ ํ ์ด๋ธ์ ๋ง๋ค์ด์ค๋ค.
โข <โก> ํ ์ด๋ธ์ ๊ธฐ์ค์ผ๋ก left join ํด์ ๊ธ์ฌ ์นดํ ๊ณ ๋ฆฌ๋ณ ์ง๊ณ๊ฐ์ ์กฐ์ธํด์ฃผ๊ณ ์ง๊ณ๊ฐ์ด null๋ก ์๋ ์นดํ ๊ณ ๋ฆฌ ๋ฒ์ฃผ๋ 0์ด ์ถ๋ ฅ๋๋๋ก IFNULL ํจ์๋ฅผ ์ฌ์ฉํ๋ค.
SELECT sal.category AS category,
IFNULL(acc.cnt, 0) AS accounts_count
FROM (
SELECT 'High Salary' AS category
UNION
SELECT 'Average Salary'
UNION
SELECT 'Low Salary'
) as sal
LEFT JOIN (
SELECT CASE WHEN income > 50000 THEN 'High Salary'
WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
ELSE 'Low Salary' END AS category
, COUNT(*) AS cnt
FROM Accounts
GROUP BY 1
) acc
ON sal.category = acc.category
'Growth ๐ณ > Practice ๐ป' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[Leet code] 1393. Capital Gain/Loss (1) | 2023.10.04 |
---|---|
[Leet code] 1204. Last Person to Fit in the Bus (0) | 2023.09.26 |
[Leet code] 1341. Movie Rating (1) | 2023.09.22 |
[Leet code] 1193. Monthly Transactions I (0) | 2023.09.21 |
[Leet code] 1174. Immediate Food Delivery II (0) | 2023.09.15 |