avatar
aggregate function in MySQL MySQL

First and foremost, we will define our exercise as follows: Assume we have table `user_logs` to capture the login and logout sessions of users, and we want to query this table to get list of user login/logout events in our system.

Leveraging subqueries to return only a single value (the latest date) for each session involves using MySQL aggregate functions such as AVG(), COUNT(), SUM(), MIN(), and MAX().

SELECT 
   ul.user_id AS user_id,
   (SELECT 
   MAX(DATE_FORMAT(CONVERT_TZ(a.f_date, 'GMT', 'Asia/Bangkok'), '%Y-%m-%d %H:%i')) as f_date
   FROM user_logs a 
   WHERE a.session_id = ul.session_id AND a.action = 'Valid Login') AS signIn,
   (SELECT 
   MAX(DATE_FORMAT(CONVERT_TZ(b.f_date, 'GMT', 'Asia/Bangkok'), '%Y-%m-%d %H:%i')) as f_date
   FROM user_logs b 
   WHERE b.session_id = ul.session_id AND b.action = 'User Logout') AS signOut
   FROM user_logs ul
   WHERE ul.session_id != '' 
   GROUP BY ul.user_id, ul.session_id
   LIMIT 0, 15;

Here is result:

fds

You need to login to do this manipulation!