hi-yujiniya

문제 출처: chat gpt 생성

문제

당신은 커머스 플랫폼의 데이터 분석가입니다.
사용자 행동 로그를 통해, "첫 방문 후 첫 구매까지 걸린 일 수" 를 측정하려 합니다.

테이블: user_logs

각 사용자별로, 첫 visit 이후 첫 purchase까지 걸린 일 수를 계산하세요.
단, 구매가 없으면 제외합니다.

출력 컬럼

답안

with fst_visit as (
select user_id
    , event_date
    from (select user_id
    , event_date
    , rank() over(partition by user_id order by event_date) as rank
from user_logs
    where event_type='visit')
where rank =1
    )
, fst_pur as (
select user_id
    , event_date
    from (select user_id
    , event_date
    , rank() over(partition by user_id order by event_date) as rank
from user_logs
    where event_type='purchase')
where rank =1
    )
select user_id
, datediff(p.event_date, v.event_date) as days_to_purchase
from fst_visit v 
join fst_pur p
on v.user_id=p.user_id and v.event_date<=p.event_date
order by user_id

문제 출처: chat gpt 생성

문제

당신은 실험 설계를 담당한 데이터 분석가입니다.
웹사이트에서 두 가지 버튼 디자인(A와 B)의 클릭 효과를 실험하고 있습니다.

table : ab_test_logs

각 실험 그룹(A와 B)에서 전환율(CTR: 클릭률)을 계산하세요.
결과는 그룹별로 1행씩 출력하고, 전환율은 소수점 4자리까지 출력해주세요.

답안

with log as (
select event_date
    , user_id
    , test_group 
    , case when action='view' then 1 else 0 end as view_yn
    , case when action='click' then 1 else 0 end as click_yn
from ab_test_logs
group by 1,2,3,4,5)
--한 날짜에 발생한 view와 click 기준으로 CTR을 집계하기 위함
, log2 as (
select event_date
    , user_id
    , test_group
    , max(view_yn) as view_yn
    , max(click_yn) as click_yn
from log)
, ctr as (
select event_date
, test_group
, sum(view_yn) as view_cnt
, sum(click_yn) as click_cnt
from log2
group by 1,2)
---일자별로 view와 click 합산 먼저 진행하여 같은 날짜에서 발생한 이벤트 기준으로만 CTR집계
select 
test_group
, sum(view_cnt) as view
, sum(click_cnt) as click 
, round(sum(click_cnt)/sum(view_cnt),4) as CTR
from ctr

문제 출처: chat gpt 생성

문제

당신은 웹 서비스의 데이터 분석가입니다.
user_logs 테이블에는 사용자의 행동 기록이 저장되어 있습니다.

같은 사용자가 특정 방문일 이후 2일 이내에 다시 방문한 기록이 있는 경우,
그 사용자 ID와 최초 방문일을 출력하세요.

답안1

with visit_rk as (
select user_id
    , event_date
    , dense_rank() over(partition by user_id order by event_date) as visit_rk
from user_logs
where event_type = 'visit')
select other_visit.user_id
, fst_visit.event_date
from (select * from visit_rk where visit_rk = 2) other_visit
join (select * from visit_rk where visit_rk =1 ) fst_visit
on other_visit.user_id=fst_visit.user_id
group by 1, 2
having datediff(other_visit.event_date, fst_visit.event_date) <= 2

답안2

with visit_rank as (
select user_id
    , event_date
    , dense_rank() over(partition by user_id order by event_date) as visit_rank
from user_logs
where event_type='visit')
, rk_1_2 as (
select user_id
    , max(case when visit_rank=1 then event_date) as fst_visit
    , max(case when visit_rank=2 then event_date) as second_visit
from visit_rank)
select user_id
, fst_visit
from rk_1_2
where datediff(second_visit, fst_visit) <=2

난이도: level 4
문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/276035

Q. FrontEnd 개발자 찾기

DEVELOPERS 테이블에서 Front End 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요. 결과는 ID를 기준으로 오름차순 정렬해 주세요.

최초 풀이과정

1. 문제 요구 조건 확인
   DEVELOPERS 테이블에서 Front End 스킬을 보유한 개발자 정보 조회 → 스킬 테이블과 매핑 필요
   
   
2. 테이블 결합 조건 설정
   DEVELOPERS 테이블이랑 SKILLCODES 테이블은 SKILL_CODE <> CODE를 key값으로 매핑
   
   
3. 필터링 조건 설정
   SKILLCODES 테이블의 NAME = 'Front End'
   
   
4. 필요 칼럼
   - DEVELOPERS. ID
   - DEVELOPERS. EMAIL
   - DEVELOPERS. FIRST_NAME
   - DEVELOPERS. LAST_NAME
   
   
5. 최종 뷰 조건
   - DEVELOPERS. ID ASC

초기 답안

select d.ID
, d.EMAIL
, d.FIRST_NAME
, d.LAST_NAME
from DEVELOPERS d
where d.SKILL_CODE & (select s.CODE from SKILLCODES s where s.CATEGORY='Front End') != 0

• 어려웠다...
• 일단 2진수 개념을 이해하는데 쪼오금 시간이 걸림
  • 2진수 1 & 2진수 2로 연산하면, 1 안에 들어있는 2를 반환해줌
    • 예를 들면, 400 & 256 = 256, 400 & 62 = 0 (없으니까)
• 그리고 join 할 때 그냥 습관적으로 left join을 하는 경향이 있는데,
  • 이번 같은 경우는 join으로 해결해서 where 없이 쓰는게 더 간편하고 성능 우수함.
  • join + on 조건으로 필터링 효과까지 
• 마지막으로...그냥 결과를 내면, 한 사람이 여러개의 프론트 엔드 기술을 가진 경우 한 ID에 여러 행이 발생함
  • 이를 방지하기 위해 DISTINCT 처리 해줘야 함

최종 답안

select distinct d.ID
, d.EMAIL
, d.FIRST_NAME
, d.LAST_NAME
from DEVELOPERS d
join SKILLCODES s
on d.SKILL_CODE&s.CODE != 0
where s.CATEGORY='Front End'
order by d.ID

짚고 넘어갈 것

1. JOIN
   • JOIN 타입
     • LEFT JOIN : 왼쪽 테이블 데이터 유지, 오른쪽은 겹치는 부분만 가져오고 없으면 NULL
     • INNER JOIN (JOIN): 양쪽 테이블에 모두 존재하는 경우만 유지(교집합)
   • ON 절
     • 역할: 조인할 조건 (매핑 / 필터링 포함)
     • 단순 key 비교뿐 아니라, 조건 논리도 가능(=, !=, >, &, LIKE)
2. 2진수 비교 시 & (비트 AND 연산자) 개념
   • & 연산 쓰임 : 조합형 상태값 분석에 자주 쓰임
   • 예시 : user.PERM & 4 != 0 →3번째 권한(4=100)을 가졌는지 확인

난이도: Level 3
문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/59042

Q. 없어진 기록 찾기

입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

풀이 과정_최초

1. 문제 요구 조건 확인
   입양을 간 기록(ANIMAL_OUTS) 기록은 있으나, 보호소에 들어온 기록(ANIMAL_INS)은 없는 row 찾기
   
   
2. 테이블 결합 조건 설정
   입양 간 기록은 있으나, 보호소 기록은 없는 row 정의
   = ANIMAL_OUT에 있는 ID 중 ANIMAL_INS에는 없는 ID select
   
   
3. 필터링 조건 설정
   join + null로 필터링
   
   
4. 필요 칼럼
   - ANIMAL_ID
   - NAME
   
   
5. 최종 뷰 조건
   - ID와 NAME으로 asc

답안

select ANIMAL_ID
,NAME
from ANIMAL_OUTS
where ANIMAL_ID not in (
select ANIMAL_ID
from ANIMAL_INS)
order by 1,2

• 정확도: 입소기록 없는 ANIMAL_ID만 출력, 출력 컬럼 이상 없음, 정렬 기준 적합
• 성능: NOT IN 사용 시 성능 저하 가능성
• 가독성: 들여쓰기, 컬럼 정렬 등 우수
• 견고성: NOT IN과 NULL 처리 > ANIMAL_INS.ANIMAL_ID는 NOT NULL이므로 안전함 ✅

개선안

SELECT ANIMAL_ID,
       NAME
FROM ANIMAL_OUTS o
WHERE NOT EXISTS (
    SELECT 1
    FROM ANIMAL_INS i
    WHERE o.ANIMAL_ID = i.ANIMAL_ID
)
ORDER BY ANIMAL_ID, NAME;

• NOT EXIST 사용 → 서브 쿼리에 NULL이 있는 경우 더 안전한 방법
  • 서브 쿼리에 NULL이 있는경우
    • NOT IN : Null이 하나라도 있으면 NOT IN 전체가 NULL 처리 → 아무 결과도 안나옴
    • NOT EXIST : true/false로 판단하여 Null이 있어도 정상 작동

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/144854

Q. 조건에 맞는 도서와 저자 리스트 출력하기

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요.

풀이 과정_최초

1. 문제 요구 조건 확인
   '경제' 카테고리에 속한 도서의 도서 정보 & 작가 정보 불러오기
   
   
2. 테이블 결합 조건 설정
   BOOK 테이블과 AUTHOR 테이블은 AUTHOR_ID로 join 가능
   
   
3. 필터링 조건 설정
   CATEGORY = '경제'
   
   
4. 필요 칼럼
   - BOOK_ID
   - AUTHOR_NAME
   -PUBLISHED_DATE
   
   
5. 최종 뷰 조건
   - PUBLISHED_DATE로 ASC

A. 초기 답안

select b.BOOK_ID
, a.AUTHOR_NAME
, date_format(b.PUBLISHED_DATE,'%Y-%m-%d') as PUBLISHED_DATE
from BOOK b
left join AUTHOR a
on b.AUTHOR_ID=a.AUTHOR_ID
where b.CATEGORY='경제'
order by b.PUBLISHED_DATE

• 정확도: select 대상 컬럼, 필터링, 정렬 모두 충족
• 성능: JOIN 사용 시, left join 대신 inner join이 성능 향상에 더 적합
• 가독성: alias 사용, 들여쓰기, 일관성 우수
• 견고성: 저자 정보 없는 책 존재를 고려해서 left join 사용했으나, 과잉일 수 있