Query Help - Duration per event on Time series data with multiple events

Hello,

I am looking for a better approach to calculate a duration per event if I have a time series data with multiple events. Today we are doing it by pulling all the events per User and running the logic on Javascript but looking for alternatives if N1QL can do better here.

Dataset example:

  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T14:00:00Z",
    "event": "DRIVING"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T14:10:20Z",
    "event": "STOP_AT_LIGHT"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T14:11:10Z",
    "event": "DRIVING"
  },
{
    "user_id": "user:1234",
    "createdAt": "2020-06-01T14:45:00Z",
    "event": "STOPPED"
  },
  {
    "user_id": "user:4567",
    "createdAt": "2020-06-01T15:08:00Z",
    "event": "DRIVING"
  },
  {
    "user_id": "user:4567",
    "createdAt": "2020-06-01T15:12:00Z",
    "event": "STOP_AT_LIGHT"
  },
  {
    "user_id": "user:4567",
    "createdAt": "2020-06-01T15:12:45Z",
    "event": "DRIVING"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T15:05:00Z",
    "event": "DRIVING"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T15:10:00Z",
    "event": "STOP_AT_LIGHT"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T15:11:00Z",
    "event": "DRIVING"
  },
  {
    "user_id": "user:1234",
    "createdAt": "2020-06-01T15:35:00Z",
    "event": "STOPPED"
  }
]

The goal is to figure out the duration between each event say how long the Driver waited at the light.

Use 6.5 EE windows functions https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/windowfun.html

CREATE INDEX ix1 ON bucket(user_id,createdAt,event);

SELECT b1.user_id, b1.event, SUM(b1.endedAt-b1.createdAt) AS total 
FROM (SELECT b.user_id, b.event, b.createdAt,
      MAX(b.createdAt) OVER (ORDER BY b.createdAt ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS endedAt
      FROM bucket AS b
      WHERE b.user_id = "user:1234" AND b.createdAt BETWEEN  "2020-06-01T00:00:01Z" AND  "2020-06-01T23:59:59Z") AS b1
GROUP BY b1.user_id, b1.event;

OR

SELECT b.user_id, b.event, SUM(b.endedAt-b.createdAt) AS total
FROM (SELECT l.user_id, l.event, l.createdAt, MIN(r.createdAt) AS endedAt
      FROM bucket AS l
      LEFT JOIN bucket AS r ON l.user_id = r.user_id AND r.createdAt > l.createdAt
      WHERE l.user_id = "user:1234"
            AND l.createdAt BETWEEN  "2020-06-01T00:00:01Z" AND  "2020-06-01T23:59:59Z"
      GROUP BY l.user_id, l.event, l.createdAt ) AS b
GROUP BY b.user_id, b.event;

Thank you @vsr1. You’re always a quick responder here.

So I did a research before coming to forum and have gone through some blogs about windows function written by CB. This blog https://blog.couchbase.com/on-par-with-window-functions-in-n1ql/ has an example which shows with and without windows function but unfortunately both of them are still supported only in CB 6.5. (Even though it says without windows on 6.0, “with” clause is introduced in 6.5)

We are running 6.0 which is a latest migration for us from 4.5 and we don’t have a near plan for 6.5 yet.