MySQL count number of user activities (table a) falling in two login session datetime (table b) – Phpmyadmin

I have a table with all the users activities and another table with all the users login events. Each of them has a datatime field (creation).

I need to count for a specific user with one query how many activities he/she has done falling into the datetime range in between two consecutive logins (assuming that in between a logout or session expiration event has happened) and after the last one.

CREATE TABLE `STAGING_admin_activities` (
  `id` int(11) UNSIGNED NOT NULL,
  `admin_id` int(11) UNSIGNED DEFAULT NULL,
  `requested_URL_and_data` tinytext DEFAULT NULL,
  `post_data` text DEFAULT NULL,
  `result` tinytext DEFAULT NULL,
  `useragent_referred_URL` tinytext DEFAULT NULL,
  `useragent_browser` tinytext DEFAULT NULL,
  `useragent_browser_version` tinytext DEFAULT NULL,
  `useragent_robot` tinytext DEFAULT NULL,
  `useragent_mobile` tinytext DEFAULT NULL,
  `useragent_platform` tinytext DEFAULT NULL,
  `useragent_languages` tinytext DEFAULT NULL,
  `ip_country_code` varchar(2) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `remarks` tinytext DEFAULT NULL,
  `creation` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `STAGING_admin_login_logout` (
  `id` int(11) UNSIGNED NOT NULL,
  `operation` enum('login','logout') NOT NULL,
  `result` tinyint(1) UNSIGNED DEFAULT NULL,
  `admin_id` int(11) UNSIGNED DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `ip_country_code` varchar(2) DEFAULT NULL,
  `creation` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To simplify the data example keeping just the meaningful fields

STAGING_admin_login_logout

STAGING_admin_activities

the expected result is something similar of this simplified table

expected results filtered on admin_id = 5

As you can see the field last_activity_time come from the activties table "creation" field and is the higher datetime in between two login events. The last column number_of_activities is the desired result, the number of activities including the last one falling in the time range between login_time (originally coming from the ‘creation’ field of the login events table) and last_activity_time (originally coming from the ‘creation’ field of the activities table).

In the following image, it is shown the relations on the datetime field of the two tables and how they are considered on creating the resulting one.

Concerning the DB version on PhpMyAdmin I can read this: "Versione del server: 10.3.32-MariaDB – MariaDB Server".

UPDATE 1

Following D-Shih ideas I tryed this

SELECT *
FROM
( SELECT operation,result,admin_id,creation as login_creation, LEAD(creation) OVER(PARTITION BY admin_id ORDER BY creation) next_login_time 
FROM STAGING_admin_login_logout
WHERE operation = "login" AND admin_id = 5 ) login_logout
INNER JOIN STAGING_admin_activities ON STAGING_admin_activities.admin_id = login_logout.admin_id
WHERE STAGING_admin_activities.creation BETWEEN login_logout.login_creation AND login_logout.next_login_time
ORDER BY STAGING_admin_activities.creation ASC

that actually gives as many rows as the ativities of user with user_id = 5 and falling in the range of datetime of two consecutive logins. What is in common to all the activities rows in between two logins is the field login_creation that has the same datetime. Now I should count the activities that as the same login_creation field value; I tryed in this way

SELECT *, COUNT(*) as number_of_activities
FROM
( SELECT operation,result,admin_id,creation as login_creation, LEAD(creation) OVER(PARTITION BY admin_id ORDER BY creation) next_login_time 
FROM STAGING_admin_login_logout
WHERE operation = "login" AND admin_id = 5 ) login_logout
INNER JOIN STAGING_admin_activities ON STAGING_admin_activities.admin_id = login_logout.admin_id
WHERE STAGING_admin_activities.creation BETWEEN login_logout.login_creation AND login_logout.next_login_time
GROUP BY login_logout.login_creation
ORDER BY STAGING_admin_activities.creation ASC

now checking if it is correct.

UPDATE 2

The query closest to the desired result is the following one, I still have a couple of troubles for which I kind ask for an help

SELECT *, COUNT(*) as number_of_activities, MAX(STAGING_admin_activities.creation) as last_activity_creation, TIMESTAMPDIFF(MINUTE,login_creation,next_login_time) as time_lenght
FROM
    ( SELECT operation,result,admin_id,creation as login_creation, LEAD(creation) OVER(PARTITION BY admin_id ORDER BY creation) next_login_time 
    FROM STAGING_admin_login_logout
    WHERE operation = "login" AND admin_id = 5 ) login_logout
INNER JOIN STAGING_admin_activities ON STAGING_admin_activities.admin_id = login_logout.admin_id
WHERE STAGING_admin_activities.creation BETWEEN login_logout.login_creation AND login_logout.next_login_time AND
      requested_URL_and_data = '/adminPanel/Products/addUpdate' AND
      login_logout.result = 1      
GROUP BY login_logout.login_creation
ORDER BY STAGING_admin_activities.creation ASC

The first problem is that the last group of activities have a time range equal in between the last login creation datatime and NULL since there is not a next login time that determines the end of the range. I need to extract some how the last/max activity creation datetime, lets call it ‘max_activity_creation’, and extend the condition on time as following

WHERE (STAGING_admin_activities.creation BETWEEN login_logout.login_creation AND login_logout.next_login_time **OR STAGING_admin_activities.creation >= max_activity_creation**) AND
      requested_URL_and_data = '/adminPanel/Products/addUpdate' AND
      login_logout.result = 1 

How can I extract this ‘max_activity_creation’ value?

The second issue is that I should calculate the time length of the activities as the datetime of the last activity before a new login and the datetime of the previous login. Let’s call the dateime of this last activity as ‘last_activity_creation’ as in the initial SELECT.

SELECT *, COUNT(*) as number_of_activities, MAX(STAGING_admin_activities.creation) as last_activity_creation, TIMESTAMPDIFF(MINUTE,login_creation,next_login_time) as time_lenght

actually ‘last_activity_creation’ is displayed correctly but if I use it in side the TIMESTAMPDIFF

TIMESTAMPDIFF(MINUTE,login_creation,last_activity_creation)

I get an error. Any ideas?