Skip to content

3140. Consecutive Available Seats II

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
WITH
  FreeSeats AS (
    SELECT
      *,
      seat_id - ROW_NUMBER() OVER(ORDER BY seat_id) AS group_id
    FROM Cinema
    WHERE free = 1
  ),
  RankedFreeSeats AS (
    SELECT
      MIN(seat_id) AS first_seat_id,
      MAX(seat_id) AS last_seat_id,
      COUNT(*) AS consecutive_seats_len,
      RANK() OVER(ORDER BY COUNT(*) DESC) AS `rank`
    FROM FreeSeats
    GROUP BY group_id
  )
SELECT first_seat_id, last_seat_id, consecutive_seats_len
FROM RankedFreeSeats
WHERE `rank` = 1
ORDER BY 1;