Here is the challenge: https://preppindata.blogspot.com/2024/01/2024-week-4-unpopular-seats.html
![](https://www.thedataschool.co.uk/content/images/2025/01/image-325.png)
And here is my SQL solution! First things first:
Step 1: Load in the data, have a peek
![](https://www.thedataschool.co.uk/content/images/2025/01/image-326.png)
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.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-331.png)
That'll do nicely.
Step 3: Aggregate to count booking for each seat in each seat, row, and 'flow card' category.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-332.png)
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:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-333.png)
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).
![](https://www.thedataschool.co.uk/content/images/2025/01/image-334.png)
and there have it! 7 seats avoided by Prep Air customers.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-336.png)
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
;