As a newbie in the data analysis field, finding the right project to carry out for your portfolio might be a daunting task, but if you have been in search of the perfect one, you can try this.
This was a project done to by Analysing the data from a hotel between 2019 and 2022.
It enabled me use two Data Analytical tools(SQL to Query the data, and Power Bi to Visualize it).
Below are the steps(including screenshots) of how I everything I did.
The Raw Data
THE DATA IN SQL Server
After uploading the data into the SQL Server, I performed a “UNION” between the 2018,2019,2020 data as they had similar columns.
This was done to make my quering easier
CREATING A TEMPORARY TABLE:
After the Union, I decided to put all data in a temporary table, so I could refer to them by the name “hotels” instead of copying all the union statement everytime I wanted to refer to that data.
GETTING REVENUE
After creating this Temporary table, I looked to figure out how much Revenue the Hotel made yearly.
This would have been straight forward, but there was no column for "Revenue" in the data provided, so I had to make one.
The Revenue was gotten by adding the amount generated by weeknight stays and weekend night stays, and multiplying the result by ADR(daily rates)
REVENUE PER-YEAR
knowing only the revenue was not enough, I had to know how it translated to the amount of years
REVENUE PER YEAR(Grouped)
The Group by clause helped sum every amount generated in a year.
Business Question
With the data in this form, the stakeholders can get a clear picture of what amount was gotten by the Hotel in each year.
MARKET SEGMENT and MARKET COST
The next step was to join the Table for Meal cost and the table for Market segment, so all the tables could be together, and be visualized in Power Bi.
Market Segment
A Left join was made using the “Market Segment” column of the Market segment data and the “market segment” column of the data in the Temporary table.
Market segment column in market segment data
Market segment data in theTemporary table
Market Segment now part of Temporary table
Market Cost
A similar thing(Left Join) was done with the Market cost and the data was exported to Power Bi for visualization.
This project allowed me to use most of the SQL knowledge I had gained from learning SQL, including creating new columns, calculating revenue by combining weeknight and weekend stays with daily rates, grouping revenue by year using the Group by clause, and joining tables for meal costs and market segments that can later be visualized.