Postgres 16

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

CREATE TYPE status AS ENUM ('Active', 'Failed');
DROP TABLE IF EXISTS Device_Status;
CREATE TABLE Device_Status
(
  ID integer,
  Status status,
  temptime text
);
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.528');
INSERT INTO Device_Status VALUES (2,'Active','2023-01-13 10.00.01.529');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.530');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.531');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.532');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.533');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.534');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.535');
INSERT INTO Device_Status VALUES (4,'Failed','2023-01-13 10.00.01.536');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.537');
insert into device_status values (1,'Failed','2023-01-13 10:00:01.538'), 
                                 (1,'Failed','2023-01-13 10:00:01.539'), 
                                 (1,'Failed','2023-01-13 10:00:01.540'), 
                                 (1,'Failed','2023-01-13 10:00:01.541'), 
                                 (1,'Failed','2023-01-13 10:00:01.542'), 
                                 (1,'Failed','2023-01-13 10:00:01.543'), 
                                 (1,'Failed','2023-01-13 10:00:01.544'),
                                 (1,'Failed','2023-01-13 10:00:01.545'), 
                                 (1,'Failed','2023-01-13 10:00:01.546'),
                                 (1,'Failed','2023-01-13 10:00:01.547');
ALTER TABLE Device_Status ADD timestamp TIMESTAMP;
UPDATE Device_Status set timestamp = 
      to_timestamp(temptime,'YYYY-MM-DD HH:MI:SS.MS');
ALTER TABLE Device_Status DROP COLUMN temptime;
WITH Device_Status_With_Change_Column AS
(
  WITH flag AS
  (
    WITH lag AS
    (
      SELECT *, 
             LAG(status) OVER (PARTITION BY Id ORDER BY TimeStamp) Last_Status,
             LAG(Id,1,0) OVER (PARTITION BY Id ORDER BY TimeStamp) Last_Id
      FROM Device_Status
    )
    SELECT *, 
           CASE
           WHEN lag.last_status IS NULL THEN 0
           WHEN lag.last_status IS DISTINCT FROM lag.status 
            AND lag.last_id = lag.id  
            THEN 1
           ELSE 0
         END Change_Flag
     FROM lag
  )
  SELECT *, 
         SUM(change_flag) OVER (PARTITION BY Id ORDER BY TimeStamp) Change
  FROM flag
)
SELECT * 
FROM Device_Status_With_Change_Column

CREATE TYPE

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 10

ALTER TABLE

UPDATE 20

ALTER TABLE

id	status	timestamp	last_status	last_id	change_flag	change
1	Active	2023-01-13 10:00:01.528	null	0	0	0
1	Failed	2023-01-13 10:00:01.53	Active	1	1	1
1	Failed	2023-01-13 10:00:01.532	Failed	1	0	1
1	Active	2023-01-13 10:00:01.533	Failed	1	1	2
1	Failed	2023-01-13 10:00:01.535	Active	1	1	3
1	Active	2023-01-13 10:00:01.537	Failed	1	1	4
1	Failed	2023-01-13 10:00:01.538	Active	1	1	5
1	Failed	2023-01-13 10:00:01.539	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.54	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.541	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.542	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.543	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.544	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.545	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.546	Failed	1	0	5
1	Failed	2023-01-13 10:00:01.547	Failed	1	0	5
2	Active	2023-01-13 10:00:01.529	null	0	0	0
3	Active	2023-01-13 10:00:01.531	null	0	0	0
3	Active	2023-01-13 10:00:01.534	Active	3	0	0
4	Failed	2023-01-13 10:00:01.536	null	0	0	0

SELECT 20