find date gap in postgres

Question

i have a table with userIds and some dates like this : -

    | userId |       dates        |
    | 1      | 2021-06-20 00:00:00|
    | 1      | 2021-06-24 00:00:00|
    | 2      | 2021-06-25 00:00:00|
    | 2      | 2021-06-28 00:00:00|
    | 2      | 2021-06-30 00:00:00|
    | 3      | 2021-06-22 00:00:00|
    | 3      | 2021-06-24 00:00:00|
    | 3      | 2021-06-27 00:00:00|

I want to find first date for every user with userId that doesn't exist:-

expected output: -

    | userId |       dates        |
    | 1      | 2021-06-21 00:00:00|
    | 2      | 2021-06-26 00:00:00|
    | 3      | 2021-06-23 00:00:00|

I'm using postgres, can someone help as the data is pretty large, 4m+.

Gordon Linoff · Accepted Answer · 2021-06-20 13:48:58Z

2

I think simplest method is lead() and aggregation:

select userid,
       min(date) + interval '1 day'
from (select t.*,
             lead(date) over (partition by userid order by date) as next_date
      from t
     ) t
where next_date is null or next_date <> date + interval '1 day'
group by userid;

Or using distinct on:

select distinct on (userid) userid, date + interval '1 day'
from (select t.*,
             lead(date) over (partition by userid order by date) as next_date
      from t
     ) t
where next_date is null or next_date <> date + interval '1 day'
order by userid, date;

You can also write the where clause as:

where next_date is distinct from date + interval '1 day'

Here is a db<>fiddle.

answered Jun 20, 2021 at 13:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lovely · Accepted Answer · 2021-06-20 13:51:57Z

0

select DISTINCT ON (userId) userId, dates from table_name order by dates

answered Jun 20, 2021 at 13:51

Lovely

3163 silver badges17 bronze badges

1 Comment

Gordon Linoff Over a year ago

. . This only returns dates in the table. The OP is asking for dates that are not in the table.

Collectives™ on Stack Overflow

find date gap in postgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related