Consulta SQL de BigQuery y Google Analytics: pregunta ampliada

Question

Estoy tratando de ampliar mi pregunta respondida aquí . entonces, dados los datos:

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

Y el resultado actual de la siguiente manera:

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

Me gustaría ampliar el resultado dado para reflexionar sobre aquellos casos en los que el objetivo es igual a cero. ¿Podría también anotar esos casos con el número de recuento de eventos independientemente de las fechas de verificación?

Entonces, el nuevo resultado previsto sería el siguiente:

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

Estoy particularmente interesado en session_id = 2 donde hay una cantidad de eventos que suceden, sin que se visite url_target. Finalmente, session_id = 3 también es otro caso en el que no estoy seguro de cómo manejarlo. Como tiene un evento (event2), pero se realizó después de visitar url_target. Tal vez debería denotarlo como target = 2, como un caso especial. Pero, si esto es difícil con SQL, entonces lo descartaría del resultado y lo mantendría como ceros, como la tabla de resultados deseada arriba.

Muchas gracias de antemano por cualquier contribución.

Answer 1

Por lo que describe, quiere lógica condicional. Esto debería 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

El target_hit_timestampes NULLsi no hay una dirección URL de destino.