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.