[패캠] SQL: 실무 문제 풀이

Q. 2020년 7월의 활성(Active) 유저의 구매율은?

구매율 = 구매유저 수 / 활성유저 수(= 방문 이력이 있는 유저 수)

 

1. 쿼리로 구매유저 수와 활성유저 수를 구한다.

 

* 활성유저 수 = 방문유저 수

아래 두 코드는 테이블만 다름

-- 구매유저 수
SELECT COUNT(DISTINCT customer_id)
FROM fastcampus.tbl_purchase 
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01';
-- 또는
-- WHERE LEFT(purchased_at, 10) BETWEEN '2020-07-01' AND '2020-07-31';

-- 활성유저 수
SELECT COUNT(DISTINCT customer_id)
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01';

 

2. 위 쿼리에서 구한 두 값(구매유저 수: 11174 / 활성유저 수: 16414)을 나눠주기

SELECT 11174 / 16414; -- 0.6808

 

SQL에서 SELECT는 '출력' 키워드 (console.log와 동일)

 

* 복잡한 쿼리 없이 단순 산술 연산으로도 충분

▶ 데이터 분석의 코딩은 '코드를 위한 코드'가 아니라 '실무 문제 해결'을 위한 것

 

 

 

3. 추가로 백분율, 반올림 처리

SELECT ROUND(11174 / 16414 * 100, 2); -- 68.08

 

결론

7월의 전체 활성 유저 중

구매한 유저의 비율은 68%

---

 

Q. 2020년 7월의 구매 유저의 월 평균 구매액(ARPPU)은?

ARPPU = Average Revenue Per Paying User
'객단가'(고객 당 구매액) 지표

 

각 고객별 구매 총액

• 한 고객이 여러 번 주문했을 가능성
• 고객 하나 당 구매한 전체 금액을 구하기 위해 GROUP BY한 후 SUM으로 출력

-- 각 고객별 구매 총액
SELECT customer_id, SUM(price)
FROM fastcampus.tbl_purchase 
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY customer_id;
-- 또는 GROUP BY 1;

인라인 뷰 서브쿼리

• FROM 절 서브쿼리
• 서브쿼리에서 만든 결과를 테이블처럼 사용

-- 각 고객별 구매 총액의 '평균' 구하기
SELECT FORMAT(CEIL(AVG(revenue)), 0) -- CEIL(올림) + AVG(평균) + FORMAT(숫자 3자리마다 컴마)
FROM (
   -- 서브쿼리(= 각 고객별 구매총액의 결과 = 테이블)
   -- 여기서 구한 'revenue'를 메인쿼리 SELECT절에서 활용 가능
   SELECT customer_id, SUM(price) AS revenue
   FROM fastcampus.tbl_purchase 
   WHERE purchased_at >= '2020-07-01'
   AND purchased_at < '2020-08-01'
   GROUP BY customer_id
) AS foo; -- 인라인 뷰 서브쿼리는 소괄호 뒤에 별칭 필수!

최종 결과

7월 고객당 평균 구매금액  =  1,884,751 원

 

---

 

Q. 2020년 7월에 가장 많이 구매한 TOP 3 고객과 TOP 10 ~ 15 고객은?

• = 고과금 유저 구하기
  • 고과금 유저는 매출에 큰 영향을 끼침

• 많이 구매한 순으로 고객을 '정렬'한 뒤
• 상위 3명과 중간 순위(10위에서 15위)를 뽑는 문제

-- 상위 3명
SELECT customer_id, SUM(price)
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY customer_id
-- 아래 추가
ORDER BY SUM(price) DESC
LIMIT 3;

 

 

상위 10위부터 15위(중간 순위) 뽑기

LIMIT 활용하여 중간 순위 뽑기
형태 1: 'LIMIT 시작 순위, 뽑을 갯수'
형태 2: 'LIMIT 뽑을 갯수 OFFSET 날릴 갯수'

 

-- 1번째 방법
SELECT customer_id, SUM(price)
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY customer_id
ORDER BY SUM(price) DESC
-- 10번째부터 5개를 뽑음
LIMIT 10, 5;

-- 2번째 방법
SELECT customer_id, SUM(price)
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY customer_id
ORDER BY SUM(price) DESC
-- OFFSET: 5개를 뽑는데, 앞에 10개는 날림
LIMIT 5 OFFSET 10;