A análise web conta sessões (visitas), mas os dados são um fluxo de eventos. A regra comum: uma nova sessão começa com o primeiro evento de um visitante, ou depois de mais de 30 minutos sem nenhum.
Três passos, cada um com uma função de janela:
- Marque as novas sessões:
LAG(event_time) por visitante, e is_new = 1 quando não há evento anterior ou o intervalo passa de 30 minutos.
- Numere-as: um
SUM(is_new) acumulado por visitante, em ordem de tempo, dá 1, 1, 1, 2, 2, ...
- Resuma por (visitante, número da sessão): início, fim, eventos e se chegou a uma compra.
SUM(is_new) OVER (PARTITION BY visitor_id ORDER BY event_time, event_id) AS session_no
Adicione um critério de desempate (event_id) à ordenação, porque dois eventos podem ter o mesmo timestamp. A regra dos 30 minutos é uma convenção; as ferramentas variam, então informe a regra que você usou junto aos números.
Minutos desde o evento anterior do mesmo visitante.