SQL Practical Example: Session Length with Overlapping Time Intervals
Interesting example of calculating session length in online poker with overlapping time intervals.
Calculating Session Length in Online Poker
Dealing with overlapping time intervals can be challenging, but with the right approach, it's manageable. I have been working for an online gambling company for two years. This is where I encountered the Session Length problem in SQL.
In online poker, total session length is one of the most important metrics for business stakeholders. At first glance, the problem seemed straightforward, but when I discovered that players could join multiple games simultaneously, it became quite a puzzle. Let me walk you through how I solved this challenge using SQL.
Problem Definition
Imagine we have a dataset (
PokerGames)tracking the start and end times of each poker game for all players.
PlayerID
GameID
GameStartTime
GameEndTime
We need to calculate session length per day for each player.
CREATE TABLE PokerGames (
PlayerID INT,
GameID CHAR(1),
GameStartTime TIMESTAMP,
GameEndTime TIMESTAMP
);
INSERT INTO PokerGames (PlayerID, GameID, GameStartTime, GameEndTime)
VALUES
(1, 'A', '2023-01-01 14:00:00', '2023-01-01 15:00:00'),
(1, 'B', '2023-01-01 14:30:00', '2023-01-01 15:30:00'),
(1, 'C', '2023-01-01 16:00:00', '2023-01-01 16:30:00'),
(1, 'D', '2023-01-01 16:00:00', '2023-01-01 17:00:00')
;
SELECT PlayerID
, GameID
, TO_CHAR(GameStartTime, 'YYYY-MM-DD HH24:MI:SS') AS GameStart
, TO_CHAR(GameEndTime, 'YYYY-MM-DD HH24:MI:SS') AS GameEnd
FROM PokerGames;
Let's observe Player 1's game times:
Game A: 14:00 → 15:00 → Duration = 1H
Game B: 14:30 → 15:30 → Duration = 1H
Game C: 16:00 → 16:30 → Duration = 0.5H
Game D: 16:00 → 17:00 → Duration = 1H
This is exactly where the issue arises. If we sum the durations immediately, we get a session length of 3.5H. However, we have two 30-minute overlaps: one between 14:30 and 15:00, and another between 16:00 and 16:30. We should take these duplications into account and present a session length of 2.5 hours for Player 1, instead of 3.5 hours.
The Challenge
Calculating the total playtime requires us to account for overlapping intervals so that we don't double-count any time. We need a way to merge overlapping sessions and sum up the unique time intervals.
The Solution
To solve this, we'll:
Unpivot Start and End Times and Assign Indexes: Transform start and end times into a single column. Mark the start times with
1and end times with-1.Calculate the Cumulative Sum: Use a running total to identify overlapping periods.
Identify Distinct Time Ranges: Determine when a player is actively playing.
Calculate Total Session Length per Interval: Sum the durations of distinct ranges.
Step-by-Step SQL Query
Step I: Unpivot Start and End Times and Assign Indexes
First, we unpivot the game start and end times into one column (GameTime) and assign an index of 1 to the start times and -1 to the end times (GameIndex). This allows us to identify whether a game has started or ended at each time point.
SELECT PlayerID
, TO_CHAR(GameStartTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, 1 AS GameIndex
FROM PokerGames
UNION ALL
SELECT PlayerID
, TO_CHAR(GameEndTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, -1 AS GameIndex
FROM PokerGames;
Step II: Calculate the Cumulative Sum
Next, we create a cumulative sum (IndexSum) of the index for each player, ordered by time. The goal is to identify overlapping time intervals. When a game starts, the sum increases by 1; when it ends, the sum decreases by 1. Thus, a sum greater than 0 indicates active gameplay and a sum greater than 1 indicates overlapping games.
SELECT overlap_indicator.*
, SUM(overlap_indicator.GameIndex) OVER (
PARTITION BY overlap_indicator.PlayerID
ORDER BY overlap_indicator.GameTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS IndexSum
FROM (
SELECT PlayerID
, TO_CHAR(GameStartTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, 1 AS GameIndex
FROM PokerGames
UNION ALL
SELECT PlayerID
, TO_CHAR(GameEndTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, -1 AS GameIndex
FROM PokerGames
) overlap_indicator;
Step III: Identify Distinct Time Ranges
With the cumulative sum, we identify and index each distinct time range. We consider a new range to start when a game begins (when GameIndex is 1 and IndexSum becomes 1) and end when all active games finish (when GameIndex is -1 and IndexSum becomes 0). We use the FLOOR function to create an index for each range (GameRange). GameRange is then used to identify unique session intervals.
For example:
Session 1: 14:00 → 15:30
Session 2: 16:00 → 17:00
SELECT range_indices.*
, FLOOR((ROW_NUMBER() OVER (
PARTITION BY range_indices.PlayerID
ORDER BY range_indices.GameTime) - 1) / 2
) AS GameRange
FROM (
SELECT overlap_indicator.*
, SUM(overlap_indicator.GameIndex) OVER (
PARTITION BY overlap_indicator.PlayerID
ORDER BY overlap_indicator.GameTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS IndexSum
FROM (
SELECT PlayerID
, TO_CHAR(GameStartTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, 1 AS GameIndex
FROM PokerGames
UNION ALL
SELECT PlayerID
, TO_CHAR(GameEndTime, 'YYYY-MM-DD HH24:MI:SS') AS GameTime
, -1 AS GameIndex
FROM PokerGames
) overlap_indicator
) range_indices
WHERE (range_indices.GameIndex = 1 AND range_indices.IndexSum = 1)
OR (range_indices.GameIndex = -1 AND range_indices.IndexSum = 0);
Step IV: Calculate the Total Session Length Per Interval
For each distinct range identified in the previous step, we calculate the duration by subtracting the minimum time from the maximum time, converted to hours. We then group by the player, each individual game date, and range to get the total session length per interval.
SELECT PlayerID
, CAST(GameTime AS DATE) AS GameDate
, GameRange
, EXTRACT(EPOCH FROM
(MAX(overlap_stats.GameTime) - MIN(overlap_stats.GameTime))
) / 60 AS Duration
FROM (
SELECT range_indices.*
, FLOOR((ROW_NUMBER() OVER (
PARTITION BY range_indices.PlayerID
ORDER BY range_indices.GameTime) - 1) / 2
) AS GameRange
FROM (
SELECT overlap_indicator.*
, SUM(overlap_indicator.GameIndex) OVER (
PARTITION BY overlap_indicator.PlayerID
ORDER BY overlap_indicator.GameTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS IndexSum
FROM (
SELECT PlayerID
, GameStartTime AS GameTime
, 1 AS GameIndex
FROM PokerGames
UNION ALL
SELECT PlayerID
, GameEndTime AS GameTime
, -1 AS GameIndex
FROM PokerGames
) overlap_indicator
) range_indices
WHERE (range_indices.GameIndex = 1 AND range_indices.IndexSum = 1)
OR (range_indices.GameIndex = -1 AND range_indices.IndexSum = 0)
) overlap_stats
GROUP BY PlayerID, CAST(GameTime AS DATE), GameRange
ORDER BY GameDate, PlayerID;
This query successfully calculates the total session length per unique interval for each player, taking into account the overlapping intervals. You can aggregate duration further if you need it per day.
I hope you liked the article. If you have questions or need further clarification, leave a comment below or reach out directly.
✅ Thank you for reading my article on SA Space! I welcome any questions, comments, or suggestions you may have.
Keep Knowledge Flowing by following me for more content on Solutions Architecture, System Design, Data Engineering, Business Analysis, and more. Your engagement is appreciated. 🚀
🔔 You can also follow my work on LinkedIn | Substack | My Resume