# 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?

• Please always disclose your version of Postgres and a basic table definition - a `CREATE TABLE` statement showing data types and constraints. Is this for a single `user_id` or is the sample misleading? Commented Apr 7, 2022 at 0:05

``````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

• I like the clever trick to count rows within time range, and skip ahead in the recursive part. Should be `AND INTERVAL '14 day' FOLLOWING`, though. Commented Apr 7, 2022 at 0:19

This is one of the rare cases where a procedural solution might be faster because it can make do with a single scan over the table:

``````CREATE OR REPLACE FUNCTION island_count(_days int = 14)
RETURNS TABLE (user_id text, islands int)
LANGUAGE plpgsql AS
\$func\$
DECLARE
this_id text;  -- using user_id for "last_id" directly
this_dt date;
last_dt date;
BEGIN
FOR this_id, this_dt IN
SELECT t.user_id, t.view_dt
FROM   tbl t
ORDER  BY 1, 2
LOOP
IF this_id = user_id THEN
IF this_dt > last_dt + _days THEN
islands := islands + 1;
last_dt := this_dt;  -- restart frame
-- RAISE NOTICE 'New island: user: % date: %', this_id, this_dt;  -- to debug
END IF;
ELSE
IF user_id IS NULL THEN
-- nothing to return yet
ELSE
RETURN NEXT;
END IF;
user_id := this_id;
islands := 1;           -- restart count
last_dt := this_dt;     -- restart frame
END IF;
-- else do nothing
END LOOP;

IF FOUND THEN                 -- if any rows at all ...
RETURN NEXT;               -- ... terminate final loop
END IF;
END
\$func\$;
``````

Call:

``````SELECT * FROM island_count();
``````

db<>fiddle here

An index over `(user_id, t.view_dt)` will help performance a lot for big tables.

If you don't want to persist the function in the db, consider a "temporary function". See: