[내일배움캠프] 25일 차 - 기초 분석 팀 과제

 

 

 

 기초 분석 팀 과제

 

 

SQL

쿼리 이탈 고객 두 가지로 구분해서 집계 결과 보여주기

 

 

처음 답안

-- OR = 전체 이탈 고객
SELECT customer_id
FROM customer_hm
WHERE club_member_status = 'LEFT CLUB'

UNION

SELECT customer_id
FROM transactions_hm
GROUP BY customer_id
HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90;


-- AND = 완전 이탈 고객
SELECT c.customer_id
FROM customer_hm c
JOIN transactions_hm t
ON c.customer_id = t.customer_id
WHERE c.club_member_status = 'LEFT CLUB'
GROUP BY c.customer_id
HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90;


-- (OR - AND) = 일반 이탈 고객
(
SELECT customer_id
FROM customer_hm
WHERE club_member_status = 'LEFT CLUB'
UNION
SELECT customer_id
FROM transactions_hm
GROUP BY customer_id
HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90
)
EXCEPT
(
SELECT c.customer_id
FROM customer_hm c
JOIN transactions_hm t
ON c.customer_id = t.customer_id
WHERE c.club_member_status = 'LEFT CLUB'
GROUP BY c.customer_id
HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90
);


-- 전체 이탈 고객 중 완전 이탈 고객과 일반 이탈 고객의 비중
WITH churned_customer AS
(
	(
		SELECT customer_id
		FROM customer_hm
		WHERE club_member_status = 'LEFT CLUB'
		UNION
		SELECT customer_id
		FROM transactions_hm
		GROUP BY customer_id
		HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90
	)
EXCEPT
	(
		SELECT c.customer_id
		FROM customer_hm c
		JOIN transactions_hm t
		ON c.customer_id = t.customer_id
		WHERE c.club_member_status = 'LEFT CLUB'
		GROUP BY c.customer_id
		HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90
	)
),
complete_churned_customer AS
(
	SELECT c.customer_id
	FROM customer_hm c
	JOIN transactions_hm t
	ON c.customer_id = t.customer_id
	WHERE c.club_member_status = 'LEFT CLUB'
	GROUP BY c.customer_id
	HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90
),
total_churned_customer AS
(
    SELECT customer_id
    FROM customer_hm
    WHERE club_member_status = 'LEFT CLUB'
    UNION
    SELECT customer_id
    FROM transactions_hm
    GROUP BY customer_id
    HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90
)
SELECT
    CAST((SELECT COUNT(customer_id) FROM complete_churned_customer) AS DECIMAL(10, 2)) /
    (SELECT COUNT(customer_id) FROM total_churned_customer) AS complete_to_total_ratio,
    CAST((SELECT COUNT(customer_id) FROM churned_customer) AS DECIMAL(10, 2)) /
    (SELECT COUNT(customer_id) FROM total_churned_customer) AS churned_to_total_ratio
FROM DUAL
LIMIT 1; 


-- 이탈 고객 수 각각 집계
WITH churned_customer AS
(
	(
		SELECT customer_id
		FROM customer_hm
		WHERE club_member_status = 'LEFT CLUB'
		UNION
		SELECT customer_id
		FROM transactions_hm
		GROUP BY customer_id
		HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90
	)
EXCEPT
	(
		SELECT c.customer_id
		FROM customer_hm c
		JOIN transactions_hm t
		ON c.customer_id = t.customer_id
		WHERE c.club_member_status = 'LEFT CLUB'
		GROUP BY c.customer_id
		HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90
	)
),
complete_churned_customer AS
(
	SELECT c.customer_id
	FROM customer_hm c
	JOIN transactions_hm t
	ON c.customer_id = t.customer_id
	WHERE c.club_member_status = 'LEFT CLUB'
	GROUP BY c.customer_id
	HAVING DATEDIFF('2019-12-31', DATE(MAX(t.t_dat))) >= 90
),
total_churned_customer AS
(
    SELECT customer_id
    FROM customer_hm
    WHERE club_member_status = 'LEFT CLUB'
    UNION
    SELECT customer_id
    FROM transactions_hm
    GROUP BY customer_id
    HAVING DATEDIFF('2019-12-31', DATE(MAX(t_dat))) >= 90
)
SELECT
    (SELECT COUNT(customer_id) FROM complete_churned_customer) AS complete_churned_customer,
    (SELECT COUNT(customer_id) FROM total_churned_customer) AS total_churned_customer,
    (SELECT COUNT(customer_id) FROM churned_customer) AS churned_customer
FROM DUAL;

 

 

개선점

 

1. HAVING 절에 집계함수 사용 가능 ✔️

• SELECT절에 집계함수 정의한 후 그 별명을 HAVING절에 사용할 필요 없다!
• HAVING절의 조건을 집계함수 사용해서 만들 수 있다

2. UNION 문법

SELECT column1
FROM table1
UNION [ALL]
SELECT column2
FROM table2

• UNION: 중복된 레코드 제거
• UNION ALL: 중복된 레코드도 포함