Repeated Payments: Stripe SQL Interview Question

문제 풀이 링크: https://datalemur.com/questions/repeated-payments

Stripe SQL Interview Question | DataLemur

---

🧾 중복 결제 탐지 문제 요약

• 목적: 동일한 merchant, credit card, 결제 금액으로 10분 이내에 발생한 중복 결제 건수를 식별하고 개수 확인
• 조건:
  • merchant_id, credit_card_id, amount가 모두 같아야 함
  • 결제 시각 간격이 10분 이내여야 함
  • 중복된 결제에서 첫 번째 결제는 제외하고, 이후 건만 중복 결제로 간주
  • 예: 동일 조건으로 두 번 결제된 경우 → 1건의 중복 결제로 집계됨

---

1. 내 풀이

• LAG() 함수를 사용하여 이전 거래 시각(previous_transaction)을 구함
  • merchant_id, credit_card_id, amount 기준으로 그룹을 나눔
  • 거래 시각 기준 오름차순 정렬(정확도를 위해 필요)
• transaction_timestamp - previous_transaction을 계산하여 시간 간격(interval) 생성
• 10분 이하인 경우만 필터링 (time_difference <= interval '10 minutes')
• 중복 결제 건수(COUNT(*))를 집계

 

WITH transaction_prev AS (
  SELECT 
    transaction_timestamp, 
    LAG(transaction_timestamp) OVER (
      PARTITION BY merchant_id, credit_card_id, amount 
      ORDER BY transaction_timestamp) previous_transaction
  FROM transactions
  ),
  time_diff AS (
  SELECT 
    transaction_timestamp, 
    previous_transaction,
    transaction_timestamp - previous_transaction time_difference
  FROM transaction_prev)
  
SELECT COUNT(*) payment_count
FROM time_diff
WHERE time_difference <= interval '10 minutes';

---

2. 정답 풀이

• LAG() 함수를 사용하여 이전 거래 시각을 구함
  • merchant_id, credit_card_id, amount 기준으로 그룹을 나누고, 거래 시각 기준 오름차순 정렬
• transaction_timestamp - previous_timestamp 차이를 초 단위(epoch) 로 변환 후 60으로 나눠 분 단위로 계산
• 10분 이하인 경우만 필터링 (minute_difference <= 10)
• 중복 결제 건수(COUNT(*))를 집계

 

WITH payments AS (
  SELECT 
    EXTRACT(EPOCH FROM transaction_timestamp - 
      LAG(transaction_timestamp) OVER(
        PARTITION BY merchant_id, credit_card_id, amount 
        ORDER BY transaction_timestamp)
    )/60 AS minute_difference 
  FROM transactions) 

SELECT COUNT(*) AS payment_count
FROM payments 
WHERE minute_difference <= 10;