CODEWITHSUNDEEP
mysqlsqlalgorithmanalytics
John Shedletsky
John Shedletsky

Reputation: 7168

Calculate peak concurrent users given list of sessions

I am trying to engineer a solution to this problem in MySQL, but I am also interested in the solution from a theoretical standpoint because I think it might make a good interview question.

Problem:

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

I am interested in finding the peak concurrent user count over an arbitrary range of time.

What is the most efficient way to find this number?

Upvotes: 4

Views: 2650

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562711

The easiest way to do this is to create a table for a series of timestamps, one per minute for example. There are only 1440 minutes per day, and this is a pretty small table.

Even if you had to enter every second of the day, that's only 86400 rows per day.

Then join the timestamps table to your sessions and see which has the highest count.

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;

Upvotes: 3

Related Questions