Build a stream of events
In the prior exercise, we broke out day spa data into a stream of entrances and exits. Unpivoting the data allows us to move to the next step, which is to order the entire stream.
The results from the prior exercise are now in a temporary table called #StartStopPoints. The columns in this table are CustomerID, TimeUTC, EntryCount, and StartOrdinal. These are the only columns you will need to use in this exercise. TimeUTC represents the event time, EntryCount indicates the net change for the event (+1 or -1), and StartOrdinal appears for entrance events and gives the order of entry.
This exercise is part of the course
Time Series Analysis in SQL Server
Exercise instructions
- Fill out the appropriate window function (
ROW_NUMBER()) to create a stream of check-ins and check-outs in chronological order. - Partition by the customer ID to calculate a result per user.
- Order by the event time and solve ties by using the start ordinal value.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT s.*,
-- Build a stream of all check-in and check-out events
___() OVER (
-- Break this out by customer ID
PARTITION BY s.___
-- Order by event time and then the start ordinal
-- value (in case of exact time matches)
ORDER BY s.___, s.___
) AS StartOrEndOrdinal
FROM #StartStopPoints s;