Postgresql – Looping through array_agg

I have a table from which I need to calculate the number of times intent_level changes for each id.

Sample Table format :

id | start_time | intent_level
----+------------+--------------
 1  | 2          | status
 1  | 3          | status
 1  | 1          |
 1  | 4          | category
 2  | 5          | status
 2  | 8          |
 2  | 7          | status

I couldn’t figure out how to loop through array_agg and compare consecutive elements. Below I tried using array_agg, but then I don’t know how to loop through it and compare consecutive elements.

select 
id, 
array_agg (intent_level ORDER BY start_time)
FROM temp.chats
GROUP BY id;

which gives output :

id |          array_agg
----+-----------------------------
 1  | {"",status,status,category}
 2  | {status,status,""}

Desired output is :

id |          array_agg
----+-----------------------------
 1  | 2
 2  | 1

2 (since value changes from "" to status(1 to 2) and status to category(3 to 4))

1 (since value changes from status to ""(2 to 3))

CREATE AND INSERT QUERIES :

create table temp.chats (
    id varchar(5),
    start_time varchar(5),
    intent_level varchar(20)
);

insert into temp.chats values 
('1', '2', 'status'),
('1', '3', 'status'),
('1', '1', ''),
('1', '4', 'category'),
('2', '5', 'status'),
('2', '8', ''),
('2', '7', 'status');

Answer

Use lag() and aggregate:

select id, count(*)
from (select c.*,
             lag(intent_level) over (partition by id order by start_time) as prev_intent_level
      from temp.chats c
     ) c
where prev_intent_level is distinct from intent_level
group by id;

Here is a db<>fiddle.

Arrays seem quite unnecessary for this.

Advertisement

Answer