CodeKata

SQL

112. Count Salary Categories

• 작성한 쿼리

(
SELECT
  "Low Salary" AS category
  , COUNT(IF(income < 20000, 1, NULL)) AS accounts_count
FROM
  Accounts
)
UNION
(
SELECT
  "Average Salary" AS category
  , COUNT(IF(income BETWEEN 20000 AND 50000, 1, NULL)) AS accounts_count
FROM
  Accounts
)
UNION
(
SELECT
  "High Salary" AS category
  , COUNT(IF(income > 50000, 1, NULL)) AS accounts_count
FROM
  Accounts
)
;

참고할 만한 다른 풀이

• Submission beats 1등한 사람 풀이

select 
  category
  , max(accounts_count) as accounts_count
from
  (select 
    category
    , count(*) as accounts_count
  from
    (select
      account_id
      , case 
        when income<20000 then 'Low Salary'
        when 20000<=income and income<=50000 then 'Average Salary' 
        else 'High Salary' 
      end as category
    from
      Accounts) a1
  group by
    category
  union
  select
    'High Salary' as category, 0 as accounts_count
  union
  select 
    'Average Salary' as category, 0 as accounts_count
  union
  select
    'Low Salary' as category, 0 as accounts_count
  ) a2
group by
  category

WITH category_class AS (
    SELECT 'Low Salary' AS category
    UNION ALL
    SELECT 'Average Salary'
    UNION ALL
    SELECT 'High Salary'
)
, class_def AS (
    SELECT CASE 
                WHEN income < 20000 THEN 'Low Salary'
                WHEN income >= 20000 AND income <= 50000 THEN 'Average Salary'
                WHEN income > 50000 THEN 'High Salary'
           END AS category, 
           COUNT(*) AS accounts_count   
    FROM Accounts
    GROUP BY CASE 
                WHEN income < 20000 THEN 'Low Salary'
                WHEN income >= 20000 AND income <= 50000 THEN 'Average Salary'
                WHEN income > 50000 THEN 'High Salary'
             END
)
SELECT cc.category, COALESCE(cd.accounts_count, 0) AS accounts_count
FROM category_class cc 
LEFT JOIN class_def cd
ON cc.category = cd.category;

SELECT
    c.category,
    COALESCE(t.accounts_count, 0) AS accounts_count
FROM
    (SELECT 'Low Salary' AS category
     UNION ALL
     SELECT 'Average Salary'
     UNION ALL
     SELECT 'High Salary') c
LEFT JOIN
    (
        SELECT 
            CASE
                WHEN income < 20000 THEN 'Low Salary'
                WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
                ELSE 'High Salary'
            END AS category,
            COUNT(account_id) AS accounts_count
        FROM accounts
        GROUP BY category
    ) t ON c.category = t.category;

• UNION, CASE WHEN

SELECT 
    'Low Salary' AS category,
    COUNT(CASE WHEN a.income < 20000 THEN 1 END) AS accounts_count
FROM accounts a
UNION ALL
SELECT 
    'Average Salary' AS category,
    COUNT(CASE WHEN a.income BETWEEN 20000 AND 50000 THEN 1 END) AS accounts_count
FROM accounts a
UNION ALL
SELECT 
    'High Salary' AS category,
    COUNT(CASE WHEN a.income > 50000 THEN 1 END) AS accounts_count
FROM accounts a;

• CTE, LEFT JOIN

SELECT 
    'Low Salary' AS category,
    COUNT(CASE WHEN a.income < 20000 THEN 1 END) AS accounts_count
FROM accounts a
UNION ALL
SELECT 
    'Average Salary' AS category,
    COUNT(CASE WHEN a.income BETWEEN 20000 AND 50000 THEN 1 END) AS accounts_count
FROM accounts a
UNION ALL
SELECT 
    'High Salary' AS category,
    COUNT(CASE WHEN a.income > 50000 THEN 1 END) AS accounts_count
FROM accounts a;

• UNION ALL, Subquery

(select 
"Low Salary" as category,
(select count(*)  from Accounts where income < 20000) as accounts_count)
union all
(select 
"Average Salary" as category,
(select count(*) from Accounts where income >= 20000 and income <= 50000) as accounts_count)
union all 
(select 
"High Salary" as category,
(select count(*) from Accounts where income > 50000) as accounts_count)

113. Employees Whose Manager Left the Company

• 작성한 쿼리

SELECT
  employee_id
FROM
  Employees
WHERE
  salary < 30000
  AND manager_id NOT IN (
    SELECT
      employee_id
    FROM
      Employees
  )
ORDER BY
  employee_id
;

참고할 만한 다른 풀이

• LEFT JOIN: 추천!

SELECT
  e1.employee_id
FROM
  Employees e1
  LEFT JOIN Employees e2 
  ON e1.manager_id = e2.employee_id
WHERE 
  e1.salary < 30000 
  AND e1.manager_id IS NOT NULL 
  AND e2.employee_id IS NULL
ORDER BY
  e1.employee_id
;

• EXIST

SELECT 
    e1.employee_id 
FROM employees e1 
WHERE EXISTS (
        SELECT 
            * 
        FROM employees e2 
        WHERE e1.manager_id = e2.manager_id 
            AND e1.salary < 30000 
            AND e2.manager_id NOT IN (SELECT employee_id FROM employees)
)
ORDER BY e1.employee_id;

114. Exchange Seats

• 작성한 쿼리

SELECT
  id
  , CASE
    WHEN id%2 = 0 THEN LAG(student) OVER (ORDER BY id) 
    ELSE COALESCE(LEAD(student) OVER (ORDER BY id), student) 
  END AS student
FROM
  Seat
;

참고할 만한 다른 풀이

• Submission beats 1등한 사람 풀이

select
  case
    when id = (select max(id) from Seat) and MOD(id , 2 ) = 1 then id  
    when MOD(id , 2) = 1 then id+1 else id-1 
  end as id 
  , student
from 
  Seat
order by 
  id
;

SELECT
    case 
        when 
            id = (select max(id) from Seat) and id % 2 = 1 
            then id
        when 
            id % 2 = 1 
            then id + 1
        else id - 1
    end as id,student
from Seat
order by id

• CTE

with even as (
    select 
        student,
        2 * (row_number() over ()) - 1 as id_nm
    from seat
    where id % 2 = 0
),
odd as (
    select 
        student,
        2 * row_number() over () as id_nm 
    from seat
    where id % 2 != 0
)
select row_number() over () as id, student from 
(select id_nm, student
from odd
union all
select id_nm, student
from even) t
order by 
    id_nm

Python

48. K번째수

• 작성한 코드

def solution(array, commands):
    answer = []
    for i in commands:
        arr = array[i[0]-1:i[1]]
        arr.sort()
        answer.append(arr[i[2]-1])
    return answer

참고할 만한 다른 풀이

• map, lambda

def solution(array, commands):
    return list(map(lambda x:sorted(array[x[0]-1:x[1]])[x[2]-1], commands))

→ map: 여러 개의 데이터를 한 번에 다른 형태로 변환하기 위해 사용
→ lambda 인자: 표현식

# map, lambda 예시
ls = ['1','2','3']
>>> list(map(int, ls))
>>> [1, 2, 3]

>>> set(map(lambda x: x ** 2, range(5)))
>>> {0, 1, 4, 9, 16}​

list(map(lambda x: 2*2, range(3))) 이런 식으로 람다 함수의 실행 부분에 변수 x를 포함시키지 않아도 됩니다.

• List Comprehension

def solution(array, commands):
    return [sorted(array[i-1:j])[k-1] for i,j,k in commands]

List Comprehension을 사용해서 return [sorted(array[a-1:b])[c-1] for a,b,c in commands] 하시는게 더 pythonic 하고 더 빠른 경우가 많습니다.
언어에 이미 higher order function 지원이 되있으니 list comprehension 추천드립니다

SDL

추가 공부

회고

• pythonic 한 코드를 짤 수 있게 되기까지 얼마나 많은 노력이 필요할까… 분발하자