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')
 ;
Improve this question
6
  • Please edit your post and add CREATE TABLE and INSERT statements.
    – Lennart - Slava Ukraini
    Commented Jun 25, 2018 at 22:07
  • What's expected result?.
    – user153556
    Commented Jun 26, 2018 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
    Commented Jun 26, 2018 at 15:35
  • Given that sample, yes that would be the expected results. Do you have a query you used to find that?
    – DataGwynn
    Commented Jun 26, 2018 at 20:55
  • Ya, i need consecutive, but thank you.
    – DataGwynn
    Commented Jun 26, 2018 at 23:43

1 Answer 1

Reset to default
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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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