Window function and time difference in Big Query

Question

I have a big query table defined as:

+----+----------------------------+------------+
| id |            time            |   event    |
+----+----------------------------+------------+
|  1 | 2015-10-01 16:31:48.000000 | signup     |
|  1 | 2015-10-01 16:41:48.000000 | 1_purchase |
|  1 | 2015-10-01 16:51:48.000000 | 2_purchase |
|  2 | 2015-10-01 16:31:48.000000 | signup     |
|  2 | 2015-10-01 16:41:48.000000 | 1_purchase |
|  3 | 2015-10-01 16:31:48.000000 | signup     |
+----+----------------------------+------------+

I would like to calculate time differences within each id group (1,2,3), obtaining a result as:

+----+----------------------------+------------+-----------------+--+
| id |            time            |   event    | timedifference  |  |
+----+----------------------------+------------+-----------------+--+
|  1 | 2015-10-01 16:31:48.000000 | signup     | -               |  |
|  1 | 2015-10-01 16:41:48.000000 | 1_purchase | 00:10:00.000000 |  |
|  1 | 2015-10-01 16:61:48.000000 | 2_purchase | 00:20:00.000000 |  |
|  2 | 2015-10-01 16:31:48.000000 | signup     | -               |  |
|  2 | 2015-10-01 16:41:48.000000 | 1_purchase | 00:10:00.000000 |  |
|  3 | 2015-10-01 16:31:48.000000 | signup     | no_purchase     |  |
+----+----------------------------+------------+-----------------+--+

After some research, I guess I would need to use window function... But I couldn't figure out any solution. Any help is highly appreciated! Best, V.

Answer 1

select 
  id, time, event, 
  time(sec_to_timestamp((timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time))))) as timedifference,
  (timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time)))/60 as timefifference_in_min,

  right('0' + string(datediff(timestamp(time),timestamp(prev_time))),2) + ' ' +
  time(sec_to_timestamp((timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time))))) as timedifference_as_dd_hh_mm_ss

from (
  select 
    id, time, event,
    lag(time) over(partition by id order by time) as prev_time
  from (
  select f0_ as id, f1_ as time, f2_ as event from
    (select 1, '2015-10-01 16:31:48.000000', 'signup'),
    (select 1, '2015-10-01 16:41:48.000000', '1_purchase'),
    (select 1, '2015-10-01 16:51:48.000000', '2_purchase'),
    (select 2, '2015-10-01 16:31:48.000000', 'signup'),
    (select 2, '2015-10-01 16:41:48.000000', '1_purchase'),
    (select 3, '2015-10-01 16:31:48.000000', 'signup')
  )
)
order by id, time