sql_sessionization_or_record_sequence_age.sql

select 
  *,
  row_number() over (partition by site,zone,location,prod_code,pallet_sequence order by year,week) as pallet_age
from 
(
  select 
    *,
    sum(broken_sequence) over (partition by site,zone,location,prod_code order by yearweek) as pallet_sequence
  from 
  (  
    select 
      *,
      case when (lag(year*52+week) over (partition by site,zone,location,prod_code order by year,week)+1) = year*52+week and boh <= lag(boh) over(partition by site,zone,location,prod_code order by year,week) then 0 else 1 end as broken_sequence
    from tablexxx
  ) sub1
)sub2

https://www.dataiku.com/learn/guide/code/reshaping_data/sessionization.html

SELECT *
  , user_id || '_' || SUM(new_session) 
  OVER (PARTITION BY user_id ORDER BY mytimestamp) AS session_id
FROM (
  SELECT *
    , CASE
       WHEN EXTRACT(EPOCH FROM mytimestamp) 
          - LAG(EXTRACT(EPOCH FROM mytimestamp)) 
            OVER (PARTITION BY user_id ORDER BY mytimestamp) >= 30 * 60 
       THEN 1
       ELSE 0
      END as new_session
    FROM
      toy_data_psql
) s1