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.
Diese Übung ist Teil des Kurses
Time Series Analysis in SQL Server
Interaktive Übung
Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.
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 ___ ___;