I have a table with 3 columns - Timestamp, UserID and Acitivity.
Activity can hold the values like - Login , Logout, Task held etc.
I want to write a query to count the no.of users who are logged in into the system on hourly basis.
SELECT MAX(timestamp) from table where Activity = 'Login' group by UserID.
This will give the latest entry for each user.
But as per my requirement, I need to get the count of users on hourly basis.
If a user is logged in at 10:00 am and is active till 12:30 am, he should be counted for 10:00 am , 11:00 am, 12:00 am in all these 3 hours.
Do you see is this possible with only these 3 columns available ?