Query group by pair of column result

Question

Currently my dataset consist of 4 columns, id, status, user_id, created_date

so after a while the data can be like this

(1, 'LOGIN', '2019-07-16 07:06:55', 'Bob')
(2, 'LOGOUT', '2019-07-16 07:29:13', 'Bob')
(3, 'LOGIN', '2019-07-16 07:30:31', 'Bob')
(4, 'LOGOUT', '2019-07-16 07:49:50', 'Bob')
(5, 'LOGIN', '2019-07-16 08:05:55', 'Tom')
(6, 'LOGOUT', '2019-07-16 08:15:13', 'Tom')
(7, 'LOGIN', '2019-07-16 09:13:55', 'John')
(8, 'LOGOUT', '2019-07-16 09:20:13', 'John')

I am trying to make it like this

(1, '2019-07-16 07:06:55', '2019-07-16 07:29:13', 'Bob', 22.5800)
(2, '2019-07-16 07:30:31', '2019-07-16 07:49:50', 'Bob', 19.5800)
(3, '2019-07-16 08:05:55', '2019-07-16 08:15:13', 'Tom', 9.5800)
(4, '2019-07-16 09:13:55', '2019-07-16 09:20:13', 'John', 6.5800)

So this is the query that I came up with so far

SELECT
  max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)               AS login_date,
  CASE WHEN max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) < max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)
    THEN max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END)
  ELSE max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) END           AS logout_date,
  A.full_name,
  CASE WHEN timestamp(max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)) < timestamp(max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)) OR max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) IS NULL
    THEN 0
  ELSE
    (timestamp(max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END)) - timestamp(max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END))) / 100 END AS session_time
FROM (
       SELECT
         timestamp(created_date) AS action_date,
         name as full_name,
         status as action
       FROM `training_ground`.session
       WHERE status = 'LOGIN' OR status = 'LOGOUT'
       GROUP BY action, cast(action_date AS DATE), name
       ORDER BY action_date DESC) AS A
GROUP BY A.full_name
ORDER BY A.action_date DESC;

I have no idea how to differentiate first login-logout to the second login-logout session, with this query, I am only getting

(1, '2019-07-16 07:06:55', '2019-07-16 07:29:13', 'Bob', 22.5800)
(2, '2019-07-16 08:05:55', '2019-07-16 08:15:13', 'Tom', 9.5800)
(3, '2019-07-16 09:13:55', '2019-07-16 09:20:13', 'John', 6.5800)

Is there a way to group login-logout as a set, so that I can differentiate every single pair of login-logout sequentially?

forpas · Accepted Answer

You can do it with a left join and then subtract the 2 dates:

select 
  t.id, 
  t.action_date login_date, tt.action_date logout_date,
  t.user_id,
  (tt.action_date - t.action_date) / 100 session_time
from (
  select * from session where status = 'LOGIN'  
) t left join (
  select * from session where status = 'LOGOUT'    
) tt on tt.user_id = t.user_id and 
  tt.action_date = (
    select min(action_date) from session
    where status = 'LOGOUT' and user_id = t.user_id and action_date > t.action_date
  )

See the demo.
Results:

| id  | login_date          | user_id | logout_date         | session_time |
| --- | ------------------- | ------- | ------------------- | ------------ |
| 1   | 2019-07-16 07:06:55 | Bob     | 2019-07-16 07:29:13 | 22.58        |
| 3   | 2019-07-16 07:30:31 | Bob     | 2019-07-16 07:49:50 | 19.19        |
| 5   | 2019-07-16 08:05:55 | Tom     | 2019-07-16 08:15:13 | 9.58         |
| 7   | 2019-07-16 09:13:55 | John    | 2019-07-16 09:20:13 | 6.58         |

Query group by pair of column result

Answers (2)

Related Questions