SQL: Need help to group by

this is my table:

I like to get this result:

If i use:

SELECT City, MIN(Start) as STA, MAX(Stop) AS STO FROM living GROUP BY City

i get:

>Solution :

WITH
  islands AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (                  ORDER BY start) AS seq,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY start) AS city_seq
  FROM
    living
)
SELECT
  city,
  MIN(start),
  MAX(stop)
FROM
  islands
GROUP BY
  city,
  seq - city_seq
ORDER BY
  MIN(start)

ROW_NUMBER() OVER (ORDER BY start) just creates a sequence of integers starting from 1, having ordered the data by start.

Adding PARTITION BY city does the same thing, but creates a separate sequence for each city.

The trick is then to use seq - city_seq. That tells you how many rows there have been so far that are NOT the current city.

Then, the combination of city and rows so far for other cities (seq - city_seq) is a unique identifier for each "island" and can be used in a normal GROUP BY

Edit:

The above assumes there are no gaps or overlaps in the data, and so ordering by start is sufficient.

If there are gaps or overlaps a slightly longer approach is more customisable…

• whenever the previous row isn’t ‘the same city, but 1 day later’, record a 1 for the row to indicate a "new island".
• cumulativel sum those values over time, creating unique identifiers for each "island"
• GROUP BY as normal

WITH
  islands AS
(
  SELECT
    *,
    CASE
      WHEN city  = LAG(city) OVER (ORDER BY start)
       AND start = LAG(stop) OVER (ORDER BY start) + INTERVAL '1 DAY'
      THEN 0
      ELSE 1
    END
      AS island_marker
  FROM
    living
),
  island_ids AS
(
  SELECT
    *,
    SUM(island_marker) OVER (ORDER BY start) AS island_id
  FROM
    islands
)
SELECT
  MIN(city),
  MIN(start),
  MAX(stop)
FROM
  island_ids
GROUP BY
  island_id
ORDER BY
  island_id