CodeKata

SQL

119. Department Top Three Salaries

• 작성한 쿼리

WITH rnk AS(
  SELECT
    d.name AS Department
    , e.name AS Employee
    , salary
    , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS ranking
  FROM
    Employee e
    JOIN Department d
    ON e.departmentId = d.id 
)
SELECT
  Department
  , Employee
  , Salary
FROM
  rnk
WHERE
  ranking <= 3	
;

참고할 만한 다른 풀이

Select d.name as Department, e.name as Employee, e.salary as Salary
From (
    Select name, salary, departmentId, Dense_Rank() Over (Partition by departmentId Order by Salary DESC) as denseRank
    From Employee
) AS e
Join Department d
On e.departmentId = d.id
Where e.denseRank <= 3
;

105. Customers Who Bought All Products

• 작성한 쿼리

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product)
;

참고할 만한 다른 쿼리

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) IN (
    SELECT COUNT(*) 
    FROM Product
)
;

select 

customer_id

#SubQuery Begin -->

from (select 

case 

     when count(distinct product_key) = (select count(product_key) from Product)

         then  customer_id end as customer_id

from Customer c

group by customer_id)  as subquery

# SubQuery --> End

where customer_id is not null



); 

106. The Number of Employees Which Report to Each Employee

• 작성한 쿼리

SELECT
  e1.reports_to AS employee_id
  , e2.name AS name
  , COUNT(*) AS reports_count
  , ROUND(AVG(e1.age), 0) AS average_age
FROM
  Employees e1
  JOIN Employees e2
  ON e1.reports_to = e2.employee_id
GROUP BY
  e1.reports_to
  , e2.name
ORDER BY
  e1.reports_to
;

Python

43. 크기가 작은 부분문자열

• 작성한 코드

def solution(t, p):
    answer = 0
    for i in range(0, len(t)+1-len(p)):
        if int(p) >= int(t[i:i+len(p)]):
            answer += 1
    return answer

참고할 만한 다른 쿼리

def solution(t, p):
    return len([t[i: i+len(p)] for i in range(len(t)-len(p)+1) if int(t[i: i+len(p)]) <= int(p)])

라이브 세션

통계야 놀자 3회차

SDL

통계적 오류와 유의수준, 유의확률

파이썬 심화반

3회차

ML 강의

1.1 ~ 1.12

회고

• 갑자기 공부해야 하는 내용이 엄청 늘어나 조금 버거움