'Calculate length of sequence - SQL
I have a table consists person id (id) and a flight date (date).
Each person may have few flights in different dates. If 2 flights are a week or less apart, they are considered the same trip.
For example, for person id #2: 2022-05-20 -> 2022-05-23 -> 2022-05-29 is a trip with 3 flights.
I would like to calculate, for each person, the length of their trips (3 for the example above).
id | flight_date
2 | 2022-05-20
2 | 2022-05-23
2 | 2022-05-29
1 | 2022-04-01
1 | 2022-05-03
3 | 2022-04-15
3 | 2022-04-22
The output for the example would be something like:
id | trip_length
2 | 3
1 | 1 (beacuse his 2 trips are not a week apart)
3 | 2
I have tried to use group by for this, but I can't calculate it with the time constraint (a week between each flight).
Would appreciate any help.
Thank you very much!
SQL dialect is not much important :)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
