Forum

Forums
SQL
Interview Questions...
How to Find Consecu...
 
Share:
Notifications
Clear all

How to Find Consecutive Seat Numbers in a Booking Table Using SQL?

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 5:59 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

In SQL, retrieving consecutive seat numbers (or any consecutive records) can be achieved by using functions like ROW_NUMBER() in combination with logical grouping techniques. Here’s a step-by-step explanation and example.

Let’s say you have a booking table with the following structure:

seat_id seat_number booking_status
1 5 Booked
2 6 Booked
3 7 Available
4 8 Booked
5 9 Booked
6 10 Available

Objective:

We want to find consecutive seat numbers with a status of “Booked.”

SQL Query Explanation:

To find consecutive seat numbers, we can:

  1. Use the ROW_NUMBER() function to assign a unique sequence to each seat.
  2. Group the seats by subtracting their position index from the seat number. This technique allows us to group consecutive seats together.

SQL Query Example:

sql
 
WITH NumberedSeats AS (
SELECT
seat_id,
seat_number,
booking_status,
ROW_NUMBER() OVER (ORDER BY seat_number) AS row_num
FROM
booking
WHERE
booking_status = 'Booked'
)
SELECT
MIN(seat_number) AS start_seat,
MAX(seat_number) AS end_seat,
COUNT(seat_number) AS consecutive_count
FROM
NumberedSeats
GROUP BY
(seat_number - row_num)
HAVING
COUNT(seat_number) > 1;

Explanation of the Query:

  1. NumberedSeats CTE: This Common Table Expression (CTE) assigns a sequential row number to each seat based on seat_number.
  2. Grouping Logic: By grouping seat_number - row_num, consecutive seats will have the same grouping key.
  3. Result Set: We use MIN() and MAX() functions to identify the starting and ending seat in each consecutive sequence.
  4. Filtering: The HAVING clause ensures we only get groups with more than one seat (true consecutive seats).

Example Output:

start_seat end_seat consecutive_count
5 6 2
8 9 2

Conclusion:

This query is useful in scenarios where you need to analyze sequential data, like seat bookings in this example. Optimizing queries like these can help improve performance, especially with large datasets.

If you're looking to further enhance your SQL skills, check out some affordable online data courses here.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: