Write a

[@doyouwanttoknow3366]

With CTE as (select hotel_id, room_number, lead (room_number) over(order by room_number), lead (room_number) over(order by room_number) - room_number as difference from Hotels where availability_status = available) select hotel_id, room_number from CTE where difference = 1

[@TheBigDataShow]

You are in right direction but there is a few improvement needed like applying Partition Byu on hotel_id and then applying lead() over(partition by hotel_id, order by room_number asc). Now there is one another catch here. For example: Let's consider following sample table. hotel_id, room_id, available status 1, 2, false 1, 3, true 1, 4, true 1, 5, true 2, 101, true 3, 1, true 3, 1, true So expected output should be hotel_id, room_id 1, 3 1, 4 1, 5 3, 1 3, 1 Now kindly check the solution that you have provided, You will able to debug your mistake

[@Momofrayyudoodle]

lead(room_number) over (partition by hotel_id order by room_number) : Need to partition by hotel id to avoid mixing up the room ids of different hotels

[@akskrish3924]

Need to filter out the room id which has single room ... to find that we need check with previous room id - current room id =1 or next available room id - current room id =1 under each hotel id. If any one of the condition satisfied then give it as a flag as consecutive_flag then fliter only flag=1 .. this will remove room id which are available as single room