Now my question is little confusing but I would try to explain the best I can . I have two different events open and click now I want to calculate the time difference between when the email was open and when it did the last click.
this is my data
this is what I need
I have tried with the FIRST_VALUE AND LAST_VALUE function in google big query but my I am getting null values even though I have also tried with casting the values into datetime but still getting null values.
SELECT
X.*,
DATETIME_DIFF(CAST((FIRST_VALUE(open_time) OVER(PARTITION BY event_timestamp order by event_timestamp asc)) as datetime),
CAST((LAST_VALUE(click_time) OVER(PARTITION BY event_timestamp order by event_timestamp asc))as datetime),hour) as check
FROM
(select *,
CASE when event_type='open' then event_timestamp else null end as open_time,
CASE when event_type='click' then event_timestamp else null end as click_time
from check-db
where subject = 'check the summers out'
)X
>Solution :
Use below approach
select email_topic,
min(event_timestamp) event_timestamp,
min(open_time) open_time,
max(click_time) click_time,
max(timestamp(click_time)) - min(timestamp(open_time)) diff
from your_table
group by email_topic
if applied to sample data in your question – output is
