20260219 TIL

[Today I Learn]

- SQL codekata

- Python codekata

---

[ Python codekata ] 

 

- 문제 1.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/120851?language=python3

2. 정답 코드

def solution(my_string):
    answer = sum(int(i) for i in my_string if i.isdigit())
    return answer

 

- 문제 2.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/120809

2. 정답 코드

def solution(numbers):
    answer = [i*2 for i in numbers]
    return answer

 

- 문제 3.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/120583?language=python3

2. 정답 코드

def solution(array, n):
    answer = sum(1 for i in array if i==n)
    return answer

def solution(array, n):
    return array.count(n)

 

---

[ SQL codekata ] 

 

- 문제 1.

1. 문제 링크: https://www.hackerrank.com/challenges/the-blunder/problem

2. 정답 코드

select ceil(avg(salary) - avg(replace(salary,'0','')))
from employees

select ceil(avg(salary - replace(salary,'0','')))
from employees

 

- 문제 2.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/164671

2. 정답 코드

with highest as ( 
    select board_id, rank() over (order by views desc) as rnk
    from used_goods_board 
)
SELECT concat('/home/grep/src/',usf.board_id,'/',usf.file_id,usf.file_name,usf.file_ext) as file_path
from highest h
join used_goods_file usf 
on h.board_id = usf.board_id 
where h.rnk = 1
order by usf.file_id desc

 

- 문제 3.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/144856

2. 정답 코드

select 
    a.author_id, 
    a.author_name,
    b.category,
    sum(bs.sales * b.price) as total_sales
from book b
join author a 
on b.author_id = a.author_id 
join book_sales bs 
on b.book_id = bs.book_id
where bs.sales_date like '2022-01%'
group by a.author_id, a.author_name, b.category
order by a.author_id asc, b.category desc

select 
    a.author_id, 
    a.author_name,
    b.category,
    sum(bs.sales * b.price) as total_sales
from book b
join author a 
on b.author_id = a.author_id 
join book_sales bs 
on b.book_id = bs.book_id
where bs.sales_date >= '2022-01-01' and bs.sales_date < '2022-02-01'
group by a.author_id, a.author_name, b.category
order by a.author_id asc, b.category desc

 

- 문제 4.

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/151139

2. 정답 코드

select month(rh.start_date) as month, rh.car_id, count(*) as records 
from car_rental_company_rental_history rh
where exists ( 
    select 1
    from car_rental_company_rental_history h
    where 
        h.start_date >= '2022-08-01' and h.start_date < '2022-11-01'
    and rh.car_id = h.car_id
    group by h.car_id
    having count(*) >= 5
) 
and rh.start_date >= '2022-08-01' and rh.start_date < '2022-11-01'
group by month(rh.start_date), rh.car_id
having count(*) > 0
order by month(rh.start_date) asc, rh.car_id desc

 

• exists
  • where절에서 rh.car_id = h.car_id 로 연결을 해주었기 때문에 exists 서브쿼리 안에 있는 조건에 해당하는 car_id 전체 행에 대해서 외부 쿼리에서 조회를 시행한다
  • 해당 기간 동안 5번 이상 거래가 일어난 자동차 Id에 대해서 조회를 하는데 결국 외부 쿼리에서는 그 해당 자동차 id 거래 전체에 대해서 조회를 하기 때문에 날짜 조건은 외부 쿼리에서 한번 더 걸어주어야 한다