SQL_Intermediate 01. 서비스 이해 문제 모음

01. 서비스 이해 문제 모음

---

기초 데이터 인풋

tbl_customer.csv

0.52MB

tbl_purchase.csv

4.27MB

tbl_visit.csv

7.79MB

 

 

 

Q1. 2020년 7월의 총 Revenue를 구해주세요

---

집계함수 사용WHERE절에 DATE 필터SQL문 실행 순서: 구분 분석 > FROM > WHERE > SELECT 순WHERE 절에 사용하는 것이 속도 향상

 

SELECT SUM(price)
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
	AND purchased_at < '2020-08-01';

 

 

 

Q2. 2020년 7월의 MAU를 구해주세요

---

ACTIVE USER의 이해

COUNT 함수 이해 - COUNT(*) : 모든 rows 카운팅 - COUNT(customer_id) : customer_id의 null 값 제외 카운팅 - COUNT(DISTINCT customer_id) : unique customer_id 카운팅

MAU(Monthly Active Users)는 월별 활동 사용자 수를 의미, 중복을 허용하지 않음.

 

SELECT COUNT(DISTINCT(customer_id))
FROM tbl_visit 
WHERE visited_at >= '2020-07-01'
	AND visited_at < '2020-08-01';

 

 

cf) 아래와 같은 코딩을 하면 안되는 이유 : 결과는 같을 수 있지만 ' 내부 연산량 및 처리 시간 ' 의 차이 때문에 권장 하지 않는다.

 

SELECT COUNT(DISTINCT(customer_id))
FROM tbl_visit
WHERE YEAR(visited_at) = 2020 AND MONTH(visited_at) = 7;

 

  말씀하신 "where year(visited_at) = 2020 and month(visited_at) = 7"는 SQL에서 권장하지 않는 방법입니다.  왜냐하면 이는 SQL 문법은 greedy하다는 성질을 가지기 때문입니다. 컴퓨터 공학에서 말하는 greedy란, "각 단계에서 가장 최선의 선택을 하여 정답에 도달하는 방법"을 의미합니다. 쉽게 말해 미래를 생각하지 않고, 눈 앞에 보이는 해를 최적이라고 생각하여 이를 연산하는 것을 말합니다.

 예시를 들어 greedy 알고리즘으로 마시멜로 실험(지금 선택하면 1개의 마시멜로를 받고, 1분 기다렸다 선택하면 2개의 마시멜로를 받는 문제, 마시멜로 이야기라는 책에 등장한 실험)을 진행한다면, 컴퓨터는 무조건 지금 선택하여 1개의 마시멜로를 받는 결과만 도출할 것입니다.

 왜냐하면 greedy 알고리즘은 미래를 고려하지 않고, 현재 순간에서 가장 최선의 선택만을 생각하기 때문입니다. 이러한 성질을 가진 SQL 문법은 질의한 문법에 대해 미래를 생각하지 않고, 해당 명령을 그대로 실행합니다. 그렇기 때문에 말씀하신 "where year(visited_at) = 2020 and month(visited_at) = 7" 라는 질의(query)를 하게 되면, 컴퓨터 내부에서는 year이 2020인 데이터를 모두 추출한 뒤, 그중 month가 7인 데이터를 추출하는 작업이 진행됩니다. 

 즉, 2020년 데이터를 전부 추출한 뒤에, 그중에 7월인 데이터를 뽑아낸다는 것입니다. 반면에 "where visited_at >= '2020-07-01' and visited_at < '2020-08-01'"라고 질의할 경우, 컴퓨터는 2020-07-01을 찾고 난 후, 그 뒤에 2020-08-01에 해당하는 데이터를 찾고 연산을 중지합니다. 

 즉 명령한 범위 안에서만 연산을 진행한다는 것입니다. 위의 두 개는 속도 차이가 있습니다. 2020년에 해당하는 데이터를 다 찾고 난 후에, 그 중에서 7월을 찾는 것과, 2020년 7월 1일에 해당하는 데이터에서부터 2020년 8월 1일 전에 해당하는 데이터까지만 찾는 것에는 컴퓨터 내부 연산 속도에 차이가 있습니다. 

 만약 지금처럼 실습 데이터와 같이 데이터의 크기가 작으면 상관이 없지만, 나중에 사이즈가 큰 데이터에서 저런 연산을 실행하게 되면, 컴퓨터의 내부 연산량 및 처리 시간이 어마어마하게 증가할 것입니다.  그렇기에 "where year(visited_at) = 2020 and month(visited_at) = 7"과 같은 방식은 권장하지 않는 것입니다.

 

 

 

Q3. 7월에 우리 Active 유저의 구매율(Paying Rate)은 어떻게 되나요? 

---

구매율 =  구매 유저 수  / 전체 활성 유저

PAYING RATE 이해

 

 

# 7월 방문자 수
SELECT COUNT(DISTINCT customer_id)
FROM tbl_visit 
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01';

 

 

# 7월 구매자 수
SELECT COUNT(DISTINCT(customer_id))
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
	AND purchased_at < '2020-08-01';

 

 

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

 

 

 

분모인 방문자 수 를 서브쿼리로 놓고 풀어도 가능하다.

 

SELECT ROUND (COUNT(DISTINCT(customer_id)) / 
			(SELECT COUNT(DISTINCT customer_id)
			FROM tbl_visit 
			WHERE visited_at >= '2020-07-01'
			AND visited_at < '2020-08-01')    * 100 , 2) AS 'Paying Rate'
FROM tbl_purchase 
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01';

 

 

 

 

Q4. 2020년 7월에 구매 유저의 월 평균 구매 금액은 어떻게 되나요?

---

ARPPU = Average Revenue Per Paying User 의 이해
ARPPU = 총구매금액 / 구매유저의 수

GROUP BY 사용 

서브쿼리(인라인 뷰) 사용

 

SELECT AVG(revenue)
FROM (SELECT customer_id
	  , sum(price) AS revenue
		FROM tbl_purchase
		WHERE purchased_at >= '2020-07-01'
		AND purchased_at < '2020-08-01'
		GROUP BY customer_id) revenue_by_customer;

 

 

 

Q5. 2020년 7월에 가장 많이 구매한 고객 Top3와 TOP10~ 15고객을 뽑아주세요

---

ORDER BY 사용

LIMIT, OFFSET 사용SQL문 실행순서: FROM -> Where -> Group By -> Select -> Order By

 

Select  * from 테이블명 orders LIMIT 숫자(X);

X 숫자만큼의 행 출력

Ex) 10행 출력

select * from member ORDERS LIMIT 10;

 

Select * from 테이블명 orders LIMIT 숫자(X) OFFSET 숫자(Y);

LIMIT 숫자 X  : 출력할 행의 수

OFFSET 숫자 Y : 몇번째 row부터 출력할 지. (1번째 row면 0)

Ex) 10행씩 출력

1페이지 : select * from member ORDERS LIMIT 10 OFFSET 0;

2페이지 : select * from member ORDERS LIMIT 10 OFFSET 10;

 

Select * from 테이블명 orders LIMIT 숫자1(X), 숫자2(Y);

숫자1 : X번째 row부터 출력

숫자2 : Y개의 행 출력

Ex) 10행씩 출력

1페이지 : select * from member ORDERS LIMIT 0, 10;

2페이지 : select * from member ORDERS LIMIT 10, 10;

 

TOP3 고객

SELECT customer_id, sum(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3;

 

 

TOP 10~15 고객

 

SELECT customer_id, sum(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5 OFFSET 9;

 

SELECT customer_id, sum(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 9,5;