SQL Interview - Seats Availability

 Given following set of integers, write an SQL statement to determine the expected outputs:

Source :

Expected Output:

Source:

Output:

Scripts:

WITH NUMBER_TRICK AS (

SELECT 0 NUM FROM DUAL

UNION ALL

SELECT 7 NUM FROM DUAL

UNION ALL

SELECT 18 NUM FROM DUAL

UNION ALL

SELECT 19 NUM FROM DUAL

UNION ALL

SELECT 24 NUM FROM DUAL

UNION ALL

SELECT 27 NUM FROM DUAL

UNION ALL

SELECT 31 NUM FROM DUAL

UNION ALL

SELECT 35 NUM FROM DUAL

UNION ALL

SELECT 36 NUM FROM DUAL

UNION ALL

SELECT 37 NUM FROM DUAL

UNION ALL

SELECT 38 NUM FROM DUAL

UNION ALL

SELECT 41 NUM FROM DUAL

UNION ALL

SELECT 47 NUM FROM DUAL

UNION ALL

SELECT 50 NUM FROM DUAL

) 

SELECT

    gap_start + 1 gap_start,

    gap_end - 1 gap_end

FROM

    (

        SELECT

            num gap_start,

            LEAD(num, 1) OVER(

                ORDER BY

                    num

            ) gap_end,

            LEAD(num, 1) OVER(

                ORDER BY

                    num

            ) - num gap

        FROM

            number_trick

    )

WHERE

    gap > 1;