Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE positions (
    id int,
    person_id int,
    start_date date,
    end_date date,
    title text
);

INSERT INTO positions VALUES
(1, 1, '2001-12-01', '2002-01-31', 'admin'),
(2, 1, '2002-02-11', '2002-03-31', 'admin'),
(3, 1, '2002-02-15', '2002-05-31', 'sales'),
(4, 1, '2002-06-15', '2002-12-31', 'ops');

SELECT * FROM positions

CREATE TABLE



INSERT 0 4




id
person_id
start_date
end_date
title


1
1
2001-12-01
2002-01-31
admin

2
1
2002-02-11
2002-03-31
admin

3
1
2002-02-15
2002-05-31
sales

4
1
2002-06-15
2002-12-31
ops



SELECT 4


SELECT 
    *,
    lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
FROM
    positions;


id
person_id
start_date
end_date
title
lead


1
1
2001-12-01
2002-01-31
admin
2002-02-11

2
1
2002-02-11
2002-03-31
admin
2002-02-15

3
1
2002-02-15
2002-05-31
sales
2002-06-15

4
1
2002-06-15
2002-12-31
ops
null



SELECT 4


SELECT
    *,
    lead - (end_date + 1)
FROM (
    SELECT 
        *,
        lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
    FROM
        positions
) s


id
person_id
start_date
end_date
title
lead
?column?


1
1
2001-12-01
2002-01-31
admin
2002-02-11
10

2
1
2002-02-11
2002-03-31
admin
2002-02-15
-45

3
1
2002-02-15
2002-05-31
sales
2002-06-15
14

4
1
2002-06-15
2002-12-31
ops
null
null



SELECT 4


SELECT
    *
FROM (
    SELECT 
        *,
        lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
    FROM
        positions
) s
WHERE lead - (end_date + 1) > 0


id
person_id
start_date
end_date
title
lead


1
1
2001-12-01
2002-01-31
admin
2002-02-11

3
1
2002-02-15
2002-05-31
sales
2002-06-15



SELECT 2


SELECT
    person_id,
    end_date + 1 AS start_date,
    lead - 1 AS end_date,
    id AS last_position_id,
    lead - (end_date + 1) AS gap_in_days
FROM (
    SELECT 
        *,
        lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
    FROM
        positions
) s
WHERE lead - (end_date + 1) > 0


person_id
start_date
end_date
last_position_id
gap_in_days


1
2002-02-01
2002-02-10
1
10

1
2002-06-01
2002-06-14
3
14



SELECT 2

id	person_id	start_date	end_date	title
1	1	2001-12-01	2002-01-31	admin
2	1	2002-02-11	2002-03-31	admin
3	1	2002-02-15	2002-05-31	sales
4	1	2002-06-15	2002-12-31	ops