Project: Analyzing Motorcycle Part Sales

You're working for a company that sells motorcycle parts, and they've asked for some help in analyzing their sales data!

They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

They have provided you with access to their database, which contains 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.

Your query output should be presented in the following format:

`product_line`	`month`	`warehouse`	`net_revenue`
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_two	---	---	---
...	...	...	...

DataFrameas

df

variable

-- Consulta para calcular ingresos netos por línea de producto, mes y almacén (solo Wholesale)
SELECT 
    product_line,
    CASE 
        WHEN EXTRACT(MONTH FROM date) = 6 THEN 'June'
        WHEN EXTRACT(MONTH FROM date) = 7 THEN 'July'
        WHEN EXTRACT(MONTH FROM date) = 8 THEN 'August'
    END AS month,
    warehouse,
    ROUND(SUM(total) - SUM(payment_fee), 2) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY 
    product_line, 
    EXTRACT(MONTH FROM date), 
    warehouse
ORDER BY 
    product_line, 
    EXTRACT(MONTH FROM date),
    net_revenue DESC;

DataFrameas

revenue_by_product_line

variable

-- Guardar como revenue_by_product_line (según especificaciones del proyecto)
SELECT 
    product_line,
    CASE 
        WHEN EXTRACT(MONTH FROM date) = 6 THEN 'June'
        WHEN EXTRACT(MONTH FROM date) = 7 THEN 'July'
        WHEN EXTRACT(MONTH FROM date) = 8 THEN 'August'
    END AS month,
    warehouse,
    ROUND(SUM(total) - SUM(payment_fee), 2) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY 
    product_line, 
    EXTRACT(MONTH FROM date), 
    warehouse
ORDER BY 
    product_line, 
    EXTRACT(MONTH FROM date),
    net_revenue DESC;

Analysis of SQL Query Results

Query Validation

Correct data structure: 48 records obtained from 54 possible combinations 4 columns: product_line, month, warehouse, net_revenue Correct ordering: by product line, month, and net revenue descending

Key Business Insights

Performance by Product Line: Frame & body: $39,477.51 (revenue leader) Suspension & traction:$ 38,294.76 Braking system: 21,235.92 Electrical system: 15,747.51 (lowest performance)
Temporal Trends: June: 49,085.35 (16 records) August: $61,455.55 (17 records) - Best performing month
Performance by Warehouse: Central: 58,065.50 (17 records) West: $22,718.91 (13 records) - Lowest performance

Missing Data Identified

6 combinations with no wholesale sales:

Electrical system - June - West Engine - June - North Engine - June - West Engine - July - West Engine - August - West Frame & body - July - West

Critical observation: The West warehouse has significant issues with Engine and Frame & body product lines. Business Recommendations

Warehouse Optimization: Investigate why West warehouse has lower performance Consider inventory reallocation or specific marketing strategies
Focus on Star Products: Strengthen Frame & body and Suspension & traction lines Analyze why Miscellaneous has low performance
Capitalize on August Trend: August growth suggests positive seasonality Prepare inventory and strategies to replicate this pattern
Engine Line Analysis: Multiple missing data points suggest distribution problems Requires immediate attention from the commercial team

SQL Query Quality Assessment The query perfectly meets all requirements:

Wholesale client filter applied
Correct net revenue calculation (total - fees)
Grouping by product, month, and warehouse
Correct month format (June, July, August)
Ordering according to specifications

Total wholesale net revenue: 9,528.71 Frame & body - August - Central: $8,657.99 Suspension & traction - June - North:$ 8,065.74 Frame & body - August - North: 6,548.85

Additional Statistical Insights Revenue Distribution: Minimum: 9,528.71 Average: $3,325.84 per combination