How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

Question

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:

42, 1
43, 1
42, 2
43, 5
43, 6
42, 3

the output contains the client_id, and boundaries of the gaps, like this:

43, 2, 4

Note that there's no row with client_id=42 because there are no gaps in its data.

I've tried this for the core part of the query:

select 
    client_id, 
    order_no as start_order_no, 
    lead(order_no) over (order by client_id, order_no) as end_order_no

but that apparently doesn't do what I want, and I suspect it's because of client_id in the OVER part.

Answer 1

You should use a CTE or a subquery to get the lead order_no first.

select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   (select client_id, 
               order_no,
               lead(order_no) 
                   over (partition by client_id order by client_id, order_no) as no
from     tbl) ct
where  no is not null
and    no - order_no > 1;

with ct as
(
select   client_id, 
         order_no,
         lead(order_no) 
             over (partition by client_id order by client_id, order_no) as no
from     tbl
)
select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   ct
where  no is not null
and    no - order_no > 1;

client_id | c1 | c2
--------: | -: | -:
       43 |  2 |  4

db<>fiddle here