Return to Question

(member_id,data_date) is defined UNIQUE. Each data_date represents one day that data was sent. There are duplicate member_ids for each day that there is data. I am running PostgreSQL 8.2.15.

(member_id,data_date) is defined UNIQUE. Each data_date represents one day that data was sent. There are duplicate data_dates for each member_id. I am running PostgreSQL 8.2.15. It is Greenplum.

This is the best I have been able to come up with so far, but it is not quite working as there are still some members with gaps 10 days or larger:

drop table if exists tempdates;
create temp table tempdates as
SELECT distinct member_id, d1.data_date as date1, (d2.data_date - d1.data_date) as datadays
FROM tempdata d1
join tempdata d2 using (member_id)
WHERE d1.data_date >= '06-01-17' and d1.data_date <= '06-30-17'
and d2.data_date between '06-10-17' and '06-20-17' 
;

drop table if exists temp1;
create temp table temp1 as
select distinct (member_id)
from tempdates
where datadays < 10
group by member_id
HAVING min(date1) <= '06-09-17' and max(date1) >= '06-21-17'
;