Analiza datos de clientes para detectar posible fraude
En este último conjunto de ejercicios, analizaremos datos de un spa de día para detectar posible fraude. Nuestra empresa da a cada cliente un pase para uso personal y un único pase para un acompañante. Tenemos datos de check-in y check-out para cada cliente, y los pases de acompañante se vinculan al ID base del cliente. Esto significa que puede haber solapamiento cuando un cliente y su acompañante hacen check-in a la vez. Queremos comprobar si hay al menos tres entradas solapadas para un mismo cliente, ya que eso infringiría nuestra regla de negocio.
La clave para pensar en entradas solapadas es despivotar los datos y considerar el flujo de entradas y salidas. Lo haremos primero.
Este ejercicio forma parte del curso
Análisis de series temporales en SQL Server
Instrucciones del ejercicio
Separa los eventos de inicio y los de fin.
- Rellena la fecha de inicio de la visita del cliente (
dsv.CustomerVisitStart) comoTimeUTCen la parte de "entradas" de la consulta. - Completa la función de ventana que aliasamos como
StartStopPointspara obtener el flujo de check-ins de cada cliente, ordenado por la fecha de inicio de su visita. - Rellena la fecha de fin de la visita del cliente (
dsv.CustomerVisitEnd) comoTimeUTCen la parte de "salidas" de la consulta.
ejercicio interactivo práctico
Prueba este ejercicio completando este código de ejemplo.
-- 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