How to get event login, logout dates as column using SQL

Question

I have subset of data like this:

      Timestamp        user_id            activity
      ---------------------------------------------
      2020-04-02 08:50    1               login
      2020-04-03 07:39    1               log_out
      2020-04-04 07:40    2               login
      2020-04-10 00:22    2               logout
      2020-04-07 07:59    3               login
      2020-04-19 07:59    3               logout
      2020-04-15 08:50    1               login

I want to specify for each user the date of login and logout, if a user is still in the state login we can put the current date for date of logout.

My data is in bigquery table, I want to develop a SQL query, I didn't find a similar example.

The result must be:

         user_id       date_login     date_logout
         ------------------------------------------
            1          2020-04-02      2020-04-03
            2          2020-04-04      2020-04-10
            3          2020-04-07      2020-04-19
            1          2020-04-15      2022-01-25 (CURRENT DATE)

Any help please ?

Answer 1

Consider below brute-force approach

select user_id, 
  min(date) as date_login, 
  max(date) as date_logout
from (
  select min(date) date, user_id, activity, session
  from (
    select date(timestamp) date, user_id, activity, 
      countif(activity = 'login') over(partition by user_id order by timestamp) session
    from (
      select * from your_table union all 
      select distinct current_timestamp(), user_id, 'logout' from your_table
    )
  )
  group by user_id, activity, session
)
group by user_id, session
-- order by date_login     

if applied to sample data in your question - output is