Hi,
I have a table in which i have the information about actions along with the date/time when the action happened. I want to know maximum simultaneous requests for a given action at any given time.
Example: Users signing into web site. Here is some sample data (using integers at timestamps to keep the example simple) :
1. User A logs in at time 0.
2. User B logs in at time 0.
3. User C logs in at time 1.
4. User D logs in at time 3.
5. User A logs out at time 4.
6. User E, F and G logs in at time 5.
7. User C logs out at time 6.
8. All Users log out at time 7.
Now, I want to run a query on start time = 0, end time = 7 and I want to know what was the maximum simultaneous count for the number of users logged in?
Based on the data, the user/time distribution is:
time 0: 2 users
time 1: 3 users
time 2: 3 users
time 3: 4 users
time 4: 3 users
time 5: 6 users
time 6: 5 users
time 7: 0 users
and so, max. simultaneous users between 0 and 7 is 6 (at time = 5). How can I find this using SQL query?
Table structure:
1. Each row has log-in and log-out time.
2. Record is written to the table once user logs-out. Till that time, its kept in memory. (Changing this behavior is not an option right now).
You can assume that this table only stores log-in and log-out actions.