Skip to content
Project: Analyzing Motorcycle Part Sales
Analyzing sales data!
The purpose of this project is to gain deeper insights into wholesale revenue across product lines at three warehouses in the area. These warehouses sell both retail and wholesale products, offering a variety of parts and accepting credit cards, cash, and bank transfer as payment methods, each incurring a different fee.
The project aims to track monthly performance and compare revenue between different stores. It involves calculating the net income for each product line, aggregating the results by month and store location. To ensure accuracy, the analysis will focus solely on 'wholesale' orders.
The database ontains the following table called sales:
Sales
| Column | Data type | Description |
|---|---|---|
order_number | VARCHAR | Unique order number. |
date | DATE | Date of the order, from June to August 2021. |
warehouse | VARCHAR | The warehouse that the order was made from— North, Central, or West. |
client_type | VARCHAR | Whether the order was Retail or Wholesale. |
product_line | VARCHAR | Type of product ordered. |
quantity | INT | Number of products ordered. |
unit_price | FLOAT | Price per product (dollars). |
total | FLOAT | Total price of the order (dollars). |
payment | VARCHAR | Payment method—Credit card, Transfer, or Cash. |
payment_fee | FLOAT | Percentage of total charged as a result of the payment method. |
DataFrameas
revenue_by_product_line
variable
WITH sales_data AS (
SELECT
product_line,
EXTRACT(month FROM date) AS month,
warehouse,
total * (1 - payment_fee) AS net_revenue,
ROW_NUMBER() OVER (PARTITION BY product_line, EXTRACT(month FROM date), warehouse ORDER BY total DESC) AS rn
FROM
sales
WHERE
client_type = 'Wholesale'
)
SELECT
product_line,
CASE
WHEN month = 6 THEN 'June'
WHEN month = 7 THEN 'July'
WHEN month = 8 THEN 'August'
END AS month_name,
warehouse,
ROUND(net_revenue::numeric, 2) AS net_revenue
FROM
sales_data
WHERE
rn = 1
ORDER BY
product_line,
month,
net_revenue DESC;