Analise dados de clientes para possível fraude
Neste conjunto final de exercícios, vamos analisar dados de um day spa para procurar possíveis fraudes. Nossa empresa oferece a cada cliente um passe para uso pessoal e um único passe para convidado. Temos dados de check-in e check-out de cada cliente, e os passes de convidados se vinculam ao ID do cliente base. Isso significa que pode haver sobreposição quando um cliente e um convidado fazem check-in juntos. Queremos verificar se existem pelo menos três entradas sobrepostas para um único cliente, pois isso violaria nossa regra de negócio.
A chave para pensar em entradas sobrepostas é desnormalizar os dados (unpivot) e considerar o fluxo de entradas e saídas. Vamos fazer isso primeiro.
Este exercício faz parte do curso
Análise de Séries Temporais no SQL Server
Instruções do exercício
Separe eventos de início e de término.
- Preencha a data de início da visita do cliente (
dsv.CustomerVisitStart) comoTimeUTCna parte de "entradas" da consulta. - Preencha a função de janela que chamamos de
StartStopPointspara obter o fluxo de check-ins de cada cliente, ordenado pela data de início da visita. - Preencha a data de término da visita do cliente (
dsv.CustomerVisitEnd) comoTimeUTCna parte de "saídas" da consulta.
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
-- 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