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;
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.