Embedded Files
Analyzing Sports Sales: This Project involves three main parts. First, I used Excel for data cleaning. Then, SQL for data exploration and Power BI to build reports. This dataset consists of customers and orders table. You can download it here.
Data Cleaning and exploration
The cleaning consisted essentially of:
• Conversion of data to correct data types
• Removing duplicate data
• The creation of shipping cost column
• Trim function used to remove trailing spaces.
Concatenate first and last name
Data Exploration
Importing the Excel file into the PostgreSQL database posed some challenges. This YouTube video helped me out.
Insight 1: KPI’s for revenue, profit, profit margin, no of orders
To answer this question, I first retrieved all the rows from the orders table. Then, use the sum and count functions to calculate for revenue, profits and profit margin.
Insight 2: What is the average rating and no of customers rating?
To calculate this, I used a subquery to first counts the number of orders where the ‘rating’ column is not null. Then calculates the average rating across all orders, rounding the result to 2 decimal places.
Insight 3: What is the no of people for each rating and its revenue, profit, profit margin?
Insight 4: What is the state revenue, profit, profit_margin?
This question leads us to apply a row_number() window function. ROW_NUMBER() function assigns a unique sequential integer to each row within a partition of a result set.
Insight 5: Calculate the monthly profit
Modify the query defined in the previous question, I calculated the monthly profit and the difference in profit compared to the previous month using the LAG window function. LAG is particularly useful for comparing the current row with the previous row or for calculating differences between consecutive rows.
Let's explain each steps as illustrated above:
• It first creates a Common Table Expression (CTE) named monthly_profit, which calculates the total profit for each month by extracting the month part from the ‘date’ column and summing up the profits for each month.
• Then, it selects the month and total_profit from the monthly_profit CTE.
• It uses the LAG() window function to retrieve the total profit of the previous month.
• It calculates the profit difference between the current month and the previous month.
• Finally, it orders the results by month.
Report
If you want to learn how to connect Postgres database into Power BI, visit Oyinbooke YouTube channel.
Conclusion
Although, I don’t like sports, this project was an opportunity to utilize my SQL querying skills to uncover valuable insights and then visualize them using Power BI. Link to report.
Page updated
Google Sites
Report abuse