SQL 과제

개요

제공    Kaggle 데이터셋 - Credit Card customers (Naive_Bayes_classifier 컬럼은 제외)

목표

신용카드 고객 데이터를 분석해 마케팅 전략을 수립할 수 있도록 인사이트를 도출.

아래의 요구사항들에 대해 SQL 쿼리로 응답해보는 것이 과제의 핵심

준비 : 데이터 불러오기

1. 파일 다운로드, 압축 해제

2. CSV파일을 열기 > Naive_bayes_classifier 컬럼 2개 삭제 > 저장

3. DBeaver 열기 > mysql* 아래 Databases 파일 (마우스 우측 클릭)

* MySql 다운 선행되어야 함. 안 했다면 링크 참조 > MySQL다운로드 안내

4. Create New Database > database name: sparta 입력 > Tables (마우스 우측 클릭) > 데이터 가져오기

5. 다음 > 경로 찾아서 CSV파일 클릭 > 인코딩 utf-8 / 헤더: top 설정 확인 > 다음 > 파일명 더블 클릭해서 컬럼별 type 확인 > 다음 > 다음 > 진행

6. 불러오기 완료 > 테이블명 소문자로 변경  > select * from sparta.bankchurners 데이터 나오는지 테스트

** 취소선 부분은 학원에서 채점을 위해 요청한 조건들이니 무시해도 된다

준비 : 데이터 확인하기

clientnum 고객 고유 번호

attrition_flag 이탈 여부(Existing Customer: 기존 고객, Attrited Customer: 이탈 고객)

customer_age 고객 나이

gender 성별 (M: 남성, F 여성) 

dependent_count 부양 가족 수

education_level 교육수준 (대학 졸업, 대학원 졸업 등)

marital_status 결혼 상태(기혼, 미혼 등)

income_category 연 소득 구간

card_category 카드 종류(blue, silver, gold, platinum)

months_on_book 카드 사용 개우러 수(고객이 카드를 보유한 개월 수)

total_relationship_count 은행과의 총 관계 계좌 수 (예금, 대출, 신용카드 등)

months_inactive_12_mon 최근 12개월 동안 비활성(거래없음) 개월 수

contacts_inactive_12_mon 최근 12개월 동안 고객센터 연락 횟수

credit_limit 신용한도

total_revolving_bal 리볼빙(이월) 잔액

avg_open_to_buy 평균 사용 신용 한도

total_amt_chng_q4_q1 1분기 대비 4분기 총 거래 횟수 변화 비율

total_trans_amt 최근 12개월간 총 거래 금액

total_trans_ct 최근 12개월간 총 거래 횟수

total_ct_chng_q4_q1 1분기 대비 4분기 총 거래 횟수 변화 비율

avg_utilization_ratio 평균 신용 한도 사용률(총 사용약/신용한도)

 

문제1. (초심자~중급자)

 

문제 고객의 서별 비율 구하기

요구 성별 비율은 소수점 둘째 자리까지 반올림

결과

select gender,
       round((count(*) * 100 / (select count(*) from sparta.bankchurners)),2) as gender_percentage
from sparta.bankchurners
group by gender
order by gender desc

설명

1) count(*) = 각 gender별 인원수 집계 * 100 (백분율을 요구했으므로)

2) (select count(*) from sparta.bankchurners) = 전체 인원 수 집계

3) round(   , 2) 소수점 둘째자리까지 반올림

4) group by 집계함수(분자 계산 등)을 위해 필요

5) order by gender desc 결과 그림에서 M이 F보다 먼저 나왔으므로 체크

 

정답

-- 성별 비율 계산
SELECT 
  Gender, 
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sparta.BankChurners), 2) AS gender_percentage
FROM sparta.bankchurners
GROUP BY Gender;

 

문제2. (초심자~중급자)

문제 이탈 고객의 평균 Credit_Limit 은 얼마인지 파악

요구 Attrition_flag = 'Attrited Customer' 대상 평균 신용한도

결과

select round(avg(credit_limit), 2) as avg_credit_limit
from sparta.bankchurners
where attrition_flag = 'Attrited Customer'

설명

1) avg(credit_limit)을 통해 이탈 고객의 신용한도 평균을 구함

2) round(  , 2) 소수점 둘째자리까지 반올림

3) where 절 : 이탈 고객만 대상으로 집계

 

정답

-- 이탈 고객 평균 Credit_Limit
SELECT 
  ROUND(AVG(Credit_Limit), 2) AS avg_credit_limit
FROM sparta.bankchurners
WHERE Attrition_Flag = 'Attrited Customer';

 

문제3. (상급자) 

문제 월별 거래량을 기준으로 활동이 많은 고객군을 정의하고, 그 특성(나이, 금액, 기간)을 분석

요구 

월별 거래량(Total_trans_Ct / Months_on_book)을 기준으로 NTILE(10) 윈도우 함수를 사용해 상위 10% 고객군을 선정

이들의 평균 나이, 총 거래액(Total_trans_amt), 활동 개월 수(Months_on_book) 등을 요약

결과

 

★ NTILE(N) 함수란?

SELECT NTILE(10) OVER (ORDER BY 기준컬럼) AS 분위, ...
FROM 테이블명;

NTILE(N)에서 N은 나누고자 하는 그룹의 개수. NTILE(10)은 10개의 분위로 나눔(낮은것부터 높은것까지)

OVER(ORDER BY 컬럼) 구문과 함께 사용되며, 지정한 컬럼의 정렬 순서에 따라 그룹이 나뉨.

각 그룹(분위)는 가능한 한 비슷한 크기로 나누지만, 전체 행 수가 N으로 나누어떨어지지 않으면, 앞쪽 그룹이 한 개씩 더 많아짐

PARTITION BY를 사용하면 파티션(그룹)별로 각각 NTILE 적용 가능

 

select round(avg(customer_age), 2) as avg_age,
       round(avg(total_trans_amt), 2) as avg_trans_amt,
       round(avg(months_on_book), 2) as avg_months
from 
(
select customer_age,
       total_trans_amt,
       months_on_book,
       ntile(10) over(order by total_trans_ct / months_on_book desc) as vips
from sparta.bankchurners
) b
where vips = 1

설명

1) 서브쿼리 

ntile(10) over (order by total_trans_ct / months_on_book desc) as vips

각 고객의 월별 거래량(total_trans_ct / months_on_book) 기준으로 내림차순 정렬 (안 그럼 거래량 적은 고객이 vips = 1이 됨)

거래량이 많은 고객이 vips=1, 그 다음이 2,... 가작 적은 고객은 10이 됨

메인쿼리에서 필요한 세 개 컬럼 추출 (customer_age, total_trans_amt, months_on_book)

2) 메인쿼리

round(avg(), 2)를 통해 평균, 소수점 둘째 자리까지 반올림

3) 조건

where vips = 1 상위 10%만 추출

 

** 참고 분모인 months_on_book이 0인 데이터가 있다면 나누기 0이 되므로 오류 발생 가능! 그래서 안전하게 하려면 아래처럼 case when을 추가해서 해야함 (그러나 min(months_on_book) 은 13인 것 확인 가능)

NTILE(10) OVER (
  ORDER BY 
    CASE WHEN months_on_book > 0 THEN total_trans_ct / months_on_book ELSE 0 END DESC
) AS vips

 

정답

WITH activity_ranked AS (
  SELECT *,
         NTILE(10) OVER (ORDER BY Total_Trans_Ct * 1.0 / Months_on_book DESC) AS activity_rank
  FROM sparta.bankchurners
)
SELECT 
  ROUND(AVG(Customer_Age), 2) AS avg_age,
  ROUND(AVG(Total_Trans_Amt), 2) AS avg_trans_amt,
  ROUND(AVG(Months_on_book), 2) AS avg_months
FROM activity_ranked
WHERE activity_rank = 1;

 

 

문제4. (EASY LEVEL ★)

문제 고객 나이(Customer_age)가 40세 이상이며 카드 거래액(Total_Trans_Amt)이 5000 이상인 고객 수

결과

select count(distinct clientnum) as customer_count
from sparta.bankchurners
where customer_age >= 40 and total_trans_amt >= 5000

설명

테이블에서 한 명의 고객이 한 행(레코드)로 저장되어 있다고 가정하면 즉 중복된 고객이 여러 행에 걸쳐 저장되어 있지 않다면, 

count(*)만으로 충분하지만, 고객 식별자(예. clientnum)이 여러 행에 중복되어 있따면, 그때는 count(distinct clientnum)이 확실

 

** 참고 : 고객 식별자가 중복되어 있는지 확인하는 방법 (확인 결과 없음)

select clientnum,
       count(*) as cnt
from sparta.bankchurners
group by clientnum
having count(*) > 1

 

정답

SELECT COUNT(*) AS customer_count
FROM sparta.bankchurners
WHERE Customer_Age >= 40
  AND Total_Trans_Amt >= 5000;

 

 

 

문제5. (MID LEVEL ★★)

문제 평균 신용한도(Credit_Limit)가 가장 높은 고객 유형(Attrition_Flag)을 구하고 그 신용한도를 구하기

결과 

select attrition_flag as Attrition_Flag,
       round(avg(credit_limit), 2) as avg_credit_limit
from sparta.bankchurners
group by 1
order by avg_credit_limit desc
limit 1

 

정답

SELECT Attrition_Flag, ROUND(AVG(Credit_Limit), 2) AS avg_credit_limit
FROM sparta.bankchurners
GROUP BY Attrition_Flag
ORDER BY avg_credit_limit DESC
LIMIT 1;

 

 

문제6. (MID LEVEL ★★★)

문제 신용한도(Credit_Limit)가 전체 고객 평균보다 높은 고객들만 대상으로, 이탈 여부(Attribution_flag)에 따른 평균 거래액(total_trans_amt)과 평균 이용률(avg_utilization_ratio)를 구하세요

요구

CTE(Common Table Expression)을 사용할 것 CTE활용방법

평균은 소수점 2자리로 반올림할 것

결과

with avg_credit as (
select avg(credit_limit) as avg_credit
from sparta.bankchurners
)
select Attrition_Flag,
       round(avg(total_trans_amt), 2) as Avg_Trans_Amt,
       round(avg(avg_utilization_ratio), 2) as Avg_Utilization_Ratio
from sparta.bankchurners, avg_credit
where credit_limit > avg_credit
group by attrition_flag

설명

1) CTE 정의

with avg_credit as 를 통해 avg_credit 라는 이름의 CTE를 정의.

이 CTE는 전체 고객의 평균 신용한도(credit_limit)을 계산하는 임시 테이블 

2) 메인쿼리

CTE의 중요점: from에 추가해줘야 함! 

select에서 필요한 컬럼들의 평균, 소수점 2자릿수까지 반환 후,

from에 테이블 , (쉼표) CTE이름까지 가져와야 함

3) where조건 충족

4) 집계함수 사용으로 group by 필수

 

정답

	WITH HighLimitCustomers AS (
	    SELECT *
	    FROM sparta.bankchurners
	    WHERE Credit_Limit > (
	        SELECT AVG(Credit_Limit)
	        FROM sparta.bankchurners
	        WHERE Credit_Limit IS NOT NULL
	    )
	)
	SELECT 
	    Attrition_Flag,
	    ROUND(AVG(Total_Trans_Amt), 2) AS Avg_Trans_Amt,
	    ROUND(AVG(Avg_Utilization_Ratio), 2) AS Avg_Utilization_Ratio
	FROM HighLimitCustomers
	GROUP BY Attrition_Flag;

 

 

문제7. (HARD LEVEL ★★★★★)

문제 연령대별 고객의 수를 파악하고 이탈률*를 대리변수로 충성도를 파악하려고 함

* 아래의 이탈률 정의 참고

요구

고객 나이의 군집화

- 20대 이하 : '20s or less'

- 30대 : '30s'

- 40대 : '40s'

- 50대 : '50s or more'

연령대별로 다음의 통계 도출

- 1. 고객 수

- 2. 이탈률 (전체 고객 중 attrited customer비율, 소수점 3자리까지) 

   ** 이탈률 = attrited 수 / 전체 수 * 100

서브쿼리를 사용해 연령대를 구분하고 이탈률을 계산할 것

결과

select age_group as Age_Group,
       count(*) as Total_Customers,
       round((sum(if(attrition_flag= 'attrited customer', 1, 0)) / count(*)), 3) as Attrition_Rate
from (
select *,
	case
		when customer_age < 30 then '20s or less'
		when customer_age between 30 and 39 then '30s'
		when customer_age between 40 and 49 then '40s'
		else '50s or more'
	end as age_group	
from sparta.bankchurners
) a
group by age_group
order by age_group

 

정답

SELECT 
    Age_Group,
    COUNT(*) AS Total_Customers,
    ROUND(
        SUM(CASE WHEN Attrition_Flag = 'Attrited Customer' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 
        3
    ) AS Attrition_Rate
FROM (
    SELECT *,
        CASE 
            WHEN Customer_Age < 30 THEN '20s or less'
            WHEN Customer_Age BETWEEN 30 AND 39 THEN '30s'
            WHEN Customer_Age BETWEEN 40 AND 49 THEN '40s'
            ELSE '50s or more'
        END AS Age_Group
    FROM sparta.BankChurners
) AS grouped
GROUP BY Age_Group
ORDER BY Age_Group;