Consulta SQL do BigQuery e do Google Analytics - pergunta expandida

Question

Estou tentando expandir minha pergunta respondida aqui . então, dados os dados:

session_id  hit_timestamp   url event_category
1           11:12:23        url134      event1
1           11:14:23        url2234     event2
1           11:16:23        url_target  null
2           03:12:11        url2344     event1
2           03:14:11        url43245    event2
3           09:10:11        url5533     event2
3           09:09:11        url_target  null
4           08:08:08        url64356    event2
4           08:09:08        url56456    event2
4           08:10:08        url_target  null

E o resultado atual conforme abaixo:

session_id  event1  event2  target
1           1       1       1
2           0       0       0
3           0       0       0
4           0       2       1

Eu gostaria de expandir o resultado dado para refletir sobre os casos em que a meta é igual a zero. Eu seria capaz de anotar também esses casos com o número de contagem de eventos, independentemente das datas de verificação?

Portanto, o novo resultado pretendido seria o seguinte:

session_id  event1  event2  target
1           1       1       1
2           1       1       0
3           0       0       0
4           0       2       1

Estou particularmente interessado em session_id = 2, onde há vários eventos acontecendo, sem url_target sendo visitado. Finalmente, session_id = 3 é outro caso em que não tenho certeza de como lidar com isso! Como tem um evento (event2), mas foi feito depois de visitar o url_target. Talvez eu deva denotá-lo como alvo = 2, como sendo um caso especial. Mas, se isso for difícil com SQL, então eu descartaria do resultado e manteria como zeros, como a tabela de resultados pretendida acima.

Muito obrigado antecipadamente por qualquer contribuição.

Answer 1

Pelo que você descreve, você quer lógica condicional. Isso deve funcionar:

select session_id,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) and category = 'event1') as event1,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) > hit_timestamp and category = 'event2') as event2,
       countif(url like '%target') as target
from (select t.*,
             min(case when url like '%target' then hit_timestamp end) over (partition by session_id) as target_hit_timestamp
      from t
     ) t
group by session_id

O target_hit_timestampé NULLse não houver URL de destino.