How does one count the number of online users per hour ?, Count the number of online users

How does one count the number of online users per hour ?, Count the number of online users

The system has a table that records user logon and logout logs. Now, the number of online users is measured in an hour. This is a mathematical problem.

Case 1:

| ___________ One hour ____________ |

<= Logon time logout time <=

Case 2:

| ___________ One hour ____________ |

Logon Time <logout time

Case 3:

| ___________ One hour ____________ |

Logon Time <= logout time <=

Case 4:

| ___________ One hour ____________ |

<= Logon time <logout time

With dd as (-- simulate a 24-hour period

SELECT (trunc (to_date ('1970-02-09 ', 'yyyy-mm-dd') + (LEVEL-1)/24) one_hour_1,
(Trunc (to_date ('1970-02-09 ', 'yyyy-mm-dd') + LEVEL/24) one_hour_2
FROM dual
Connect by level <= 24
)
SELECT dd. one_hour_1, COUNT (1)
FROM log_user_login d,
Dd
WHERE (d. login_time> = dd. one_hour_1 AND d. exit_time <= dd. one_hour_2)
OR (d. login_time <dd. one_hour_1 AND d. exit_time BETWEEN dd. one_hour_1 AND dd. one_hour_2)
OR (d. login_time BETWEEN dd. one_hour_1 AND dd. one_hour_2 AND d. exit_time> dd. one_hour_2)
OR (d. login_time <dd. one_hour_1 AND d. exit_time> dd. one_hour_2)
Group by dd. one_hour_1
Order by dd. one_hour_1;