0

I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like so (although much larger):

+-----------+------------+
| member_id |  data_date |
+-----------+------------+
|         1 | 2018-04-10 |
|         5 | 2018-04-16 |
|         1 | 2018-04-11 |
|         2 | 2018-04-12 |
|         3 | 2018-04-13 |
|         4 | 2018-04-12 |
|         5 | 2018-04-15 |
|         3 | 2018-04-19 |
|         2 | 2018-04-17 |
|         1 | 2018-04-18 |
|         5 | 2018-04-10 |
|         2 | 2018-04-18 |
|         1 | 2018-04-08 |
|         2 | 2018-04-03 |
|         3 | 2018-04-02 |
|         4 | 2018-04-14 |
|         5 | 2018-04-15 |
|         3 | 2018-04-16 |
|         2 | 2018-04-19 |
|         1 | 2018-04-14 |
+-----------+------------+

(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.

There are up to 30 data_dates for each member_id in the month, I am having trouble figuring out how to find the largest gap without data being sent in the entire month for each member.

Here is an example of some test data:

create temp table tempdata  (
  member_id integer NOT NULL,
  data_date date
);

INSERT INTO tempdata(member_id, data_date) VALUES
   (1, '2017-04-01')
 , (1, '2017-04-02')
 , (1, '2017-04-03')
 , (1, '2017-04-04')
 , (1, '2017-04-05')
 , (1, '2017-04-06')
 , (1, '2017-04-07')
 , (1, '2017-04-08')
 , (1, '2017-04-09')
 , (1, '2017-04-10')
 , (1, '2017-04-11')
 , (1, '2017-04-12')
 , (1, '2017-04-13')
 , (1, '2017-04-14')
 , (1, '2017-04-15')
 , (1, '2017-04-16')
 , (1, '2017-04-17')
 , (1, '2017-04-18')
 , (1, '2017-04-19')
 , (1, '2017-04-20')
 , (1, '2017-04-21')
 , (1, '2017-04-22')
 , (1, '2017-04-23')
 , (1, '2017-04-24')
 , (1, '2017-04-25')
 , (1, '2017-04-26')
 , (1, '2017-04-27')
 , (1, '2017-04-28')
 , (1, '2017-04-29')
 , (1, '2017-04-30')
 , (2, '2017-04-09')
 , (2, '2017-04-10')
 , (2, '2017-04-11')
 , (2, '2017-04-12')
 , (3, '2017-04-01')
 , (3, '2017-04-02')
 , (3, '2017-04-03')
 , (3, '2017-04-04')
 , (3, '2017-04-05')
 , (3, '2017-04-06')
 , (3, '2017-04-07')
 , (3, '2017-04-08')
 , (3, '2017-04-09')
 , (3, '2017-04-10')
 , (3, '2017-04-11')
 , (3, '2017-04-12')
 , (3, '2017-04-13')
 , (3, '2017-04-14')
 , (3, '2017-04-15')
 , (3, '2017-04-16')
 , (3, '2017-04-17')
 , (3, '2017-04-18')
 , (3, '2017-04-19')
 , (3, '2017-04-20')
 , (3, '2017-04-21')
 , (3, '2017-04-22')
 , (3, '2017-04-23')
 , (3, '2017-04-24')
 , (3, '2017-04-25')
 , (3, '2017-04-26')
 , (3, '2017-04-27')
 , (3, '2017-04-28')
 , (3, '2017-04-29')
 , (3, '2017-04-30')
 , (4, '2017-04-01')
 , (4, '2017-04-02')
 , (4, '2017-04-03')
 , (4, '2017-04-04')
 , (4, '2017-04-05')
 , (4, '2017-04-06')
 , (4, '2017-04-07')
 , (4, '2017-04-08')
 , (4, '2017-04-09')
 , (4, '2017-04-10')
 , (4, '2017-04-11')
 , (4, '2017-04-12')
 , (4, '2017-04-13')
 , (4, '2017-04-14')
 , (4, '2017-04-15')
 , (4, '2017-04-16')
 , (4, '2017-04-17')
 , (4, '2017-04-18')
 , (4, '2017-04-19')
 , (4, '2017-04-20')
 , (4, '2017-04-21')
 , (4, '2017-04-22')
 , (5, '2017-04-01')
 , (5, '2017-04-02')
 , (5, '2017-04-03')
 , (5, '2017-04-04')
 , (5, '2017-04-05')
 , (5, '2017-04-06')
 , (5, '2017-04-07')
 , (5, '2017-04-08')
 , (5, '2017-04-09')
 , (5, '2017-04-10')
 , (5, '2017-04-11')
 , (5, '2017-04-12')
 , (5, '2017-04-13')
 , (5, '2017-04-14')
 , (5, '2017-04-15')
 , (5, '2017-04-16')
 , (5, '2017-04-17')
 , (5, '2017-04-18')
 , (5, '2017-04-22')
 , (5, '2017-04-23')
 , (5, '2017-04-24')
 , (5, '2017-04-25')
 , (5, '2017-04-26')
 , (5, '2017-04-27')
 , (5, '2017-04-29')
 , (5, '2017-04-30')
 , (6, '2017-04-01')
 , (6, '2017-04-02')
 , (6, '2017-04-03')
 , (6, '2017-04-04')
 , (6, '2017-04-05')
 , (6, '2017-04-06')
 , (6, '2017-04-07')
 , (6, '2017-04-08')
 , (6, '2017-04-09')
 , (6, '2017-04-10')
 , (7, '2017-04-01')
 , (7, '2017-04-04')
 , (7, '2017-04-05')
 , (7, '2017-04-06')
 , (7, '2017-04-07')
 , (7, '2017-04-08')
 , (7, '2017-04-09')
 , (7, '2017-04-11')
 , (7, '2017-04-12')
 , (7, '2017-04-13')
 , (7, '2017-04-14')
 , (7, '2017-04-15')
 , (7, '2017-04-16')
 , (7, '2017-04-17')
 , (7, '2017-04-18')
 , (7, '2017-04-19')
 , (7, '2017-04-21')
 , (7, '2017-04-22')
 , (7, '2017-04-26')
 , (7, '2017-04-27')
 , (7, '2017-04-28')
 , (7, '2017-04-30')
 , (8, '2017-04-02')
 , (8, '2017-04-03')
 , (8, '2017-04-04')
 , (8, '2017-04-05')
 ;    
  • Please edit your post and add CREATE TABLE and INSERT statements. – Lennart Jun 25 '18 at 22:07
  • What's expected result?. – Sam Jun 26 '18 at 1:22
  • I have no control over the version of postgres that we use, I have brought this up but there is nothing I can do. – DataGwynn Jun 26 '18 at 15:35
  • Given that sample, yes that would be the expected results. Do you have a query you used to find that? – DataGwynn Jun 26 '18 at 20:55
  • Ya, i need consecutive, but thank you. – DataGwynn Jun 26 '18 at 23:43
0

Updating with the answer that I figured out for anyone interested:

drop table if exists tempdata;
create temp table tempdata as
select distinct member_id
from (
  select member_id
    , max(data_date) over (partition by member_id order by data_date) start_range
    , lead(data_date) over (partition by member_id order by data_date) end_range
  from tempmembers
) as c
where c.end_range-c.start_range > interval'9 days'
    ;

I was able to use this and then remove the ids that i found from the initial table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.