FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

create table sessions (userId int, login timestamp, logout timestamp);

insert into sessions values
	(7, '2025-04-03 08:30:00', '2025-04-03 09:10:00'),
	(2, '2025-04-03 09:20:00', '2025-04-03 09:30:00'),
	(3, '2025-04-03 09:37:00', '2025-04-03 09:45:00'),
	(6, '2025-04-03 09:55:00', '2025-04-03 10:30:00'),
	(1, '2025-04-03 10:15:00', '2025-04-03 10:50:00'),
	(5, '2025-04-03 12:01:00', '2025-04-03 12:04:00'),
	(6, '2025-04-03 12:05:00', '2025-04-03 17:26:00'),
	(7, '2025-04-03 12:23:00', '2025-04-03 12:53:00'),
	(4, '2025-04-03 12:25:00', '2025-04-03 12:37:00');

/*- Overlaps -*/

/* By sum of running sessions at timeslot starts. */
/* Simple version, with only the timeslot start. */
with
	bounds as (select login t from sessions union select logout from sessions),
	slices as
	(
		select userId, t.*
		from sessions s join bounds t on s.login <= t.t and s.logout > t.t
    ),
	overlaps as (select t, count(1) n from slices group by t)
select * from overlaps order by t;

/* By sum of running sessions at timeslot starts, with timeslot end */
with
	bounds as (select login t from sessions union select logout from sessions),
    spans as (select t start, lead(t) over (order by t) end from bounds),
	slices as
	(
		select userId, t.*
		from sessions s join spans t on s.login < t.end and s.logout > t.start
    ),
	overlaps as (select start, end, count(1) from slices group by start, end)
select * from overlaps order by start;

/* By cumulating all +1 (log ins) and -1 (log outs) since the beginning */
with
	events as
    (
		select 1 inc, login t from sessions
		union all
		select -1 inc, logout t from sessions
	),
	-- Aggregate simultaneous events, to get 0 instead of a (parasit) -1+1 when a user logs in at the same moment another logs out:
	transitions as (select t, sum(inc) inc from events group by t),
    -- Now compute the sum of increments since the beginning.
    connected as
	(
		select t, sum(inc) over (order by t) as n from transitions
	)
select * from connected order by t;

/* Now focus on creating the round hours */
with recursive
	events as (select 1 inc, login t from sessions union all select -1 inc, logout t from sessions),
	transitions as (select t, sum(inc) inc from events group by t),
	connected as
	(
		select
			t,
			lead(t) over (order by t) as until,
			sum(inc) over (order by t) as n
		from transitions
	),
	hours as
	(
		select date_add(date(min(t)), interval hour(min(t)) hour) as h from connected
		union all
		select h + interval 1 hour as h from hours
		where h < (select max(t) from connected)
	)
select h, max(n) "sessions"
from hours left join connected s
on s.t < h + interval 1 hour and s.until > h
group by h
order by h;

t	n
2025-04-03 08:30:00	1
2025-04-03 09:20:00	1
2025-04-03 09:37:00	1
2025-04-03 09:55:00	1
2025-04-03 10:15:00	2
2025-04-03 10:30:00	1
2025-04-03 12:01:00	1
2025-04-03 12:05:00	1
2025-04-03 12:23:00	2
2025-04-03 12:25:00	3
2025-04-03 12:37:00	2
2025-04-03 12:53:00	1

start	end	count(1)
2025-04-03 08:30:00	2025-04-03 09:10:00	1
2025-04-03 09:20:00	2025-04-03 09:30:00	1
2025-04-03 09:37:00	2025-04-03 09:45:00	1
2025-04-03 09:55:00	2025-04-03 10:15:00	1
2025-04-03 10:15:00	2025-04-03 10:30:00	2
2025-04-03 10:30:00	2025-04-03 10:50:00	1
2025-04-03 12:01:00	2025-04-03 12:04:00	1
2025-04-03 12:05:00	2025-04-03 12:23:00	1
2025-04-03 12:23:00	2025-04-03 12:25:00	2
2025-04-03 12:25:00	2025-04-03 12:37:00	3
2025-04-03 12:37:00	2025-04-03 12:53:00	2
2025-04-03 12:53:00	2025-04-03 17:26:00	1

t	n
2025-04-03 08:30:00	1
2025-04-03 09:10:00	0
2025-04-03 09:20:00	1
2025-04-03 09:30:00	0
2025-04-03 09:37:00	1
2025-04-03 09:45:00	0
2025-04-03 09:55:00	1
2025-04-03 10:15:00	2
2025-04-03 10:30:00	1
2025-04-03 10:50:00	0
2025-04-03 12:01:00	1
2025-04-03 12:04:00	0
2025-04-03 12:05:00	1
2025-04-03 12:23:00	2
2025-04-03 12:25:00	3
2025-04-03 12:37:00	2
2025-04-03 12:53:00	1
2025-04-03 17:26:00	0

h	sessions
2025-04-03 08:00:00	1
2025-04-03 09:00:00	1
2025-04-03 10:00:00	2
2025-04-03 11:00:00	0
2025-04-03 12:00:00	3
2025-04-03 13:00:00	1
2025-04-03 14:00:00	1
2025-04-03 15:00:00	1
2025-04-03 16:00:00	1
2025-04-03 17:00:00	1
2025-04-03 18:00:00	NULL