Complete the fraud analysis
So far, we have broken out day spa data into a stream of entrances and exits and ordered this stream chronologically. This stream contains two critical fields, StartOrdinal and StartOrEndOrdinal. StartOrdinal is the chronological ordering of all entrances. StartOrEndOrdinal contains all entrances and exits in order. Armed with these two pieces of information, we can find the maximum number of concurrent visits.
The results from the prior exercise are now in a temporary table called #StartStopOrder.
This exercise is part of the course
Time Series Analysis in SQL Server
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
s.CustomerID,
MAX(2 * s.StartOrdinal - s.StartOrEndOrdinal) AS MaxConcurrentCustomerVisits
FROM #StartStopOrder s
WHERE s.EntryCount = 1
GROUP BY s.CustomerID
-- The difference between 2 * start ordinal and the start/end
-- ordinal represents the number of concurrent visits
HAVING MAX(2 * s.___ - s.___) > 2
-- Sort by the largest number of max concurrent customer visits
ORDER BY ___ ___;