Get count of series/groups based on 14 days of interval

• I have a table containing range table like this.

``````+---------+---------+
| user_id | view_dt |
+---------+---------+
| A       |     1/1 |
+---------+---------+
| A       |    1/10 |
+---------+---------+
| A       |    1/14 |
+---------+---------+
| A       |    1/22 |
+---------+---------+
| A       |    1/23 |
+---------+---------+
| A       |    1/30 |
+---------+---------+
``````

I want this data to be grouped based on a 14 day interval. That is, the groups would be:

GROUP 1: 1/1, 1/10, 1/14

GROUP 2： 1/22, 1/23, 1/30

Basically I want to count the orders by interval. So Output should be:

``````user_id: A          count of series = 2
``````

Note that I the 1/30 date should belong to GROUP 2 since 1/30 should be compared to the first date of GROUP 2 (1/22) instead of (1/1).

I think it can be achieved by creating a recursive query because my grouping condition is 14 days from the first date of the group. Just curious if there's an alternative solution to the recursive CTE method?

• ``````WITH RECURSIVE
cte1 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) rn,
COUNT(*) OVER (PARTITION BY id ORDER BY dt
RANGE BETWEEN CURRENT ROW AND INTERVAL '15 day' FOLLOWING) cnt
FROM test
ORDER BY 1,2
),
cte2 AS (
SELECT *
FROM cte1
WHERE rn = 1
UNION ALL
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.id = cte2.id
AND cte1.rn = cte2.rn + cte2.cnt
)
SELECT id, COUNT(*)
FROM cte2
GROUP BY id;
``````

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=874ecc698183349114594e6f01ece3fd

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2