'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