SQL to find sum of total days in a window for a series of changes

Following is the table:

start_date	recorded_date	id
2021-11-10	2021-11-01	1a
2021-11-08	2021-11-02	1a
2021-11-11	2021-11-03	1a
2021-11-10	2021-11-04	1a
2021-11-10	2021-11-05	1a

I need a query to find the total day changes in aggregate for a given id. In this case, it changed from 10th Nov to 8th Nov so 2 days, then again from 8th to 11th Nov so 3 days and again from 11th to 10th for a day, and finally from 10th to 10th, that is 0 days.

In total there is a change of 2+3+1+0 = 6 days for the id – ‘1a’.

Basically for each change there is a recorded_date, so we arrange that in ascending order and then calculate the aggregate change of days grouped by id. The final result should be like:

id	Agg_Change
1a	6

Is there a way to do this using SQL. I am using vertica database.

Thanks.

Answer

I was thinking lag function will provide me the answer, but it kept giving me wrong answer because I had the wrong logic in one place. I have the answer I need:

with cte as(
select id, start_date, recorded_date,
row_number() over(partition by id order by recorded_date asc) as idrank,
lag(start_date,1) over(partition by id order by recorded_date asc) as prev
from table_temp
)
select id, sum(abs(date(start_date) - date(prev))) as Agg_Change
from cte
group by 1

If someone has a better solution please let me know.