add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t (
Name VARCHAR(1),
order_no INTEGER,
category VARCHAR(8)
);

INSERT INTO t
(Name, order_no, category)
VALUES
('A', '1', 'Good'),
('A', '2', 'Good'),
('A', '3', 'MODERATE'),
('A', '4', 'Good'),
('A', '5', 'MODERATE'),
('A', '6', 'Bad'),
('A', '7', 'Bad'),
('B', '1', 'Good'),
('B', '2', 'Good'),
('B', '3', 'Good'),
('B', '4', 'BAD');
11 rows affected
select name
,count(cnt) as category_transition_count
from
(select name
,case when category <> lag(category) over(partition by Name order by order_no) or lag(category) over(partition by Name order by order_no) is null then 1 end as cnt
from t) t
group by name
name category_transition_count
A 5
B 2