[DataLemur] 중복 결제 탐지

https://datalemur.com/questions/repeated-payments

Stripe SQL Interview Question | DataLemur

 

테이블

 

 

문제

• 중복 결제를 찾아내라.
• 중복 결제 = 10분 내에 동일한 merchant_id/credit_card_id/amount로 결제된 내역

쿼리 #1

• case when과 윈도우 함수를 사용. transaction_timestamp 순으로 order by를 하고, 이전 행의 credit_card_id, amount가 동일하고,이전행의 transaction_timestamp와 10분 미만으로 차이가 나면 error라고 기록했다.
• 맞는 쿼리이긴 하나... 쿼리가 좀 복잡하고 예쁘지 않다.

-- 10분 안/ 같은 merchant/같은 card/같은 amount
with cte as (
  SELECT * , 
      case 
        when credit_card_id = lag(credit_card_id) over(partition by merchant_id order by transaction_timestamp)
          and transaction_timestamp - lag(transaction_timestamp) over(PARTITION by merchant_id order by transaction_timestamp) <= interval '10 minutes'
          and amount = lag(amount) over(partition by merchant_id order by transaction_timestamp)
        then 'error' else null 
      end as is_error
  FROM transactions)
  
  select count(is_error) as payment_count
  from cte

 

 

쿼리 #2

• COUNT() OVER() 함수를 사용하여, 같은 merchant, 같은 카드, 같은 금액을 가지며, +-10분 안에 발생한 트랜잭션 개수를 세는 방법.
• 쿼리 #1보다 훨씬 짧고 효율적이다....👍

WITH repetitions_found AS ( 
SELECT *,
  COUNT(transaction_id) OVER(
      PARTITION BY merchant_id, credit_card_id, amount 
      ORDER BY transaction_timestamp ASC
      RANGE BETWEEN CURRENT ROW  AND INTERVAL '10 MINUTES' FOLLOWING)-1 AS repetitions
FROM transactions)

SELECT sum(repetitions)
FROM repetitions_found