스파르타 내일배움캠프_본캠프_data11기 김선영_TIL_Day 20

[오늘의 학습]

• SQL 코드카타 (중급 4개)
• Python 코드카타 (코딩 테스트 입문 5개)
• 데이터 전처리/시각화 세션 3회차

---

[학습내용 정리]

# SQL 코드카타

☑️ 조건에 맞는 사용자 정보 조회하기 [🔗] 🔴

`USED_GOODS_BOARD`와 `USED_GOODS_USER` 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요.
이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요.
결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

• 나의 코드 / 의도
  • concat()함수 사용하여 나눠져 있는 컬럼을 합침.
  • 글을 올린 사용자를 보는 문제, inner join 사용
  • writer_id별 업로드한 글 개수가 3개 이상인 유저 파악 위해 where 서브쿼리 사용.
    • from USED_GOODS_BOARD as b2
      글 업로드 보는 테이블에서만 확인
    • where b2.WRITER_ID = b.WRITER_ID
      바깥 쿼리에서 확인하는 b.WRITER_ID 하나에 대한 count 계산.
    • group by b2.WRITER_ID
      having count(b2.WRITER_ID) >= 3
      having에 조건 걸기 위해 group by도 사용한 케이스.

select 
    u.USER_ID
    , u.NICKNAME
    , concat(u.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_BOARD b
    join USED_GOODS_USER u
    on b.WRITER_ID = u.USER_ID
where b.WRITER_ID = (
    select count(b2.WRITER_ID) as cnt_writer
    from USED_GOODS_BOARD as b2
    where b2.WRITER_ID = b.WRITER_ID
    group by b2.WRITER_ID
    having count(b2.WRITER_ID) >= 3
    )

• 정답 코드 / 오답노트

select 
    u.USER_ID
    , u.NICKNAME
    , concat(u.CITY, " ", u.STREET_ADDRESS1, " ", u.STREET_ADDRESS2) as "전체주소"-- full address
    , concat(substr(u.TLNO, 1, 3), "-"
             , substr(u.TLNO, 4, 4), "-"
             , substr(u.TLNO, 8, 4)) as "전화번호"
from USED_GOODS_BOARD b
    join USED_GOODS_USER u
    on b.WRITER_ID = u.USER_ID
where b.WRITER_ID = (
    select c.WRITER_ID
    from (
        select 
            b2.WRITER_ID
            , count(b2.WRITER_ID) as cnt_writer
        from USED_GOODS_BOARD as b2
        where b2.WRITER_ID = b.WRITER_ID
        group by b2.WRITER_ID
        having count(b2.WRITER_ID) >= 3
        ) c
    )
group by u.USER_ID
order by u.USER_ID desc;

 

☑️ 5월 식품들의 총매출 조회하기 [🔗]

`FOOD_PRODUCT`와 `FOOD_ORDER` 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요.
이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

• 나의 코드 / 의도
  • 컬럼 price와 컬럼 amount를 곱하여 총매출 계산
  • 주문이 없는 데이터도 보기 위해 `left join`, `and` 조건으로 "2022-05"에 주문한 row 필터링
  • product_id와 product_name은 매칭되는 컬럼이니까, `group by` 는 product_id로만.

select
    p.PRODUCT_ID
    , p.PRODUCT_NAME
    , p.PRICE * o.AMOUNT as TOTAL_SALES
from FOOD_PRODUCT p
    left join FOOD_ORDER o
    ON p.PRODUCT_ID = o.PRODUCT_ID
    AND o.PRODUCE_DATE like "2022-05%"
group by p.PRODUCT_ID
order by TOTAL_SALES desc, p.PRODUCT_ID

• 정답 코드 / 오답노트
  • 집계함수(sum()) 빼먹음. 바보냐?
  • 날짜 기간은 `like`보단 `between and`가 더 안전.

select
    p.PRODUCT_ID
    , p.PRODUCT_NAME
    , sum(p.PRICE * o.AMOUNT) as TOTAL_SALES
from FOOD_PRODUCT p
    left join FOOD_ORDER o
    ON p.PRODUCT_ID = o.PRODUCT_ID
where o.PRODUCE_DATE between "2022-05-01" and "2022-05-31"
group by p.PRODUCT_ID, p.PRODUCT_NAME
order by TOTAL_SALES desc, p.PRODUCT_ID

---

[내일의 학습]

내일 주 목표: 시각화 배우기 전, 더 복잡해지기 전에 데이터 전처리 함수 사용에 익숙해지기!

• 데이터 전처리/시각화 세션 4회차
• 데이터 전처리 연습 (w. Claude)
• SQL, Python 코드카타