I have a table with a list of vehicles and timestamps of adding and deleting them from the database. A single vehicle can be added by more than one user, so there can be more than one row when a specific license plate is 'active' on some point (added to the database but not deleted). Periods can overlap.

How to check if there was a gap for a specific license plate in the entire period I'am checking? There are multiple rows with start_date and and date (or with no end date if it wasn't deleted), I need to find out if there is any gap between first start_date and last end_date for a specific car. I need to group vehicles into 3 groups: Active (never completely deleted from the database), Deleted and Resurrected (deleted but re-entered - for this group I need to determine the gap). I tried to create tables with one row per a car based on a row number

ROW_NUMBER() OVER(PARTITION BY license_plate ORDER BY create_timestamp, delete_timestamp) AS rank

and after having multiple tables compare entries row by row:

CASE WHEN r1.delete_timestamp is null THEN 'Active'
     WHEN r1.delete_timestamp < r2.create_timestamp AND r2.delete_timestamp is null THEN 'Active'
     WHEN r1.delete_timestamp > r2.create_timestamp AND r2.delete_timestamp is null THEN 'Resurrected'
     WHEN r1.delete_timestamp > r2.create_timestamp AND r2.delete_timestamp is not null 
          AND r3.create_timestamp is null THEN 'Deleted'
     end so on for next rows :)
     END AS Vehicle_status

But it doesn't make sense as there are currently vehicles with 20 rows (entries) and can be more in the future.

Hope someone has any idea how to solve it :)