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.
Cet exercice fait partie du cours
Time Series Analysis in SQL Server
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de 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 ___ ___;