How to get the difference between the first value and last value of two different column in Google B-CodePudding

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

CodePudding user response：

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