In bigquery, I have a customer table with information about how much he spends X amount of money between a start date and end date like this:
id
start_date
end_date
amount
1
2022-01-01
2022-01-10
100
1
2022-01-10
2022-01-15
30
1
2022-02-10
2022-02-18
10
1
2022-02-18
2022-02-20
30
1
2022-02-20
2022-02-25
50
1
2022-02-18
2022-03-20
5000
2
2022-01-12
2022-01-15
30
2
2022-01-15
2022-01-27
30
And I would like to have this:
id
start_date
end_date
amount
1
2022-01-01
2022-01-15
130
1
2022-02-10
2022-02-25
90
1
2022-02-18
2022-03-20
5000
2
2022-01-12
2022-01-27
60
The catch is that there can be multiple contiguous rows for the same id, and if there is a merge we want to merge the row with the smallest time interval possible, in the example the row with id=1,start_date=2022-02-18,end_date=2022-03-20 is not merged.
