[250331] SQL & 파이썬 문제풀이

카테고리 없음

[250331] SQL & 파이썬 문제풀이

jeonieee 2025. 3. 31. 20:55

SQL 문제풀이

문제 #1 (쉬움)
- 카카오 광고 분석을 위한 ad_events 테이블이 있습니다.
- 2022년 동안 발생한 클릭률(Click-Through Rate, CTR)을 계산하는 SQL 쿼리를 작성하세요.
- 결과는 ad_id 기준으로 오름차 정렬하세요. 소수점 둘째 자리까지 반올림하여 출력하세요.
  - 📌 CTR(클릭률) 공식 : CTR = 100.0 * ( Click 수 / Impression 수 )
내 풀이

select ad_id,
       100*(count(event_type = 'click')/count(event_type = 'impression')) as CTR
from ad_events 
group by ad_id

정답

select ad_id,
       round(sum(case when event_type='click' then 1 else 0 end) * 100 / 
       sum(case when event_type='impression' then 1 else 0 end), 2) as CTR
from ad_events 
group by 1
order by 1

문제 #2 (중간)
- 모든 제품 카테고리에서 최소 1개 이상의 상품을 구매한 고객을 VIP 고객이라 정의합니다.결과는 고객 ID 기준으로 오름차 정렬하세요.
- VIP 고객의 customer_id를 조회하는 SQL 쿼리를 작성하세요.
- 쿠팡의 고객 구매 데이터를 분석하여 VIP 고객을 식별하려고 합니다.
내 풀이
- 여기서 모든 카테고리를 어떻게 하는지 모르겠네.. 상품 종목을 알면 a and b and c 라고 하면 될텐데 ..
  - count(distinct product_category)하면 카테고리 갯수 구하고 having절로 걸러내기

select customer_id 
from customer_orders as co join products as p on co.product_id = p.product_id
group by customer_id
order by customer_id

정답

select co.customer_id
from customer_orders as co join products as p on co.product_id = p.product_id
group by customer_id
having count(distinct p.product_category) >=3
order by customer_id

문제 #3 (중간)
- 각 사용자의 블로그 게시글 수에 대한 3일 이동 평균을 계산하여 user_id, post_date, rolling_avg_3d 값을 출력하는 SQL 쿼리를 작성하세요.결과는 고객 ID, 게시글 작성 날짜 기준으로 오름차 정렬하세요.
- 이동 평균은 시계열 데이터를 분석할 때 일정 기간 동안의 평균을 계산하는 기법입니다.
- 이 문제에서는 각 사용자의 3일 이동평균(3-day rolling average)을 구해야 합니다.
- 즉, 각 날짜의 게시글 수와 그 이전 2일간의 게시글 수 평균을 계산해야 합니다.
- 데이터가 3일보다 적으면 가능한 범위 내에서 평균을 구합니다.
📌 이동 평균(rolling average) 정의
- 결과는 소수점 둘째 자리까지 반올림하여 출력하세요.
- 네이버 블로그의 게시글 데이터를 분석하여 3일 이동 평균(rolling average)을 계산하세요.
내 풀이

: 정말 죄송하지만.. 문제부터가 어려워서 무슨말인지 잘 이해가 되지 않음.... 어휘력이 고장난거같음...

select 
from blog_posts
group by user_id 
order by user_id, post_date

정답

select user_id, post_date,
       post_count,
       avg(post_count) over (
      	 partition by user_id
      	 order by post_date 
      	 rows between 2 preceding and 1 preceding
       ) ma_3,
      avg(post_count) over (
      partition by user_id
      order by post_date  
      ) ma_all
from blog_posts
group by user_id 
order by user_id

문제 #4 (도전)
- 각 주문(order_id)이 다음 행(order_id + 1)의 음식(item)과 바뀌어 저장되는 오류가 발생했습니다.결과는 수정된 주문 ID 기준으로 오름차 정렬하세요.
  - 짝수 order_id는 이전 order_id - 1의 item과 매칭되어야 합니다.
  - 홀수 order_id는 다음 짝수 order_id의 item과 매칭되어야 합니다.
  - 마지막 주문(order_id가 홀수일 경우)는 변경하지 않고 그대로 유지해야 합니다.
- 📌 조건
- 이 오류를 수정하여 올바른 주문 ID와 음식(item) 쌍을 출력하는 SQL 쿼리를 작성하세요.
- 배달의민족의 배달 시스템 오류로 인해 주문 데이터가 잘못 정렬되었습니다.
  - 설명
    - order_id = 1은 order_id = 2의 item(피자)로 변경됨.
    - order_id = 2는 order_id = 1의 item(짜장면)으로 변경됨.
    - order_id = 3는 order_id = 4의 item(치킨)으로 변경됨.
    - order_id = 4는 order_id = 3의 item(떡볶이)으로 변경됨.
    - order_id = 5는 order_id = 6의 item(햄버거)으로 변경됨.
    - order_id = 6는 order_id = 5의 item(김밥)으로 변경됨.
    - order_id = 7은 마지막 홀수이므로 그대로 유지됨(삼겹살).