Saturday, 2020-12-05 Posted in [Sql]
Pretty amazing the tasks they ask you to do “on the fly”: Yesterday we fixed the passport scanner, today you have to write a piece of code which scans all the boarding passes in your environment (no one said they all carry them in the hand, and show it to you), and while waiting in the gangway you also have to identify your designated seat. Because, you know, you are such a good hacker you lost your boarding pass on the way from the gate to the plane ;-)
Task 1: Scan all the boarding passes with your phone camera, extract the code, and apply binary pattern matching to identify which seats exist. The airline has a pretty complex scheme going on, which depends on binary space partitioning. Find the highest seat number on the plane.
Task 2: Use the scanned data and find your seat. Seat belts sign is already on and you are still hacking in the gangway, hurry up! The only information you have: your seat exists, it’s not the first and last one, and the seats next to yours (-1, +1) exist.
Load the data into a table. I don’t need an identifier today:
This goes into a CTE, to allow easier access to the data. Strictly speaking that’s not necessary for task 1, I can apply the MAX() right to the result and solve the problem. But I need better data access for task 2.
WITH seat_nums AS (
SELECTCASEWHENSUBSTRING(valueFROM1FOR1) ='F'THEN0ELSE512END+CASEWHENSUBSTRING(valueFROM2FOR1) ='F'THEN0ELSE256END+CASEWHENSUBSTRING(valueFROM3FOR1) ='F'THEN0ELSE128END+CASEWHENSUBSTRING(valueFROM4FOR1) ='F'THEN0ELSE64END+CASEWHENSUBSTRING(valueFROM5FOR1) ='F'THEN0ELSE32END+CASEWHENSUBSTRING(valueFROM6FOR1) ='F'THEN0ELSE16END+CASEWHENSUBSTRING(valueFROM7FOR1) ='F'THEN0ELSE8END+CASEWHENSUBSTRING(valueFROM8FOR1) ='L'THEN0ELSE4END+CASEWHENSUBSTRING(valueFROM9FOR1) ='L'THEN0ELSE2END+CASEWHENSUBSTRING(valueFROM10FOR1) ='L'THEN0ELSE1ENDAS seat_num
SELECT MAX(seat_num) FROM seat_nums;
The highest seat number is 938, that’s quite large. A quick count over seat_nums shows that there are 868 entries in the list. Wow, that’s a large plane!
Got curious, so I checked the largest (to date) commercial aircraft which is the A380. And indeed, the A380-800 is certified for 868 passengers in a one-class configuration. Pretty sure that’s not a coincidence. Do you have other numbers in your list?
Need to find my seat, the plane wants to depart! Based on the seat numbers on the seat_nums list I need to calculate the neighbour seat (+1 and -1) numbers. And then simply check if these seats exist in the list. For that I have to loop over all available seats on the plane - I have an extra CTE to figure out the maximum seat number, and use that in the generate_series() loop - and then check if the seats exist in the list. Here is the CTE for the max seat number:
max_seat AS (
SELECT MAX(seat) AS max_seat_num
And the CTE to calculate the neighbour seats:
seats_exist AS (
SELECT seat AS seat,
seat +1AS sp1,
seat -1AS sm1