문제3. 유저별 결제 및 주문 분석
고객의 주문 및 결제 데이터를 분석하는 것은 비즈니스 전략에 중요한 인사이트를 제공합니다. 특히, 배송된 주문에 대한 분석을 통해 고객들의 구매 패턴을 파악하고, 이들을 대상으로 더 나은 서비스나 마케팅 전략을 수립할 수 있습니다. 이번 과제는 배송이 완료된 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하는 것입니다.
이 분석을 통해, 배송이 완료된 고객들의 행동 패턴을 파악하고, 특정 고객군에 대한 맞춤형 마케팅을 고려할 수 있는 기초 데이터를 제공할 수 있습니다.
문제
배송된(delivered) 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하세요.
- 결과 컬럼: cnt_users, cnt_orders, sum_payment, arppu
문제풀이 핵심요약
1. 배송이 완료된 기준으로 (where 절을 이용한 배송완료기준으로 제한)
2. 고유 고객수 ( 중복없이 세기)
3. 총 주문 수 ( 배송완료기준 안에서 세기)
4. 총 결제 금액 ( 다른 테이블에 있기때문에 조인으로 가져오기)
5. 고객 1명당 평균 결제액 ( 다른테이블에 있기때문에 조인으로 가져와서 연산하기)
정답 :
SELECT count(DISTINCT mo.customer_id) AS cnt_users,
COUNT(mo.order_id) AS cnt_orders,
SUM(mp.payment_value) AS sum_payments,
SUM(mp.payment_value)/COUNT(DISTINCT mo.customer_id) AS arppu
FROM marketer_orders mo
JOIN marketer_payments mp ON mo.order_id = mp.order_id
WHERE order_delivered_customer_date IS NOT NULL;
분석 : 먼저 이문제는 조인으로 다른테이블에 있는 값을 가져와야함, 근데 아직은 자유자재 단계가 아니기때문에 처음부터 조인을쓰지는 못하고 하다가 조인으로 넘어가게됨 > 무슨말이냐면 결국에 조인을쓰면 테이블별 별칭을 추가하게 되는 과정이 필요한대 바로바로 못하고나중에 추가해야됨
SELECT count(DISTINCT mo.customer_id) AS cnt_users, > 중복없이 "고유"값 임으로 DISTINCT 붙여서 중복값 없애줘야됨
COUNT(mo.order_id) AS cnt_orders,
SUM(mp.payment_value) AS sum_payments, > 조인으로 가져왔기때문에 mp를 붙여야함
SUM(mp.payment_value)/COUNT(DISTINCT mo.customer_id) AS arppu > 값/고객수
FROM marketer_orders mo
JOIN marketer_payments mp ON mo.order_id = mp.order_id
WHERE order_delivered_customer_date IS NOT NULL; > 비어있지 않은 값만 출력 그러니까 배송완료된 기준으로 제한을 두는 것
새롭게알게된 사실
- mo/mp 등 별명은 AS를 붙이지 않고 띄어쓰기 mp하면 설정됨
- 그러므로 join같은 것을 할때
FROM marketer_orders mo
JOIN marketer_payments mp on mo.order_id = mp.order_id - 이런식으로 해야되고 또 자동으로 붙기도함
- 그러면 그다음부터는 mo. mp이렇게 붙일 수 있는데
- 2번째 줄에 order_id같이 둘다 중복으로 있는 경우는 별칭을 안정해주면 sql이 어떤걸 가져올지 몰라서 로드가안됨 그러므로 꼭 확인해야되고
- 참조한 다른테이블의 행명 payments_value 같은 행도 고유하니 굳이 별칭 안붙여줘도됨
7. join하기
- FROM 문 밑에다가 하기
FROM marketer_orders mo(별칭)
JOIN marketer_payments mp(별칭) ON mo.order_id = mp.order_id > 둘다 가지고있는 오더아이디를 기준으로 조인을 하겠다는 말 별칭은 꼭 붙여줘야되고 - 조인하고자하는 테이블명 ON 별칭.중복행 = 별칭.중복행 >> 이 과정에서
ON 뒤에 테이블 나열 순서는 상관x
W
I
L
SQL기본과제 무한반복을 돌리다가 개인과제로 넘어갔다
3번까지는 나쁘지않게 풀었다
이제 남은건
456
무한반복이 답인것 같다
아자아자 화이팅!
이런 방법으로 배송 스테이터스를 확인하는 방법이 있다
디스팅트로 유니크한 상태를 출력해서 확인하기.
그러므로 마지막 is not null 대신에 'delivered'가 정답이겠다.왜냐면 delivered만 나오니까.
is not null은 빈공간 빼고 다니까 아마 베송중,배송완료, 반품 다 나왔을듯..
