Calculate maximum concurrent users based on session list

Question

I am trying to develop a solution to this problem in MySQL, but I am also interested in a solution from a theoretical point of view, because I think this can make a good interview question.

Problem:

I have a (large) user session database. For each user, I have a timestamp for the start of the session and the length of the session in seconds.

I am interested in finding the maximum number of matching users over an arbitrary time range.

What is the most efficient way to find this number?

Answer 1

- , , . 1440 , .

, 86400 .

, .

SELECT t.timestamp, COUNT(*) AS count
FROM timestamps t
JOIN sessions s ON t.timestamp BETWEEN s.start and s.start + INTERVAL s.seconds SECOND
GROUP BY t.timestamp
ORDER BY count DESC
LIMIT 1;