Find MySql concurrent user per hour in a week

Question

This is my table, i want to find concurrent user per hour for a given week

I am trying to calculate number of concurrent users in a time range. The input looks something like the below

Table

id  user_id  login_time
1    23     2016-06-08 09:10:00
2    24     2016-06-08 08:55:00
3    25     2016-06-08 09:29:00
4    26     2016-06-08 09:40:00
5    27     2016-06-08 09:08:00
6    28     2016-06-09 13:40:00
7    31     2016-06-09 14:04:00

How to get the concurrent users in time range ?

Expected Output Table

Date	Hour	User
2014-08-04	0	3
2014-08-04	1	2
2014-08-04	2	0
2014-08-05	0	1

Similar question concurrent users sql

Answer 1

You can begin with this, but (from my opinion) it has no sense the result you are trying to get because you need to calculate the time:

• If a user enters 9:30 and left 9:35 and re-enter 9:45 is not a concurrent user but you get this in the SQL.
• If a user enters 9:59 and enter 10:01 you have a concurrent user but you won't see this with this logic of "hour"
• Concurrent user with different day (23:59 and 00:01 logins)

In any case, the SQL you are asking for: SQL Fiddle SELECT up.user_id, up.diff as TimeDiff, FROM ( SELECT TIMESTAMPDIFF(HOUR,u1.login,u2.login) as diff, u1.user_id FROM users u1 JOIN users u2 ON u1.user_id = u2.user_id AND u1.login < u2.login ) up WHERE up.diff < 1

And without DIFF time (as you requested):

SELECT 
g.id,
g.hour,
g.datelogin,
COUNT(*) as times
FROM
(SELECT HOUR(login) as hour, DATE(login) as datelogin, id FROM users) g
GROUP BY datelogin, hour, id
HAVING COUNT(*) > 1 -- This will show only counts is bigger than 1

Answer 2

I created a DBFIDDLE

• first I entered the data from your question

• half-way I changed data to what was given here: http://sqlfiddle.com/#!9/67356f/2

• first the cte1 contains the first and last date from users.

• cte2 contains all the dates between StartDate and EndDate

• cte3 contains all (24) hours for the dates.

• After this is is just counting to see if a user is logged in.

WITH RECURSIVE cte1 AS (
   SELECT 
      DATE(MIN(login_time)) StartDate,
      DATE(MAX(login_time)) EndDate
FROm users),
cte2 AS (
   SELECT cte1.StartDate
   from cte1
   union all
   select DATE_ADD(cte2.StartDate, INTERVAL 1 DAY)
   from cte2
   cross join cte1 where cte2.StartDate < cte1.EndDate
),
cte3 AS (
   SELECT StartDate, 0 as H
   FROM cte2
   UNION ALL
   SELECT StartDate, H+1 FROM cte3 WHERE H<24
)
select * from (
   select 
      StartDate as `Date`,
      H as `hour`,
      (SELECT count(*) from users 
       WHERE login_time BETWEEN DATE_ADD(StartDate, interval H HOUR) AND DATE_ADD(StartDate, interval (H+1) HOUR)
      ) as `Count`
   from cte3) x
where x.`Count` <>0
order by 1,2;