SQL 개인과제

문제 1. (필수)

• 문제) 고객의 성별 비율을 구해보세요.
• 요구 사항) 성별 비율은 소수점 둘째 자리까지 반올림

단계별 접근

먼저 어떤 컬럼이 있는지 알아보기

비율을 계산할 때 필요한 함수 알아보기
| 구성 요소 | 설명 |
| ----------------------- | ------------------------- |
| COUNT(*) | 성별별 인원 수 |
| SUM(COUNT(*)) OVER () | 전체 인원 수를 한 줄로 반환하는 윈도우 함수 |
| ROUND(..., 2) | 소수점 둘째 자리까지 반올림 |

정답 코드

select 
	Gender,
 	round(count(*) * 100 / sum(count(*)) over(), 2) as gender_percentage
 from sparta.bankchurners b 
 where Gender is not null
 group by Gender;

SUM(...) OVER ()

윈도우 함수: 전체 결과셋에 대해 누적 계산

OVER를 뺐더니 오류가 났다.

WHY? => SUM(COUNT( )) => 집계 함수 안에 또 다른 집계 함수가 들어간 형태. 문법상 불가능.
OVER는 윈도우 함수의 시작 표시
결론: 각 성별 그룹에서 COUNT( )를 구한 다음 → 모든 성별 그룹의 총합을 SUM(...) OVER ()로 계산.
OVER()를 빼면 "전체"라는 개념이 사라지니 오류

GROUP BY vs OVER()

기능	GROUP BY	OVER()
집계된 결과만 출력	✅	❌
원래 행 + 집계 결과 같이 출력	❌	✅
누적합, 순위 계산 등	❌	✅
전체합을 행마다 보여주기	❌	✅

OVER()는 행(row) 단위로 집계된 값을 함께 보고 싶을 때 유용하다.
💡 GROUP BY는 집계 결과만 보여줌.

문제 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';

문제 3.

• 문제) 월별 거래량을 기준으로 활동이 많은 고객군을 정의하고, 그 특성(나이, 금액, 기간)을 분석해보세요.
• 요구 사항)
  • 월별 거래량(Total_Trans_Ct / Months_on_book)을 기준으로 NTILE(10) 윈도우 함수를 사용하여 상위 10% 고객군을 선정하세요.
  • 이들의 평균 나이, 총 거래액(Total_Trans_Amt), 활동 개월 수(Months_on_book) 등을 요약하세요.

접근

우선 상위 10% 고객 선정이 먼저 => 이를 위한 것이 NTILE 같은데, 정보 부족=>구글링

문법 정리

NTILE(n)은 데이터를 정렬한 후, 가능한 동일한 개수로 n등분.
각 행에 1부터 n까지 그룹 번호를 부여.

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

PARTITION BY를 생략하면 전체 행에 대해서 그룹화가 수행됨.
반대로 PARTITION BY를 지정하면 해당 파티션 내에서 그룹화를 진행하여 행 번호를 부여.

IDEA

그렇다면 상위 10%는 그룹을 10개로 나누고, 그 중 번호 1이 부여된 데이터를 가져오면 될 것이다.
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 r
	from sparta.bankchurners 
	)a
where r = 1;

문제 4.

• 문제) 신용한도(Credit_Limit)가 전체 고객 평균보다 높은 고객들만 대상으로, 이탈 여부 (Attrition_Flag)에 따른 평균 거래액(Total_Trans_Amt)과 평균 이용률(Avg_Utilization_Ratio)을 구하세요.
• 요구 사항)
  • CTE(Common Table Expression)를 사용할 것(CTE 활용 방법)
  • 평균은 소수점 2자리로 반올림할 것

CTEs란?

'Common Table Expressions' 쿼리를 통해 만들어낸 임시적인 데이터 세트.
WITH 테이블 이름 AS (테이블 만들 쿼리문)

접근

1. 우선 '이탈 여부에 따른 평균 거래액과 평균 이용률'을 구하는 쿼리 짜기

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
group by 1

2. '전체 고객 신용한도 평균'을 임시 데이터셋으로 만들기.
   => 이것보다 Credit_Limit가 큰 값을 추출하는 쿼리문 짜기.

정답 코드

with avg_limit as(
 select
 	avg(Credit_Limit) as avg_credit_limit 
 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 b
inner join avg_limit as a
	on b.Credit_Limit >= a.avg_credit_limit
group by 1;

TRY

찾아보니 CTE를 두 개 쓰는 방법도 있었다.

WITH avg_limit AS (
    SELECT AVG(Credit_Limit) AS avg_credit_limit
    FROM sparta.bankchurners
),
above_average AS (
    SELECT *
    FROM sparta.bankchurners, avg_limit
    WHERE Credit_Limit >= avg_credit_limit
)
SELECT
    Attrition_Flag,
    ROUND(AVG(Total_Trans_Amt), 2) AS Avg_Trans_Amt,
    ROUND(AVG(Avg_Utilization_Ratio), 2) AS Avg_Utilization_Ratio
FROM above_average
GROUP BY Attrition_Flag;

🔍 쿼리 설명
1. avg_limit CTE
전체 고객의 평균 Credit_Limit을 구함

2. above_average CTE
   평균 이상 신용한도를 가진 고객을 필터링

3. 메인 쿼리
   이탈 여부(Attrition_Flag) 별로 평균 거래액과 평균 이용률을 계산

처음에 의도한 방향은 이쪽이긴 한데, CTE를 두 개 쓰는 방법이 떠오르지 않아 하나만 쓰는 위쪽 방법을 택했다.

문제 5.

• 문제) 연령대별 고객의 수를 파악하고 이탈률를 대리변수로 충성도를 파악해고자 합니다.
  **다음의 이탈률 정의를 참고해서 계산해보세요.
• 요구 사항)
  • 고객 나이의 군집화
    • 20대 이하: '20s or less'
    • 30대: '30s'
    • 40대: '40s'
    • 50대 이상: '50s or more'
  • 연령대별로 다음의 통계 도출
    1. 고객 수
    2. 이탈률 (전체 고객 중 Attrited Customer 비율, 소수점 3자리까지)
       • 이탈률 계산식
         이탈률 = Attrited 수 / 전체 수 * 100
  • 서브쿼리를 사용해 연령대를 구분하고 이탈률을 계산할 것

첫 작성 코드 (오답)

select 
	Age_Group,
	count(*) as Total_Customers,
	round(sum(case when Attrition_Flag = 'Attrited Customer' then 1 else 0 end) * 100 / count(*), 3) as Attrition_Rate
from 
(
select
	case when Customer_Age <= 20 then '20s or less'
		 when Customer_Age between 30 and 39 then '30s'
		 when Customer_Age between 40 and 49 then '40s'
		 when Customer_Age >= 50 then '50s or more'
		 end as Age_Group,
	Attrition_Flag
from sparta.bankchurners
) a
group by Age_Group
order by Age_Group;

IDEA

요소	설명
서브쿼리	나이 구간을 미리 분류 (Age_Group)
SUM(CASE WHEN ...)	이탈 고객만 집계
COUNT(*)	해당 연령대 전체 고객 수
ROUND(..., 3)	소수점 3자리까지 이탈률 계산

PROBLEM

첫 결과는 위와 같았음. 20대가 NULL로 나타났는데, 왜일까?

SOLUTION

case when Customer_Age <= 20 then '20s or less'
처음에는 NULL값이 존재하는 줄 알았는데, 조건을 잘못 걸었다.
20대 이하면 <=29를 썼어야 했다.
CASE WHEN을 쓸 때는 누락되는 값이 없도록 신경써야겠다.

SQL 코드카타

문제 1.

자동차 평균 대여 기간 구하기

PROBLEM & TRY

DATEDIFF(START_DATE, END_DATE)를 사용하여 대여 기간을 구했는데, 오답이 나왔다.
우선 값이 음수로 나오니, 순서를 바꿔보았는데도 오류가 나왔다. 그렇다면 '기간'이니까 +1을 해주면 될까? 했더니 정답이었다.

정답 코드

SELECT 
    CAR_ID,
    ROUND(AVG(DATEDIFF(END_DATE, START_DATE))+1, 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

문제 2.

헤비 유저가 소유한 장소

정답 코드

SELECT *
FROM PLACES
WHERE HOST_ID IN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(*) >= 2
)
ORDER BY ID;

🔍 쿼리 설명
| 구문 | 설명 |
| ------------------------ | ----------------------------------- |
| GROUP BY HOST_ID | 사용자별로 등록된 공간 수를 계산 |
| HAVING COUNT(*) >= 2 | 두 개 이상 등록한 사용자만 추출 |
| WHERE HOST_ID IN (...) | 서브쿼리 결과에 해당하는 사용자(HOST_ID)의 공간만 조회 |
| ORDER BY ID | ID 기준으로 정렬 |

Python 코드카타

문제 1.

수박수박수박수박수박수?

IDEA

문자열 반복에 관한 문제.
단순 반복이면 n번만큼 곱한 결과를 리턴하면 될 것이다.
처음에는 for문을 생각했지만, 안 쓰는 게 더 나을 것 같았다.

'수박'이란 단어는 짝수라면 딱 절반만 반복할 것이고,
홀수라면 마지막 '박'만 빠질 것이다.
이때 여유가 있도록 (n//2) +1 만큼 곱해줘야 한다.
문자열 슬라이싱을 이용하면 어떨까?

정답

def solution(n):
    return ('수박' * (n//2 +1))[:n]