20260108 TIL

Today I Learn

: 코드카타, Python 문풀날 과제, Python 강의, Git 활용법 세션

 

- 코드카타 

 

- 문제 1 (#48)

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

2. 정답 코드:

with top as (
select food_type, rest_id, rest_name, 
       sum(favorites) as favorites,
       rank() over (partition by food_type order by favorites desc) as rnk
from rest_info 
group by food_type, rest_id, rest_name
order by food_type desc
)
select food_type, rest_id, rest_name, favorites 
from top 
where rnk = 1

 

- 문제 2 (#49)

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

2. 정답 코드:

with ranked as (
    select category, price, product_name, 
       rank() over (partition by category order by price desc) as rnk
    from food_product 
    where category in ('과자','국','김치','식용유')
) 
select category, price as max_price, product_name 
from ranked 
where rnk = 1
order by max_price desc

 

- 문제 3 (#50)

1. 문제 링크: 

2. 정답 코드:

select p.product_id, p.product_name, 
       sum(o.amount*p.price) as total_sales
from food_product p
join food_order o
on p.product_id = o.product_id
where o.produce_date like "2022-05%"
group by p.product_id, p.product_name
order by total_sales desc, p.product_id

 

3. 오류 상황:

select p.product_id, p.product_name, sum(o.amount) as total_sales
from food_product p
join food_order o
on p.product_id = o.product_id
where o.produce_date like "2022-05%"
group by p.product_id, p.product_name
order by total_sales desc, p.product_id

• '총매출'을 구해야 하는데 '총판매량'을 구하는 식인 sum(o.amount)만 적어서 틀

4. 시도 방법: 프로그래머스 질문하기에 올라와있는 문제 풀이 블로그 참고

 

 

- 문제 4 (#51)

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

2. 정답 코드:

select o.animal_id, o.name
from animal_outs o 
left join animal_ins i 
on o.animal_id = i.animal_id 
where i.datetime is null
order by o.animal_id

 

- 문제 5 (#55)

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

2. 정답 코드:

with over_three as (
select b.writer_id, count(distinct b.board_id) cnt
from used_goods_board b
group by b.writer_id
having cnt >= 3
)
select u.user_id, u.nickname,
       concat(city, ' ', u.street_address1, ' ', u.street_address2) as '전체주소',
       concat(substr(u.tlno,1,3),'-',substr(u.tlno,4,4),'-',substr(u.tlno,8,4)) as '전화번호'
from used_goods_user u 
join over_three o 
on u.user_id = o.writer_id
order by u.user_id desc

select user_id, nickname,
       concat(city, ' ', street_address1, ' ', street_address2) as '전체주소',
       concat(substr(tlno,1,3),'-',substr(tlno,4,4),'-',substr(tlno,8,4)) as '전화번호'
from used_goods_user u 
where user_id in ( 
    select writer_id 
    from used_goods_board 
    group by writer_id 
    having count(*) >= 3
    )
order by user_id desc

elect user_id, nickname,
       concat_ws(' ', city, street_address1, street_address2) as '전체주소',
       insert(insert(tlno,4,0,'-'),9,0,'-') as '전화번호'
from used_goods_user u 
where user_id in ( 
    select writer_id 
    from used_goods_board 
    group by writer_id 
    having count(*) >= 3
    )
order by user_id desc

 

• concat_ws(' ', 컬럼1, 컬럼2, 컬럼3) : 각 컬럼을 연결하고 각 컬럼 사이에 공백문자 넣음
• insert(컬럼1,위치,길이,넣을문자) : 컬럼의 해당 위치에 길이만큼 삭제를 하고 문자 넣음 (길이 0은 삽입만을 의미)

- 문제 6 (#58)

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

2. 정답 코드: 

select a.apnt_no, p.pt_name, p.pt_no, a.mcdp_cd, d.dr_name, a.apnt_ymd
from appointment a
join patient p 
on a.pt_no = p.pt_no
join doctor d 
on a.mddr_id = d.dr_id 
where a.apnt_ymd like '2022-04-13%'
and a.apnt_cncl_yn = 'N' # 취소되지 않은
and a.mcdp_cd = 'CS'
order by a.apnt_ymd asc

 

 

 

- 파이썬 공부

 

- isalpha() & isdigit()

https://myun0506.tistory.com/30

isalpha, isdigit() 함수

 

- if 문의 continue vs pass, while문 활용

- 중첩 삼항 연산자, 연쇄 비교 (Chained comparison), 연산자 우선순위, 단락 평가

- for-else 구문

https://myun0506.tistory.com/29

if 조건문 활용, while 절 활용, 연산자 비교 우선순위, 단락평가

 

 

 

- Truthiness (참 같은 값)

- 중첩 조건 표현식과 Truthy/Falsy의 연쇄

 

https://myun0506.tistory.com/32

Truthiness (참 같은 값), Truthy/Falsy, 단락평가

 

 

- 가변 (mutable) 객체와 불변 (immutable) 객체의 차이

https://myun0506.tistory.com/31

가변 (Mutable) vs 불변 (Immutable)

 

 

- 리스트 컴프리헨션 (List Comprehension)

- 중첩 리스트 컴프리헨션 (Nested List Comprehension)

 

https://myun0506.tistory.com/33

리스트 컴프리헨션 (List Comprehension)

 

 

- SQL + Python 연동 사고력

 

https://myun0506.tistory.com/manage/newpost/?type=post&returnURL=%2Fmanage%2Fposts%2F

티스토리

 

 

 

 

- Git과 GitHub 특강 - 임경원 튜터님 

 

https://myun0506.tistory.com/35

 

Git과 GitHub 특강 - 임경원 튜터님 20260108