Question 363716 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Airline Seat Assignment Procedure in SQL

I’ve been given these instructions and I’m not sure why it’s not correct.

Here are the instructions:

You work for an airline, and you’ve been tasked with improving the procedure for reserving and buying seats.
You have the table seats, which describes seats in the airplane. It has the following columns:

seat_no – The unique number of the seat;
status – The status of the seat (0 indicates free, 1 indicates reserved, and 2 indicates purchased);
person_id – The ID of the person who reserved/purchased this seat (0 if the corresponding status is 0).
You also have the table requests, which contains the following columns:

request_id – The unique ID of the request;
request – The description of the request (1 indicates reserve, 2 indicates purchase);
seat_no – The number of the seat that the person want to reserve/purchase;
person_id – The ID of the person who wants to reserve/purchase this seat.
A person can reserve/purchase a free seat and can purchase a seat that they have reserved.

Your task is to return the table seats after the given requests have been performed.

Note: requests are applied from the lowest request_id; it’s guaranteed that all values of seat_no in the table requests are presented in the table seats.

Example

For the given tables seats

seat_no status  person_id
      1      1          1
      2      1          2
      3      0          0
      4      2          3
      5      0          0

and requests

 request_id request seat_no person_id
          1       1       3         4
          2       2       2         5
          3       2       1         1

the output should be

 seat_no    status  person_id
       1         2          1
       2         1          2
       3         1          4
       4         2          3
       5         0          0

The first request is completed because seat number 3 is free. The second request is ignored because seat number 2 is already reserved by another person. The third request is completed because seat number 1 was reserved by this person, so they can purchase it.

This is my attempt:

CREATE PROCEDURE solution()
BEGIN
    /* Write your SQL here. Terminate each statement with a semicolon. */
    
WITH ranked_requests AS (
    SELECT
        r.request_id,
        r.request,
        r.seat_no,
        r.person_id,
        ROW_NUMBER() OVER (PARTITION BY r.seat_no ORDER BY r.request_id) AS rn
    FROM
        requests r
),
applied_requests AS (
    SELECT
        s.seat_no,
        COALESCE(MAX(CASE
            WHEN r.request = 1 AND s.status = 0 THEN 1
            WHEN r.request = 2 AND s.status IN (0, 1) AND (s.status = 0 OR s.person_id = r.person_id) THEN 2
            ELSE s.status
        END), s.status) AS status,
        COALESCE(MAX(CASE
            WHEN r.request = 1 AND s.status = 0 THEN r.person_id
            WHEN r.request = 2 AND s.status IN (0, 1) AND (s.status = 0 OR s.person_id = r.person_id) THEN r.person_id
            ELSE s.person_id
        END), s.person_id) AS person_id
    FROM
        seats s
        LEFT JOIN ranked_requests r ON s.seat_no = r.seat_no
    GROUP BY
        s.seat_no
)
SELECT
    a.seat_no,
    a.status,
    a.person_id
FROM
    applied_requests a
ORDER BY
    a.seat_no;
    
    
    
END

These were the error messages from CodeSignal which were not very helpful which is why I’m lost:

Test 1 Passed
Input
Return Value
seats
seat_no status  person_id
      1      1          1
      2      1          2
      3      0          0
      4      2          3
      5      0          0

requests
request_id  request seat_no person_id
         1        1       3         4
         2        2       2         5
         3        2       1         1

Test 2
Wrong answer


seats
seat_no status  person_id
      1      2          1
      2      1          2
      3      0          0
      4      2          3
      5      0          0
      6      0          0
      7      2          1
      8      1         31
      9      2         81
     10      2         10
10 out of 10 rows displayed.
Show less
requests
request_id  request seat_no person_id
         1        1       3         4
         2        2       2         5
         3        2       1         1
         4        1       9        81
         5        2      10        10
         6        1       3        59
6 out of 6 rows displayed.
Show less
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is an answer that works:

    CREATE PROCEDURE solution()
BEGIN
    /* Write your SQL here. Terminate each statement with a semicolon. */
    
SELECT
    s.seat_no,
    -- Determine the final status of each seat after applying all requests
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 2
              AND (s.status = 0 OR (s.status = 1 AND s.person_id = r.person_id))
        ) THEN 2
        WHEN EXISTS (
            SELECT 1
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 1
              AND s.status = 0
        ) THEN 1
        ELSE s.status
    END AS status,
    -- Update the person_id for the seat if a new reservation or purchase is made
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 2
              AND (s.status = 0 OR (s.status = 1 AND s.person_id = r.person_id))
        ) THEN (
            SELECT r.person_id
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 2
              AND (s.status = 0 OR (s.status = 1 AND s.person_id = r.person_id))
            ORDER BY r.request_id
            LIMIT 1
        )
        WHEN EXISTS (
            SELECT 1
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 1
              AND s.status = 0
        ) THEN (
            SELECT r.person_id
            FROM requests r
            WHERE r.seat_no = s.seat_no
              AND r.request = 1
              AND s.status = 0
            ORDER BY r.request_id
            LIMIT 1
        )
        ELSE s.person_id
    END AS person_id
FROM
    seats s
ORDER BY
    s.seat_no;
    
    
    
END

  2. 0

    Here’s another answer, alittle simpler:

    CREATE PROCEDURE solution()
BEGIN
    /* Write your SQL here. Terminate each statement with a semicolon. */
    
WITH ReservationEligibility AS (
    SELECT 
        r.seat_no, 
        r.person_id
    FROM requests r
    JOIN seats s ON r.seat_no = s.seat_no AND r.request = 1 AND s.status = 0
    ORDER BY r.request_id
    LIMIT 1
),
PurchaseEligibility AS (
    SELECT 
        r.seat_no, 
        r.person_id
    FROM requests r
    JOIN seats s ON r.seat_no = s.seat_no AND r.request = 2 
    WHERE s.status = 0 OR (s.status = 1 AND s.person_id = r.person_id)
    ORDER BY r.request_id
    LIMIT 1
),
FinalStatus AS (
    SELECT
        s.seat_no,
        CASE
            WHEN p.seat_no IS NOT NULL THEN 2
            WHEN res.seat_no IS NOT NULL THEN 1
            ELSE s.status
        END AS status,
        COALESCE(p.person_id, res.person_id, s.person_id) AS person_id
    FROM seats s
    LEFT JOIN ReservationEligibility res ON s.seat_no = res.seat_no
    LEFT JOIN PurchaseEligibility p ON s.seat_no = p.seat_no
)
SELECT 
    seat_no,
    status,
    person_id 
FROM FinalStatus
ORDER BY seat_no;
    
    
    
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search