Answer the question
In order to leave comments, you need to log in
Is it possible to simplify the SQL query?
Good afternoon.
There is such a table of user visits to pages:
happened_at
page
user_id
with t as(select user_id,page,happened_at,
happened_at - lag (happened_at) over (partition by user_id order by happened_at) is null or
happened_at - lag (happened_at) over (partition by user_id order by happened_at) > interval '1 hour' as is_session_start,
lead (happened_at) over (partition by user_id order by happened_at) - happened_at is null or
lead (happened_at) over (partition by user_id order by happened_at) - happened_at > interval '1 hour' as is_session_end
from test.vimbox_pages ),
tt as (select user_id,page,happened_at,is_session_start,is_session_end,
coalesce(lag (happened_at) over (partition by user_id order by happened_at), happened_at) as session_start
from t
where is_session_start=true or is_session_end=true)
select
user_id,
session_start,
happened_at as session_end
from tt
where is_session_end=true
order by user_id, session_start
Answer the question
In order to leave comments, you need to log in
it is possible if you add the session_id entry to the table initially
Can you explain the solution in more detail?
And if it's not difficult, then write to me in telegram @ilya8960
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question