잠재적 사기를 찾기 위한 고객 데이터 분석

이번 마지막 연습 문제에서는 데이 스파 데이터를 분석해 잠재적인 사기 징후를 찾아보겠습니다. 저희 회사는 각 고객에게 개인용 이용권 1장과 동반 1인 이용권 1장을 제공합니다. 각 고객의 체크인·체크아웃 데이터가 있으며, 동반 1인 이용권은 기준 고객 ID에 연결됩니다. 따라서 고객과 동반자가 함께 체크인하면 시간이 겹칠 수 있습니다. 단일 고객에게 겹치는 입장 기록이 3건 이상 있는지 확인하려고 합니다. 이는 당사의 비즈니스 규칙을 위반하는 사례이기 때문입니다.

겹치는 기록을 파악하는 핵심은 데이터를 언피벗(unpivot)해 입장과 퇴장의 흐름으로 바라보는 것입니다. 먼저 이 작업을 진행하겠습니다.

이 연습은 강의의 일부입니다

SQL Server에서의 시계열 분석

강의 보기

연습 안내

시작 이벤트와 종료 이벤트를 분리하세요.

쿼리의 "entrances" 부분에서 고객 방문 시작일(dsv.CustomerVisitStart)을 TimeUTC로 채우세요.
각 고객에 대해 방문 시작일 순으로 정렬된 체크인 흐름을 만들기 위해 별칭이 StartStopPoints인 창 함수를 채우세요.
쿼리의 "departures" 부분에서 고객 방문 종료일(dsv.CustomerVisitEnd)을 TimeUTC로 채우세요.

실습형 인터랙티브 연습

이 예제를 이 샘플 코드를 완성하여 풀어보세요.

-- This section focuses on entrances:  CustomerVisitStart
SELECT
	dsv.CustomerID,
	dsv.___ AS TimeUTC,
	1 AS EntryCount,
    -- We want to know each customer's entrance stream
    -- Get a unique, ascending row number
	___() OVER (
      -- Break this out by customer ID
      PARTITION BY dsv.___
      -- Ordered by the customer visit start date
      ORDER BY dsv.___
    ) AS StartOrdinal
FROM dbo.DaySpaVisit dsv
UNION ALL
-- This section focuses on departures:  CustomerVisitEnd
SELECT
	dsv.CustomerID,
	dsv.___ AS TimeUTC,
	-1 AS EntryCount,
	NULL AS StartOrdinal
FROM dbo.DaySpaVisit dsv

코드 편집 및 실행