SQLtest SQL lessons for beginners

Explanation of the Aircraft Seat Map Task

Main Goal of the Task

The task is to create a visual seat map for a specific aircraft model (in this case, 'Boeing 777-300'). The result should be a list of rows, where for each row and service class ('Business', 'Economy', 'Comfort'), an array of available seats is shown.

For example, for the 1st row of business class, it might look like this:

• Row: 1
• Seats: ["A", "C", "D", "F"]
• Class: Business

Solution Logic (Step-by-Step)

1. Step 1: Selecting the Right Aircraft.

   • First, you need to filter the data to work only with seats belonging to the 'Boeing 777-300' model.
   • To do this, you need to join the seats table with the aircrafts_data table on the common field aircraft_code.
   • Then, in the WHERE clause, specify the desired aircraft model.

2. Step 2: Splitting the Seat Number into Row and Letter.

   • The seat number in the seats table has a format like 12A, 30K, where 12 is the row number, and A is the seat letter in the row.
   • The row number can be obtained by removing the letter part from the seat_no string. For example, using the rtrim function or regular expressions.
   • The seat letter can be obtained by taking the last character from the seat_no string (e.g., with the right function).

3. Step 3: Grouping the Seats.

   • The main idea is to group all seats that are in the same row and belong to the same service class (fare_conditions).
   • Therefore, the GROUP BY clause must include both the calculated row number and the service class.

4. Step 4: Aggregating Seat Letters into an Array.

   • For each group (e.g., "row 10, economy class"), we need to collect all the seat letters (A, B, C, etc.) into a single list or array.
   • The aggregate function json_agg (in PostgreSQL) is ideal for this, as it creates a JSON array from the values.
   • To ensure the seats in the array are in the correct order (A, B, C, not C, A, B), sorting must be added inside the json_agg function.

5. Step 5: Final Sorting.

   • To make the seat map look logical (rows going from the front to the back of the plane), the final result needs to be sorted by the row number.

        select 
            rtrim(seat_no, 'ABCDEFGHKJ')::int "row", 
            json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats , 
            fare_conditions 
        from seats
        join aircrafts_data using(aircraft_code)
        where aircrafts_data.model->>'en' = 'Boeing 777-300'
        group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
        order by "row";