Here is the challenge: https://preppindata.blogspot.com/2024/01/2024-week-4-unpopular-seats.html
And here is my SQL solution! First things first:
Step 1: Load in the data, have a peek
Step 2: Union the three flow card tables together
We want to do this without losing the information regarding whether or not a customer is a flow card holder. We therefore need a calculated field called 'Flow-Card Holder?' in each table containing 'Flow-Card' or 'Non Flow-Card'. Then we can stick them all together with a simple union.
That'll do nicely.
Step 3: Aggregate to count booking for each seat in each seat, row, and 'flow card' category.
Step 4: Join to Seating Plan data. Return records for seats not booked.
The key here is utilising a full join, this way we don't lose any data, and can use the null values for aggregated booking count to identify the unbooked seats. First the join:
If we filter this by the null values mentioned above, we can find those undesirable spots! We'll do this with the 'HAVING' function (which is use to filter results post aggregation).
and there have it! 7 seats avoided by Prep Air customers.
Full code:
WITH unioned as
(SELECT
'Flow-Card' as Flow_Card_Holder
,*
FROM
FLOW_CARD
UNION
SELECT
'Non Flow-Card' as Flow_Card_Holder
,*
FROM
NON_FLOW_CARD1
UNION
SELECT
'Non Flow-Card' as Flow_Card_Holder
,*
FROM
NON_FLOW_CARD2
)
,aggregated as (
SELECT
Flow_Card_Holder
,COUNT(CUSTOMERID) as bookingcount
,SEAT
,"Row"
,CLASS
FROM unioned
GROUP BY 1,3,4,5
)
SELECT seat_plan.class
,seat_plan.seat
,seat_plan."Row"
FROM aggregated
FULL OUTER JOIN SEAT_PLAN
ON aggregated.seat = seat_plan.seat
AND aggregated."Row" = seat_plan."Row"
AND aggregated.CLASS = SEAT_PLAN.class
HAVING bookingcount IS NULL
;