how to add values of related rows to a column in SQL

Question

I have a table with contract information and I would like to add a calculated column that identifies when a contract is consecutive to the previous one of the same client. So when the end date of a contract matches the start date of the following one for the same client we consider that is consecutive.

The data looks like this:

And I would like it to look like this:

I tried doing an inner join of the contract table with itself and then I unioned it, but I don't think that is the most effective way of doing it.

Do you know of a better way of achieving this?

Thanks in advance.

Answer 1

The code below was tested on SQL Server, but may also work on Presto. SQL Server has bit values rather than booleans, so I've returned TRUE and FALSE as strings. Hopefully you'll be able to make any necessary modifications.

SELECT
  c.contract_id
  ,c.client
  ,c.start_date
  ,c.end_date
  ,CASE
       WHEN LAG(c.end_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.start_date
       THEN 'TRUE' ELSE 'FALSE'
   END as is_consecutive
  ,CASE
       WHEN LAG(c.end_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.start_date
       THEN LAG(c.contract_id) OVER (PARTITION BY c.client ORDER BY c.start_date)
   END as related_previous
  ,CASE
       WHEN LEAD(c.start_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.end_date
       THEN LEAD(c.contract_id) OVER (PARTITION BY c.client ORDER BY c.start_date)
   END as related_next
FROM
  contract c