Find members with consecutive entries I need to find all members in the database with data on 7 or more consecutive days. The table is set up as member_id and data_date. There are duplicate member_ids for each day that there is data.

I found some answers using MySQL that involved datediff or dateadd, but I am not sure how to do this in PostgreSQL 8.2.15. Below is an example of what the table looks like, but with many more rows.

+------------+------------+
| member_id  |  data_date |
+------------+------------+
|    0000001 | 2018-04-10 |
|    0000005 | 2018-04-16 |
|    0000001 | 2018-04-11 |
|    0000002 | 2018-04-12 |
|    0000003 | 2018-04-13 |
|    0000004 | 2018-04-12 |
|    0000005 | 2018-04-15 |
|    0000003 | 2018-04-19 |
|    0000002 | 2018-04-17 |
|    0000001 | 2018-04-18 |
|    0000005 | 2018-04-10 |
|    0000002 | 2018-04-18 |
|    0000001 | 2018-04-08 |
|    0000002 | 2018-04-03 |
|    0000003 | 2018-04-02 |
|    0000004 | 2018-04-14 |
|    0000005 | 2018-04-15 |
|    0000003 | 2018-04-16 |
|    0000002 | 2018-04-19 |
|    0000001 | 2018-04-14 |
+------------+------------+


(member_id, data_date) is defined UNIQUE.
This simple query should work in your outdated version:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
JOIN   tbl t3 USING (member_id)
JOIN   tbl t4 USING (member_id)
JOIN   tbl t5 USING (member_id)
JOIN   tbl t6 USING (member_id)
JOIN   tbl t7 USING (member_id)
WHERE  t2.data_date = t1.data_date + 1
AND    t3.data_date = t1.data_date + 2
AND    t4.data_date = t1.data_date + 3
AND    t5.data_date = t1.data_date + 4
AND    t6.data_date = t1.data_date + 5
AND    t7.data_date = t1.data_date + 6;


In Postgres, you can just add integer to a date to get the next day.

And probably fast, too - as long as you don't have much longer streaks of entries producing many dupes in the first step.

Or:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
WHERE  t2.data_date BETWEEN t1.data_date + 1
                        AND t1.data_date + 6
GROUP  BY t1.member_id, t1.date
HAVING count(*) = 6;


Update to a current version of Postgres at the earliest opportunity.