Bike Sales Data Analysis

Technologies used: SQL Server (T-SQL), Excel, Power BI
Dashboard File: bike_sales_dashboard.pbix
Dataset: BikeSales-DB.xlsx
T-SQL Queries: Bike_Sales_Queries_1.sql
Github: bikesales-Queries

Biking is a fun activity. It is also enormously enjoyed as a means of commuting in countries such as the Netherlands. The associated equipment can be grouped into roughly three categories: accessories, clothing and obviously, bikes. Which category drives sales for biking equipment? How do equipment sales fluctuate within a year? How do sales vary by country?

In this project, I analyse a fictional dataset (source: Kaggle Datasets) about bike sales. The dataset is comprised of information such as order details, product names, revenue, profit and customers' age and gender. After cleaning the data in Microsoft Excel and examining it using T-SQL queries, I created a dashboard (see above) in Microsoft Power BI to visualize and summarize its important aspects, such as the sum of profit, sum of revenue, the revenue by country and the like.

Some queries...

See the full list of queries here.

Finding the net revenue by product category.

Input:

SELECT Product_Category, SUM(Profit) AS [Net Revenue]
FROM BikeSalesDB..bike_sales
GROUP BY Product_Category

• Bikes seem to be the biggest source of revenue. 🤔

For each country, find the percentage of units sold for each category.

Input:

WITH sum_units_sold_coun_cat AS (
	SELECT Country, SUM(Order_Quantity) AS Total_Sold
	FROM BikeSalesDB..bike_sales
	GROUP BY Country
)
SELECT bsales.Country, bsales.Product_Category, 
		ROUND(SUM(bsales.Order_Quantity) / coun_cat.Total_Sold, 5) AS [Perc of Units Sold]
FROM BikeSalesDB..bike_sales bsales INNER JOIN 
		sum_units_sold_coun_cat coun_cat
		ON bsales.Country = coun_cat.Country
GROUP BY bsales.Country, bsales.Product_Category, coun_cat.Total_Sold
ORDER BY 1, 2

• Items from the "Accessories" category are the most sold items in every country - as expected. 🤷
• Bikes constitue less than 10% of sales in every country.

Finding the top 3 products in terms of revenue for each age group.

Input:

WITH product_rev_by_age AS (
	SELECT Age_Group, Product, SUM(Revenue) AS Revenue
	FROM BikeSalesDB..bike_sales
	GROUP BY Age_Group, Product
),
top_three_prod AS (
	SELECT Age_Group, Product, Revenue,
			RANK() OVER (PARTITION BY Age_Group ORDER BY Revenue DESC) Rev_Rank
	FROM product_rev_by_age
)
SELECT Age_Group AS [Age Group],
		Product,
		Rev_Rank AS [Revenue Rank],
		Revenue
FROM top_three_prod
WHERE Rev_Rank < 4

• The Road-150 Red is present in the top 3 for all age groups, except for "Seniors (64+)".

Citations

Sadiq Shah. 2020. Bike Sales in Europe, Version 1. [Online Resource]

I generally reply to email inquiries within 3-5 business days. The best time to reach me by phone is between 11am and 5pm during weekdays. Feel free to leave a message on my voicemail if I'm temporarily unreachable via phone call.

• olivieryannick5@gmail.com
• +1 (587) 987-2552
• Edmonton, AB, Canada