Finding maximum levels of overlap
Time Series Analysis in SQL Server
Maham Faisal Khan
Senior Data Science Content Developer
Start with some data
| StartTime | EndTime | ProductsOrdered |
|---|---|---|
| 2019-07-08 14:35:00 | 2019-07-08 16:01:00 | 13 |
| 2019-07-08 15:35:00 | 2019-07-08 17:01:00 | 13 |
| 2019-07-08 16:35:00 | 2019-07-08 18:01:00 | 17 |
| 2019-07-08 17:35:00 | 2019-07-08 19:01:00 | 15 |
| 2019-07-08 17:55:00 | 2019-07-08 17:57:00 | 1 |
| 2019-07-08 20:35:00 | 2019-07-08 22:01:00 | 13 |
Reasoning through the problem
| StartTime | EndTime | ProductsOrdered |
|---|---|---|
| 2019-07-08 14:35:00 | 2019-07-08 16:01:00 | 13 |
| 2019-07-08 15:35:00 | 2019-07-08 17:01:00 | 13 |
| 2019-07-08 16:35:00 | 2019-07-08 18:01:00 | 17 |
| 2019-07-08 17:35:00 | 2019-07-08 19:01:00 | 15 |
| 2019-07-08 17:55:00 | 2019-07-08 17:57:00 | 1 |
| 2019-07-08 20:35:00 | 2019-07-08 22:01:00 | 13 |
Reasoning through the problem
| StartTime | EndTime | ProductsOrdered |
|---|---|---|
| 2019-07-08 14:35:00 | 2019-07-08 16:01:00 | 13 |
| 2019-07-08 15:35:00 | 2019-07-08 17:01:00 | 13 |
| 2019-07-08 16:35:00 | 2019-07-08 18:01:00 | 17 |
| 2019-07-08 17:35:00 | 2019-07-08 19:01:00 | 15 |
| 2019-07-08 17:55:00 | 2019-07-08 17:57:00 | 1 |
| 2019-07-08 20:35:00 | 2019-07-08 22:01:00 | 13 |
Algorithm, step 1
CTE StartStopPoints:
SELECT
o.StartTime AS TimeUTC,
1 AS EntryCount,
ROW_NUMBER() OVER (ORDER BY o.StartTime) AS StartOrdinal
FROM #Orders o
UNION ALL
SELECT
o.EndTime AS TimeUTC,
-1 AS EntryCount,
NULL AS StartOrdinal
FROM #Orders o
Algorithm, step 1
| TimeUTC | EntryCount | StartOrdinal |
|---|---|---|
| 14:35:00 | 1 | 1 |
| 15:35:00 | 1 | 2 |
| 16:35:00 | 1 | 3 |
| 17:35:00 | 1 | 4 |
| 17:55:00 | 1 | 5 |
| 20:35:00 | 1 | 6 |
| TimeUTC | EntryCount | StartOrdinal |
|---|---|---|
| 16:01:00 | -1 | NULL |
| 17:01:00 | -1 | NULL |
| 18:01:00 | -1 | NULL |
| 19:01:00 | -1 | NULL |
| 17:57:00 | -1 | NULL |
| 22:01:00 | -1 | NULL |
Algorithm, step 2
CTE StartStopOrder:
SELECT
s.TimeUTC,
s.EntryCount,
s.StartOrdinal,
ROW_NUMBER() OVER (ORDER BY TimeUTC, StartOrdinal) AS StartOrEndOrdinal
FROM StartStopPoints s
Algorithm, step 2
| TimeUTC | EC | SO | StartEndOrdinal |
|---|---|---|---|
| 14:35:00 | 1 | 1 | 1 |
| 15:35:00 | 1 | 2 | 2 |
| 16:01:00 | -1 | NULL | 3 |
| 16:35:00 | 1 | 3 | 4 |
| 17:01:00 | -1 | NULL | 5 |
| 17:35:00 | 1 | 4 | 6 |
| TimeUTC | EC | SO | StartEndOrdinal |
|---|---|---|---|
| 17:55:00 | 1 | 5 | 7 |
| 17:57:00 | -1 | NULL | 8 |
| 18:01:00 | -1 | NULL | 9 |
| 19:01:00 | -1 | NULL | 10 |
| 20:35:00 | 1 | 6 | 11 |
| 22:01:00 | -1 | NULL | 12 |
Algorithm, step 2
| TimeUTC | EC | SO | StartEndOrdinal |
|---|---|---|---|
| 14:35:00 | 1 | 1 | 1 |
| 15:35:00 | 1 | 2 | 2 |
| 16:01:00 | -1 | NULL | 3 |
| 16:35:00 | 1 | 3 | 4 |
| 17:01:00 | -1 | NULL | 5 |
| 17:35:00 | 1 | 4 | 6 |
| TimeUTC | EC | SO | StartEndOrdinal |
|---|---|---|---|
| 17:55:00 | 1 | 5 | 7 |
| 17:57:00 | -1 | NULL | 8 |
| 18:01:00 | -1 | NULL | 9 |
| 19:01:00 | -1 | NULL | 10 |
| 20:35:00 | 1 | 6 | 11 |
| 22:01:00 | -1 | NULL | 12 |
Algorithm, step 3
| TimeUTC | StartOrdinal | StartEndOrdinal | Calc | Result |
|---|---|---|---|---|
| 14:35:00 | 1 | 1 | (2*1) - 1 | 1 |
| 15:35:00 | 2 | 2 | (2*2) - 2 | 2 |
| 16:01:00 | NULL | 3 | NULL | NULL |
| 16:35:00 | 3 | 4 | (2*3) - 4 | 2 |
| 17:01:00 | NULL | 5 | NULL | NULL |
| 17:35:00 | 4 | 6 | (2*4) - 6 | 2 |
Algorithm, step 3
SELECT
MAX(2 * s.StartOrdinal - s.StartOrEndOrdinal) AS MaxConcurrentVisitors
FROM StartStopOrder s
WHERE s.EntryCount = 1;
| MaxConcurrentVisitors |
|---|
| 3 |
Let's practice!
Time Series Analysis in SQL Server
