By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE mytable (
id int,
created_at timestamp,
user_id int
);
INSERT INTO mytable VALUES
(1, '2019-10-14 09:26:53.813', 1),
(2, '2019-10-14 09:26:54.813', 1),
(3, '2019-10-14 09:46:53.813', 1),
(4, '2019-10-14 09:46:54.813', 2),
(5, '2019-10-14 09:46:55.813', 1),
(6, '2019-10-14 09:46:56.813', 1),
(7, '2019-10-14 09:46:58.813', 2),
(8, '2019-10-14 10:06:56.813', 1);
CREATE TABLE
INSERT 0 8
SELECT
*,
lag(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
FROM
mytable
| id | created_at | user_id | lag |
|---|---|---|---|
| 1 | 2019-10-14 09:26:53.813 | 1 | null |
| 2 | 2019-10-14 09:26:54.813 | 1 | 2019-10-14 09:26:53.813 |
| 3 | 2019-10-14 09:46:53.813 | 1 | 2019-10-14 09:26:54.813 |
| 5 | 2019-10-14 09:46:55.813 | 1 | 2019-10-14 09:46:53.813 |
| 6 | 2019-10-14 09:46:56.813 | 1 | 2019-10-14 09:46:55.813 |
| 8 | 2019-10-14 10:06:56.813 | 1 | 2019-10-14 09:46:56.813 |
| 4 | 2019-10-14 09:46:54.813 | 2 | null |
| 7 | 2019-10-14 09:46:58.813 | 2 | 2019-10-14 09:46:54.813 |
SELECT 8
SELECT
*,
created_at - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
FROM
mytable
| id | created_at | user_id | ?column? |
|---|---|---|---|
| 1 | 2019-10-14 09:26:53.813 | 1 | 00:00:00 |
| 2 | 2019-10-14 09:26:54.813 | 1 | 00:00:01 |
| 3 | 2019-10-14 09:46:53.813 | 1 | 00:19:59 |
| 5 | 2019-10-14 09:46:55.813 | 1 | 00:00:02 |
| 6 | 2019-10-14 09:46:56.813 | 1 | 00:00:01 |
| 8 | 2019-10-14 10:06:56.813 | 1 | 00:20:00 |
| 4 | 2019-10-14 09:46:54.813 | 2 | 00:00:00 |
| 7 | 2019-10-14 09:46:58.813 | 2 | 00:00:04 |
SELECT 8
SELECT
*,
created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes'
FROM
mytable
| id | created_at | user_id | ?column? |
|---|---|---|---|
| 1 | 2019-10-14 09:26:53.813 | 1 | f |
| 2 | 2019-10-14 09:26:54.813 | 1 | f |
| 3 | 2019-10-14 09:46:53.813 | 1 | t |
| 5 | 2019-10-14 09:46:55.813 | 1 | f |
| 6 | 2019-10-14 09:46:56.813 | 1 | f |
| 8 | 2019-10-14 10:06:56.813 | 1 | t |
| 4 | 2019-10-14 09:46:54.813 | 2 | f |
| 7 | 2019-10-14 09:46:58.813 | 2 | f |
SELECT 8
SELECT
*,
(created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes')::int AS group_id
FROM
mytable
| id | created_at | user_id | group_id |
|---|---|---|---|
| 1 | 2019-10-14 09:26:53.813 | 1 | 0 |
| 2 | 2019-10-14 09:26:54.813 | 1 | 0 |
| 3 | 2019-10-14 09:46:53.813 | 1 | 1 |
| 5 | 2019-10-14 09:46:55.813 | 1 | 0 |
| 6 | 2019-10-14 09:46:56.813 | 1 | 0 |
| 8 | 2019-10-14 10:06:56.813 | 1 | 1 |
| 4 | 2019-10-14 09:46:54.813 | 2 | 0 |
| 7 | 2019-10-14 09:46:58.813 | 2 | 0 |
SELECT 8
SELECT
*,
SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at)
FROM (
SELECT
*,
(created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes')::int AS group_id
FROM
mytable
)s
| id | created_at | user_id | group_id | sum |
|---|---|---|---|---|
| 1 | 2019-10-14 09:26:53.813 | 1 | 0 | 0 |
| 2 | 2019-10-14 09:26:54.813 | 1 | 0 | 0 |
| 3 | 2019-10-14 09:46:53.813 | 1 | 1 | 1 |
| 5 | 2019-10-14 09:46:55.813 | 1 | 0 | 1 |
| 6 | 2019-10-14 09:46:56.813 | 1 | 0 | 1 |
| 8 | 2019-10-14 10:06:56.813 | 1 | 1 | 2 |
| 4 | 2019-10-14 09:46:54.813 | 2 | 0 | 0 |
| 7 | 2019-10-14 09:46:58.813 | 2 | 0 | 0 |
SELECT 8
SELECT
user_id,
group_id,
first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC) AS last_value,
first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at) AS first_value
FROM (
SELECT
id, created_at, user_id,
SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
FROM (
SELECT
*,
(created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes')::int AS group_id
FROM
mytable
)s
)s
| user_id | group_id | last_value | first_value |
|---|---|---|---|
| 1 | 0 | 2019-10-14 09:26:54.813 | 2019-10-14 09:26:53.813 |
| 1 | 0 | 2019-10-14 09:26:54.813 | 2019-10-14 09:26:53.813 |
| 1 | 1 | 2019-10-14 09:46:56.813 | 2019-10-14 09:46:53.813 |
| 1 | 1 | 2019-10-14 09:46:56.813 | 2019-10-14 09:46:53.813 |
| 1 | 1 | 2019-10-14 09:46:56.813 | 2019-10-14 09:46:53.813 |
| 1 | 2 | 2019-10-14 10:06:56.813 | 2019-10-14 10:06:56.813 |
| 2 | 0 | 2019-10-14 09:46:58.813 | 2019-10-14 09:46:54.813 |
| 2 | 0 | 2019-10-14 09:46:58.813 | 2019-10-14 09:46:54.813 |
SELECT 8
SELECT DISTINCT
user_id,
group_id,
first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC)
- first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at)
FROM (
SELECT
id, created_at, user_id,
SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
FROM (
SELECT
*,
(created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes')::int AS group_id
FROM
mytable
)s
)s
| user_id | group_id | ?column? |
|---|---|---|
| 2 | 0 | 00:00:04 |
| 1 | 0 | 00:00:01 |
| 1 | 2 | 00:00:00 |
| 1 | 1 | 00:00:03 |
SELECT 4
SELECT
user_id,
AVG(diff)
FROM (
SELECT DISTINCT
user_id,
group_id,
first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC)
- first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at) as diff
FROM (
SELECT
id, created_at, user_id,
SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
FROM (
SELECT
*,
(created_at
- lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
> interval '10 minutes')::int AS group_id
FROM
mytable
)s
)s
)s
GROUP BY user_id
| user_id | avg |
|---|---|
| 2 | 00:00:04 |
| 1 | 00:00:01.333333 |
SELECT 2