[GPT 출제] 일별 송금 금액 이상탐지 #금융 #SQL #MSSQL #GPT코테

본 문제는 chatGPT로 가상의 데이터셋을 생성하여 출제하도록 요청해 생성한 문제입니다.

일별 송금 금액 이상탐지

[문제 설명]

• 일별 송금 금액이 급격하게 증가하거나 감소하는 이상 징후를 모니터링하고자 합니다.
  이를 위해 하루 전 대비 송금 금액 변화율을 계산하고, 이 변화율이 ±30% 이상인 날을 ‘이상 거래일’로 탐지하려고 합니다.

[조건]

• 대상 기간: 2025-06-01 ~ 2025-06-30
• 거래 타입: send만 포함
• 거래 테이블: transactions
• 사용 컬럼: transaction_date, amount, transaction_type
• MSSQL 문법 사용
• 전날 대비 증감률 = (오늘 - 어제) / 어제 * 100
• 전날 데이터가 없는 경우(예: 6월 1일)는 제외

[출력 예시]

inactive_user_count

비활성 사용자 수

 

---

[답]

WITH base AS
(
SELECT
	FORMAT(transaction_date, 'yyyy-MM-DD') AS transaction_date,
	SUM(amount) AS total_send_amount
FROM
	transactions
WHERE
	(transaction_date BETWEEN '2025-06-01' AND '2025-06-30') AND
	transaction_type='send'
GROUP BY
	FORMAT(transaction_date, 'yyyy-MM-DD')
), lag_base AS
(
SELECT
	transaction_date,
	total_send_amount,
	LAG(total_send_amount) OVER(ORDER BY transaction_date) AS prev_amount
FROM
	base
)

SELECT
	transaction_date,
	total_send_amount,
	ROUND(((total_send_amount - prev_amount) * 1.0 / prev_amount) * 100, 2) AS 'day_over_day_change (%)',
	CASE 
		WHEN ABS((total_send_amount - prev_amount) * 1.0 / prev_amount)>=0.3 THEN 'Y'
		ELSE 'N'
	END AS is_anomaly
FROM 
	lag_base
WHERE
	prev_amount IS NOT NULL
ORDER BY
	transaction_date

---

[해결을 위해 설계한 논리]

사용 함수 : WITH AS + LAG + SUM + ROUND + ABS + CASE WHEN

1. base CTE: 하루 단위 송금액 집계
2. lag_base CTE: LAG() 함수로 전날 금액 가져오기 → 변화율 계산을 위한 기준값 확보
3. 최종 SELECT:
   • 변화율 계산: ((today - yesterday) / yesterday) * 100
   • 이상 탐지: ABS(변화율) >= 30%일 경우 Y
   • ROUND(..., 2)로 소수점 두 자리까지 표현

 

[회고]

• 단위가 크거나 이상 탐지를 하는 경우는 절댓값으로 계산하여 양측의 이상치를 모두 검토할 수 있다. (ABS 활용)
• 전날 대비 변화율 계산을  위해 LAG으로 전날 데이터 집계 후 변화량을 계산했다.