Self joining a table while maintaining the order of the rows

Question

So i have a table that logs users using certain apps. It has the fields below

id
time
app
user
server
type (can be IN or OUT)

So a user starts using a certain app and when they get the right to use the app a row is logged and the type column is set to 'OUT'

Similarly, when they stop using the app, another row gets logged and now the type column is set to 'IN'

So i have to build a session table which shows the time between an app being used by a certain user & server up until when the user stops using it.

So i made the following query doing a self join of the table

SELECT A.time as OutTime, B.time as InTime, A.app, 
A.username, A.requestserver
FROM logs A, logs B
WHERE A.app = B.app
AND A.username = B.username
AND A.requestserver = B.requestserver
AND A.type = 'OUT'
AND B.type = 'IN'
AND A.time < B.time

I am not sure if the logic of this 100% right. What if there's a case where the same user from the same server has two sessions for example:

OUT (1) -> 10 AM

IN (1) -> 1 PM

OUT (2) -> 3 PM

IN (2) -> 7PM

Now with my logic there's a chance i might just create a session from 10AM-7PM which is wrong. How would i go about this?

Answer 1

I would do this with window functions instead of a self-join. You can set the window to only rows before the current row, and also set a max session length for performance reasons if that's of interest.

Assuming your time field is a full timestamp, and not just time of day:

with sessionized as (

select
    *,
    last_value(
        case when type = 'IN' then time end
        ignore nulls
    ) over (
        partition by username, app, requestserver
        order by time asc
        rows between unbounded preceding and current row
    ) as session_start

from logs

)
select *
from sessionized 
where type = 'OUT'