2024: Week 9 - Prep Air Capacity

Challenge by: Jenny Martin

Prep Air would like to do some analysis on how their flights are filling up over time. They've given a small sample of flights that will be taking off next month, and the actions that customers who have booked those flights have been taking. 

Inputs

1. A customer actions table in which a new row appears each time a customer takes an action relating to their flight booking 
   
   
   
2. A flight details table detailing how many seats are available for each class on the flight 
   

Requirements

• Input the data
• If the customer has cancelled their flight, make sure all rows are filtered out for that flight 
• For each customer on a flight, filter the dataset to their most recent action
• Based on the Date field, create a field which shows how many seats in total have been booked as of that date for each flight and class
• Hint: Running Sum could be useful here!
• Bring in information about the Flight Details
• Calculate the Capacity %: of the available seats on the flight for each class, what percentage have been booked so far
• For classes which are yet to be booked for a flight, ensure the Capacity % shows as 0% for these rows
• The Date for these rows should be today's date (28/02/2024) 
• Output the data

Output

• 11 fields
• Flight Number
• Flight Date
• Class
• Total Seats booked over time
• Capacity
• Capacity %
• Customer ID
• Action
• Date
• Row
• Seat
• 500 rows (501 including headers)

You can download the output from here. If you want to check your results. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!