group by same key UUID across different event and subtract timestamp

Question

I have two or more event with same Id like a session but with different attribute values I should query the all event grouped by id and calc the difference between two time stamps.

example of events

event one:

{ type: 'start', Id: 1 ... }

event two:

{ type: 'end', Id: 1 ... }

Pseudo query:

select (end.timrstamp-start.timestamp)/1000 group by Id

Answer 1

I think the one possible solutions is

SELECT average(duration) FROM (
SELECT (
  (filter(latest(timestamp), where transitionName = 'STARTED') - 
  filter(latest(timestamp), where transitionName = 'CREATED'))
) AS 'duration'
FROM table 
FACET id AS uniqueId
SINCE last hour