[SQL Challenge] 4회차 - groupby, exists, 퍼널분석, 리텐션분석, 코호트분석

 

< groupby 개념 및 유용한 구문 >

 

# group by

1) 사용이유

집계함수를 사용하기 위해서

 

2) 문법

select 컬럼,

           집계함수

group by 컬럼

 

 

# 현재 데이터베이스 위치 구하기

select database()

 

# 데이터베이스 선택하기

use 데이터베이스;

 

 

 

< exists 개념 >

 

 

# exists 문법

 

예제 테이블 만들기

CREATE TABLE test_user(
	id varchar(50),
	name varchar(50),
	email varchar(50)
)

 

CREATE TABLE test_info(
	id varchar(50),
	city varchar(50),
	age int
)

 

테이블에 데이터 삽입하기

INSERT INTO test_user 
VALUES
('user1', 'A', 'A@A.com'),
('user2', 'B', 'B@B.com'),
('user3', 'C', 'C@C.com'),
('user4', 'D', 'D@D.com'),
('user5', 'E', 'E@E.com');

 

 

 

INSERT INTO test_info
VALUES
('user1', 'Seoul', 15),
('user2', 'Jeju', 20),
('user3', 'Japan', 30);

 

 

 

EXISTS는 서브쿼리(subquery)에서 반환된 결과가 존재하는지 여부를 확인하는 조건 연산자입니다.

EXISTS를 사용하여 서브쿼리를 조합하면 데이터를 필터링하거나 관련된 데이터를 확인하는 데 유용하게 사용할 수 있습니다.

 

 

# test_user 테이블에서 test_info테이블의 id값과 같은 행 출력하기

 

SELECT *
FROM test_user a
WHERE EXISTS (
	SELECT *
	FROM test_info 
	WHERE a.id = id
)

 

 

 

# test_user 테이블에서 test_info테이블의 id값과 다른 행 출력하기

 

SELECT *
FROM test_user a
WHERE NOT EXISTS (
	SELECT *
	FROM test_info 
	WHERE a.id = id
)

 

 

 

# exists()만 사용한다면 exists()안의 조건에 맞는 것이면 1(true), 0(false)으로 출력하기

 

SELECT *,
	   EXISTS (
	   		SELECT *
	   		FROM test_info 
	   		WHERE a.id = id
	   ) AS cnt
FROM test_user a

 

 

 

참고자료

https://jh-tr.tistory.com/113

[MySQL] EXISTS 사용법, 예제, 활용하기 (조건 만족 검색)

 

 

 

< 4-1. 활성 유저의 리텐션 계산 >

 

목표: Facebook SQL 인터뷰에 나온 활성 유저의 리텐션을 SQL로 계산

• 학습 목표 : 코호트 리텐션의 필요성에 대해 이해하고 이를 SQL로 구현할 수 있다.
  • [페이스북] 활성 유저 리텐션 구하기

https://datalemur.com/questions/user-retention

 

결과: 사이트에서 문제를 풀고 정답 SQL 코드를 제출

 

※ 해당 사이트에서는 PostgreSQL로 작성하게 되어있다

※ 필자는 Mysql문법을 작성한 후 PostgreSQL 으로 바꿔서 제출함

 

 

SELECT *
FROM user_actions

 

 

 

[ 정답 코드 ]

 

( test데이터베이스의 user_actions 테이블 )

SELECT month(curr_month.event_date) AS MONTH,
	   count(DISTINCT user_id) AS monthly_active_users
FROM user_actions AS curr_month
WHERE EXISTS (
	SELECT last_month.user_id
	FROM user_actions AS last_month
	WHERE curr_month.user_id = last_month.user_id
	AND month(last_month.event_date) = month(date_sub(curr_month.event_date, INTERVAL 1 month))
)
AND month(curr_month.event_date) = 7
AND year(curr_month.event_date) = 2022
GROUP BY month(curr_month.event_date)

 

 

[ 출력 결과 ]

 

 

< 4-2. 코호트 리텐션 계산 >

 

목표: 세션에서 코호트 리텐션 (고객의 수)를 구한 것을 기반으로 재구매 클래식 리텐션을 비율로 구하기

• 학습 목표 : 코호트 리텐션의 필요성에 대해 이해하고 이를 SQL로 구현할 수 있다.

1. solvesql에 구글 로그인으로 가입한다.
2. 플레이그라운드로 들어간다.
3. 데이터 베이스에서 UK E-Commerce Orders를 클릭한다.

결과: 월별 재구매 리텐션을 백분율 (소수점 두번째짜리까지)로 구해 SQL 코드를 제출

 

( test데이터베이스의 user_actions 테이블 )

 

 

SELECT *
FROM sales_transaction_v

 

 

 

 

< 핵심 개념 >

 

# 문자열을 날짜 형식으로 바꾸기

STR_TO_DATE(Date, '%m/%d/%Y') as Date
# 현재 값 형식 그대로 매개변수에 전달

 

 

# %Y / %m / %d

date_format(날짜컬럼, '%Y-%m-%d')

 

%Y

4자리 년도

 

%m

2자리 월

 

%d

2자리 일

 

%01

01이라는 고정된값

 

# 핵심구문

count( distinct / case when 구문 / date_add )

count(distinct 컬럼) * 100.0 / count(distinct 컬럼)

 

 

# 인원수 구하기

 

[ 정답 코드 ]

WITH first AS (
	SELECT CustomerNo,
		   TransactionNo,
		   str_to_date(Date, '%m/%d/%Y') AS Date,
		   min(str_to_date(Date, '%m/%d/%Y')) OVER (PARTITION BY CustomerNo ORDER BY str_to_date(Date, '%m/%d/%Y'))
		   AS first_order_date
	FROM sales_transaction_v 
	WHERE CustomerNo IS NOT NULL
), mon AS (
	SELECT CustomerNo,
		   TransactionNo,
		   Date,
		   date_format(Date, '%Y-%m-%01') AS order_month,
		   date_format(first_order_date, '%Y-%m-%01') AS first_order_month 
	FROM FIRST
)
SELECT first_order_month,
	   count(DISTINCT CustomerNo) AS month0,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 1 month) = order_month THEN CustomerNo ELSE NULL END) AS month1,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 2 month) = order_month THEN CustomerNo ELSE NULL END) AS month2,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 3 month) = order_month THEN CustomerNo ELSE NULL END) AS month3,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 4 month) = order_month THEN CustomerNo ELSE NULL END) AS month4,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 5 month) = order_month THEN CustomerNo ELSE NULL END) AS month5,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 6 month) = order_month THEN CustomerNo ELSE NULL END) AS month6,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 7 month) = order_month THEN CustomerNo ELSE NULL END) AS month7,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 8 month) = order_month THEN CustomerNo ELSE NULL END) AS month8,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 9 month) = order_month THEN CustomerNo ELSE NULL END) AS month9,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 10 month) = order_month THEN CustomerNo ELSE NULL END) AS month10,
	   count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 11 month) = order_month THEN CustomerNo ELSE NULL END) AS month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month

 

 

[ 출력결과 ]

 

 

# 비율 구하기

 

[ 정답 코드 ]

WITH first AS (
	SELECT CustomerNo,
		   TransactionNo,
		   str_to_date(Date, '%m/%d/%Y') AS Date,
		   min(str_to_date(Date, '%m/%d/%Y')) OVER (PARTITION BY CustomerNo ORDER BY str_to_date(Date, '%m/%d/%Y'))
		   AS first_order_date
	FROM sales_transaction_v 
	WHERE CustomerNo IS NOT NULL
), mon AS (
	SELECT CustomerNo,
		   TransactionNo,
		   Date,
		   date_format(Date, '%Y-%m-%01') AS order_month,
		   date_format(first_order_date, '%Y-%m-%01') AS first_order_month 
	FROM FIRST
)
SELECT first_order_month,
	   round(count(DISTINCT CustomerNo)*100.0 / count(DISTINCT CustomerNo), 2) AS month0,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 1 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month1,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 2 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month2,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 3 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month3,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 4 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month4,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 5 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month5,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 6 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month6,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 7 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month7,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 8 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month8,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 9 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month9,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 10 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month10,
	   round(
	   		count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 11 month) = order_month THEN CustomerNo ELSE NULL END)*100.0
	   		/ count(DISTINCT CustomerNo), 2) AS month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month

 

 

 

[ 출력 결과 ]