Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE view_TABLE (date_column, user_1,ip, in_ip,out_ip,session_1,activity) AS
SELECT 	DATE '2023-02-09' + INTERVAL '15:06' HOUR TO MINUTE, 'X', 123, 123,  NULL, 1, 'IN'  FROM DUAL UNION ALL
SELECT 	DATE '2023-02-09' + INTERVAL '15:08' HOUR TO MINUTE, 'X', 123, NULL, 123,  1, 'OUT' FROM DUAL UNION ALL
SELECT 	DATE '2023-02-08' + INTERVAL '16:08' HOUR TO MINUTE, 'Y', 143, NULL, 143,  2, 'OUT' FROM DUAL UNION ALL
SELECT 	DATE '2023-02-08' + INTERVAL '16:04' HOUR TO MINUTE, 'Y', 153, 153,  NULL, 2, 'IN'  FROM DUAL UNION ALL
SELECT 	DATE '2023-02-08' + INTERVAL '12:45' HOUR TO MINUTE, 'X', 163, 163,  NULL, 3, 'IN'  FROM DUAL UNION ALL
SELECT 	DATE '2023-02-08' + INTERVAL '12:48' HOUR TO MINUTE, 'X', 163, NULL, 163,  3, 'OUT' FROM DUAL;
6 rows affected

select
  USER_1,
  IP,
  max(case when activity = 'IN' then DATE_COLUMN end) IN_1,
  max(case when activity = 'OUT' then DATE_COLUMN end) OUT_1
from view_table
group by USER_1, IP, SESSION_1


USER_1
IP
IN_1
OUT_1


X
123
2023-02-09 15:06:00
2023-02-09 15:08:00

Y
143
null
2023-02-08 16:08:00

Y
153
2023-02-08 16:04:00
null

X
163
2023-02-08 12:45:00
2023-02-08 12:48:00


SELECT user_1, in_ip, in_time, out_ip, out_time
FROM   (
  SELECT v.*,
         TRUNC(date_column) AS day
  FROM   view_table v
)
MATCH_RECOGNIZE(
  PARTITION BY user_1, day
  ORDER     BY date_column
  MEASURES
    user_in.ip           AS in_ip,
    user_in.date_column  AS in_time,
    user_out.ip          AS out_ip,
    user_out.date_column AS out_time
  PATTERN ( user_in user_out? | user_out user_in? )
  DEFINE
    user_in  AS activity = 'IN',
    user_out AS activity = 'OUT'
)


USER_1
IN_IP
IN_TIME
OUT_IP
OUT_TIME


X
163
2023-02-08 12:45:00
163
2023-02-08 12:48:00

X
123
2023-02-09 15:06:00
123
2023-02-09 15:08:00

Y
153
2023-02-08 16:04:00
143
2023-02-08 16:08:00

USER_1	IP	IN_1	OUT_1
X	123	2023-02-09 15:06:00	2023-02-09 15:08:00
Y	143	null	2023-02-08 16:08:00
Y	153	2023-02-08 16:04:00	null
X	163	2023-02-08 12:45:00	2023-02-08 12:48:00