[SQL] 숙제하기1

Peter·2021년 7월 13일

SQL

목록 보기

11/19

숙제

테이블 소개

CREATE TABLE raw_data.session_transaction (
  sessionid varchar(32),
  refunded boolean,
  amount int
)

CREATE TABLE raw_data.channel (
	channelName varchar(32)
)

트랜잭션과 채널 테이블이 추가됨

4가지 테이블을 가지고 과제 수행

풀어야할 내용

채널별 월 매출액 테이블 만들기(본인 스키마 밑에 CTAS로 테이블 만들기)

session_timestamp, user_session_channel, session_transaction 테이블 사용
아래와 같은 필드로 구성
- month
- channel
- uniqueUsers(총방문 사용자)
- paidUsers(구매 사용자: refund한 경우도 판매로 고려)
- conversionRate(구매사용자/총방문 사용자)
- grossRevenue(refund 포함)
- netRevenue(refund 제외)

해결내용

혹시 OUT JOIN이 필요한지, 테이블 점검

%%sql

select distinct sessionid from raw_data.session_timestamp
minus
select distinct sessionid from raw_data.user_session_channel
;

각기 다른 테이블에서 고유세션아이디를 가지고 서로 마이너스해서 값이 있으면 어느 한쪽이 다른한쪽에서 가지지못한 세션아이디를 가지고 있다는 의미
이럴 경우 이너조인이 아니라 아웃조인 형태로 데이터를 가져와줘야함

값의 오류를 체크

%%sql

select * from raw_data.session_transaction
where amount <= 0

세션 트랜즈액션 테이블에 있는 컬럼들을 가져오고
어마운트값중에 마이너스가 있는지 가져오는것!
마이너스가 있으면 오류

유일한 사용자 수 카운트

SELECT LEFT(ts, 7) "month",
  usc.channel,
  COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
GROUP BY 1, 2;
ORDER BY 1, 2;

유저세션채널과 타임스탬프를 세션아이디로 묶고
유저세션채널값을 가져오고 유저아이디를 중복없이 카운트 해서 유니크 유저스 컬럼에 넣어줌
month 에 날짜도 들어감
날짜와 채널을 기준으로 그룹핑해주고
날짜와 채널을 기준으로 정렬

복잡한 JOIN

raw_data.user_session_channel
raw_data.session_timestamp
raw_data.session_transaction

위의 3개 테이블 모드 sessionid를 기준으로 조인해야함
1, 2번 테이블을 1:1 조인이 가능함: INNER JOIN
3번의 경우 모든 레코드에 sessionid가 존재하지 않음
- LEFT JOIN(혹은 RIGHT JOIN)이 필요
- FROM에서 사용해야하는 테이블은 모든 값이 있는 1, 2번 둘중 하나가 돼야함
- sessionid가 결여된쪽이 기준이 되면 1, 2번중에 사라지는 레코드가 발생

3개 테이블 조인하기

SELECT LEFT(ts, 7) "month",
  usc.channel,
  COUNT(DISTINCT userid) uniqueUsers,
  COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;

LEFT JOIN을 통해 세션아이디가 빈 테이블을 유저세션채널에 붙임
어마운트 컬럼이 0 이상인 값을 가진 userid를 중복없이 카운트해 paidUsers 컬럼에 기입해줌

paidUsers의 conversionRate

paidUsers/uniqueUsers AS conversionRate

전체 유저중에 한번이라도 지불 한 사람의 비중을 conversionRate라고 함
문제는 소수점 표현이 안됨

paidUsers::float/uniqueUsers AS conversionRate

소수점을 표현하기 위해 paidUsers를 float 형태로 변환시켜줌
하지만 무한반복소수가 나올 가능성이 있음

ROUND(paidUsers*100.0/uniqueUsers, 2) AS conversionRate

ROUND를 통해 분자에 100을 곱해주고 소수점3자리에서 반올림 되도록 해줌
분모가 0이될 가능성 있음

ROUND(paidUsers*100.0/NULLIF(uniqueUSers, 0), 2) AS conversionRate

NULLIF를 통해 분모가 0이면 NULL값으로 만들어줌

나머지 문제까지

%%sql

SELECT LEFT(ts, 7) "month",  -- "year month"
       channel,
       COUNT(DISTINCT usc.userid) uniqueUsers,
       COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
       ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0),2) conversionRate,
       SUM(amount) grossRevenue,
       SUM(CASE WHEN refunded is False THEN amount END) netRevenue
   FROM raw_data.user_session_channel usc
   LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
   LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
   GROUP BY 1, 2
   ORDER BY 1, 2;

나머지는 위의 계산대로 처리

%%sql

--혹시 기존에 생성되어 있으면 삭제
DROP TABLE IF EXISTS adhoc.keeyong_monthly_channel_summary;

--Summary Table 생성
CREATE TABLE adhoc.keeyong_monthly_channel_summary
AS
SELECT TO_CHAR(ts, 'YYYY-MM') year_month, 
    usc.channel, 
    COUNT(DISTINCT usc.userid) unique_users, 
    COUNT(DISTINCT CASE WHEN amount>0 THEN userid END) paid_users,
    ROUND(paid_users*100./NULLIF(unique_users,0),2) conversion_rate, 
    SUM(amount) gross_revenue, 
    SUM(CASE WHEN refunded is False THEN amount
        ELSE 0 END) net_revenue
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
  LEFT JOIN raw_data.session_transaction str ON usc.sessionid = str.sessionid
GROUP BY 1, 2;

--정상적으로 생성되었는지 확인
SELECT * FROM adhoc.keeyong_monthly_channel_summary;