Skip to content

Instantly share code, notes, and snippets.

@mnhpub

mnhpub/gist:bf34d8404f4daf160bd5de63aad72672

Created February 10, 2026 22:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mnhpub/bf34d8404f4daf160bd5de63aad72672 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mnhpub/bf34d8404f4daf160bd5de63aad72672 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mnhpub created this gist Feb 10, 2026.
    120 changes: 120 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,120 @@
    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.

    [execution time limit] 10 seconds (mysql)

    [memory limit] 1 GB

    -- Procedure to process seat reservation and purchase requests
    -- Requests are applied from lowest request_id to highest
    -- Rules:
    -- - Reserve (request=1): Can only reserve if seat is free (status=0)
    -- - Purchase (request=2): Can purchase if seat is free (status=0) or if person already reserved it (status=1, person_id matches)

    DROP PROCEDURE IF EXISTS process_requests;

    DELIMITER //

    CREATE PROCEDURE process_requests()
    BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_request_id INT;
    DECLARE v_request_type INT;
    DECLARE v_seat_no INT;
    DECLARE v_person_id INT;
    DECLARE v_status INT;
    DECLARE v_seat_person_id INT;

    DECLARE cur_requests CURSOR FOR
    SELECT request_id, request, seat_no, person_id
    FROM requests
    ORDER BY request_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_requests;

    read_loop: LOOP
    FETCH cur_requests INTO v_request_id, v_request_type, v_seat_no, v_person_id;
    IF done THEN
    LEAVE read_loop;
    END IF;

    -- Get current seat status
    SELECT status, person_id INTO v_status, v_seat_person_id
    FROM seats WHERE seat_no = v_seat_no;

    -- Handle reserve request (request_type = 1)
    IF v_request_type = 1 THEN
    -- Can reserve only if seat is free (status=0)
    IF v_status = 0 THEN
    UPDATE seats
    SET status = 1, person_id = v_person_id
    WHERE seat_no = v_seat_no;
    END IF;

    -- Handle purchase request (request_type = 2)
    ELSEIF v_request_type = 2 THEN
    -- Can purchase if:
    -- 1. Seat is free (status=0), OR
    -- 2. Seat is reserved by this person (status=1 AND person_id matches)
    IF v_status = 0 OR (v_status = 1 AND v_seat_person_id = v_person_id) THEN
    UPDATE seats
    SET status = 2, person_id = v_person_id
    WHERE seat_no = v_seat_no;
    END IF;
    END IF;

    END LOOP;

    CLOSE cur_requests;
    END //

    DELIMITER ;

    -- Execute the procedure
    CALL process_requests();

    -- Return the final seats table
    SELECT * FROM seats ORDER BY seat_no;