Postgres 13

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

CREATE TABLE mytable
    ("id" varchar(3), "status" varchar(9), "update_date" date)
;
    
INSERT INTO mytable
    ("id", "status", "update_date")
VALUES
    ('A', 'PENDING', '2020-11-01'),
    ('A', 'PENDING', '2020-11-02'),
    ('A', 'CONFIRMED', '2020-11-03'),
    ('A', 'CONFIRMED', '2020-11-04'),
    ('A', 'CONFIRMED', '2020-11-05'),
    ('A', 'PENDING', '2020-11-06'),
    ('A', 'PAID', '2020-11-07'),
    ('B', 'CONFIRMED', '2020-11-02')
;

SELECT * FROM mytable
8 rows affected



id
status
update_date


A
PENDING
2020-11-01

A
PENDING
2020-11-02

A
CONFIRMED
2020-11-03

A
CONFIRMED
2020-11-04

A
CONFIRMED
2020-11-05

A
PENDING
2020-11-06

A
PAID
2020-11-07

B
CONFIRMED
2020-11-02


SELECT
    *,
    lead(status) OVER (PARTITION BY id ORDER BY update_date) AS next
FROM
    mytable


id
status
update_date
next


A
PENDING
2020-11-01
PENDING

A
PENDING
2020-11-02
CONFIRMED

A
CONFIRMED
2020-11-03
CONFIRMED

A
CONFIRMED
2020-11-04
CONFIRMED

A
CONFIRMED
2020-11-05
PENDING

A
PENDING
2020-11-06
PAID

A
PAID
2020-11-07
null

B
CONFIRMED
2020-11-02
null


SELECT
    *
FROM (
    SELECT
        *,
        lead(status) OVER (PARTITION BY id ORDER BY update_date) AS next
    FROM
        mytable
) s
WHERE status != next OR next is null


id
status
update_date
next


A
PENDING
2020-11-02
CONFIRMED

A
CONFIRMED
2020-11-05
PENDING

A
PENDING
2020-11-06
PAID

A
PAID
2020-11-07
null

B
CONFIRMED
2020-11-02
null


SELECT
    id, 
    status,
    row_number() OVER (PARTITION BY id)
FROM (
    SELECT
        *,
        lead(status) OVER (PARTITION BY id ORDER BY update_date) AS next
    FROM
        mytable
) s
WHERE status != next OR next is null


id
status
row_number


A
PENDING
1

A
CONFIRMED
2

A
PENDING
3

A
PAID
4

B
CONFIRMED
1

id	status	update_date
A	PENDING	2020-11-01
A	PENDING	2020-11-02
A	CONFIRMED	2020-11-03
A	CONFIRMED	2020-11-04
A	CONFIRMED	2020-11-05
A	PENDING	2020-11-06
A	PAID	2020-11-07
B	CONFIRMED	2020-11-02