Analizza i dati dei clienti per potenziali frodi
In questo set finale di esercizi, analizzeremo i dati di una day spa per individuare potenziali frodi. La nostra azienda concede a ogni cliente un pass personale e un singolo pass per un ospite. Abbiamo i dati di check-in e check-out per ciascun cliente e i pass per ospiti sono collegati all’ID cliente di base. Questo significa che potrebbe esserci sovrapposizione quando un cliente e il suo ospite effettuano il check-in insieme. Vogliamo verificare se esistono almeno tre ingressi sovrapposti per un singolo cliente, perché ciò costituirebbe una violazione della nostra regola aziendale.
La chiave per ragionare sulle sovrapposizioni è fare un unpivot dei dati e pensare al flusso di ingressi e uscite. Partiremo da questo.
Questo esercizio fa parte del corso
Analisi delle serie temporali in SQL Server
Istruzioni dell'esercizio
Separa gli eventi di inizio e di fine.
- Inserisci la data di inizio visita del cliente (
dsv.CustomerVisitStart) comeTimeUTCnella parte "ingressi" della query. - Inserisci la funzione finestra che aliasiamo come
StartStopPointsper ottenere il flusso di check-in per ciascun cliente, ordinato per data di inizio visita. - Inserisci la data di fine visita del cliente (
dsv.CustomerVisitEnd) comeTimeUTCnella parte "uscite" della query.
esercizio interattivo pratico
Prova questo esercizio completando questo codice di esempio.
-- 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