Analyseer klantgegevens op mogelijke fraude

In deze laatste reeks oefeningen analyseren we dagspa-gegevens om mogelijke fraude op te sporen. Ons bedrijf geeft elke klant één pas voor persoonlijk gebruik en één gastpas. We hebben check-in- en check-outgegevens voor elke klant, en gastpassen zijn gekoppeld aan het basis-klant-ID. Dit betekent dat er overlap kan zijn wanneer een klant en een gast tegelijk inchecken. We willen zien of er minstens drie overlappende registraties zijn voor één klant, want dat zou een schending van onze bedrijfsregel zijn.

De sleutel om over overlappende registraties na te denken is om onze gegevens te "unpivotten" en te kijken naar de stroom van entrees en exits. Dat doen we eerst.

Deze oefening maakt deel uit van de cursus

Tijdreeksanalyse in SQL Server

Cursus bekijken

Oefeninstructies

Splits start- en eindgebeurtenissen uit.

Vul de startdatum van het klantbezoek (dsv.CustomerVisitStart) in als TimeUTC in het deel "entrances" van de query.
Vul de vensterfunctie in die we als StartStopPoints aliassen om ons de stroom van check-ins per klant te geven, geordend op hun startdatum van het bezoek.
Vul de einddatum van het klantbezoek (dsv.CustomerVisitEnd) in als TimeUTC in het deel "departures" van de query.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- 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

Code bewerken en uitvoeren