Find some answers and insights for the following questions:
What are the popular choices of booking rates (table rate, columns ShortRateName or RateName) for different segments of customers (table reservation, columns AgeGroup, Gender, NationalityCode)?
What are the typical guests who do online check-in? Is it somehow different when you compare reservations created across different weekdays (table reservation, IsOnlineCheckin column)?
Look at the average night cost per single occupied capacity. What guest segment is the most profitable per occupied space unit? And what guest segment is the least profitable?
First, answer the questions using SQL and with your answer provide also the final SQL queries and output.
Then, build a report/dashboard ideally using Power BI that also help answering the questions. You may also use python and build a jupyter notebook.
Include the comments to present your solution
Attached are two CSV files rates and reservations that you can load into SQL database of your choice.
For 3rd task, you will use NightCost_Sum and OccupiedSpace_Sum columns. Since the data already contains aggregated values, here is how the OccupiedSpace_Sum is calculated:
ISNULL(SUM(RoomCategory.[Capacity] + RoomCategory.[ExtraCapacity]), 0) as OccupiedSpace_Sum,
LEFT JOIN Room ON ([login to view URL] = [login to view URL])
LEFT JOIN RoomCategory ON ([login to view URL] = [login to view URL])
where Capacity is pretty much how many people you can fit into the space, or more vaguely how many beds are there.