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 | --- | --- | --- |
| ... | ... | ... | ... |
SELECT *
FROM sales;SELECT
product_line,
TO_CHAR(date, 'Month') AS month,
warehouse,
SUM(total) AS total
FROM sales
GROUP BY
product_line,
month,
warehouse,
client_type
HAVING client_type = 'Wholesale';SELECT
product_line,
TO_CHAR(date, 'Month') AS month,
warehouse,
SUM(total) - SUM(payment_fee) AS net_revenue
FROM sales
GROUP BY
product_line,
month,
warehouse,
client_type
HAVING client_type = 'Wholesale';SELECT
product_line,
TO_CHAR(date, 'Month') AS month,
warehouse,
SUM(total) - SUM(payment_fee) AS net_revenue
FROM sales
GROUP BY
product_line,
month,
warehouse,
client_type
HAVING client_type = 'Wholesale'
ORDER BY product_line, month, net_revenue DESC;Insights and Analysis
Overall Revenue by Product Line:
-
Frame & Body:
- Highest net revenue in August across all warehouses: 8657.99 (Central).
- Strong performance in July with 6154.61 (North) and 3135.13 (Central).
- Consistently high revenue in June as well: 5111.34 (Central) and 4910.12 (North).
-
Suspension & Traction:
- Highest net revenue in June (North) with 8065.74.
- Significant performance in July: 6456.72 (Central) and 3714.28 (North).
- August performance is also notable with 5416.70 (Central).
-
Braking System:
- Highest revenue in July (Central) with 3778.65.
- Strong in August with 3039.41 (Central).
- June shows good results with 3684.89 (Central), but weaker compared to Frame & Body and Suspension & Traction.
-
Electrical System:
- Highest revenue in July (Central) with 5577.62.
- August performance is strong with 4721.12 (North) and 3126.43 (Central).
- Lower revenue in June with 2904.93 (Central).
-
Engine:
- Highest revenue in August (Central) with 9528.71.
- Also notable in June (Central) with 6548.85.
- Lower in July, particularly in North with 1007.14.
-
Miscellaneous:
- Lowest revenue compared to other product lines, even in peak months.
- Highest in July (Central) with 3118.44.
- August revenue shows a peak in North with 1841.40.
Revenue by Month:
-
August:
- Frame & Body and Engine show peak revenues with notable figures across regions.
- Suspension & Traction also performs strongly.
- Electrical System shows varied performance across regions.
-
July:
- Suspension & Traction and Electrical System show strong performance, particularly in Central.
- Frame & Body has significant revenue in the North.
- Braking System also performs well.
-
June:
- Suspension & Traction shows the highest revenue, especially in North.
- Frame & Body also performs well, especially in Central.
- Engine performs strongly in Central, with notable figures.
Revenue by Warehouse/Region:
-
Central:
- Generally shows the highest revenue across most product lines and months.
- Significant in Frame & Body (August) and Suspension & Traction (July).
-
North:
- High revenue for Suspension & Traction and Frame & Body.
- Consistently strong performance across months.
-
West:
- Generally lower compared to Central and North.
- Braking System shows relatively high revenue in August and July.
Key Observations:
-
Peak Months: August shows strong performance across several product lines, suggesting higher demand or better sales strategies during this month.
-
High Performers:
- Frame & Body and Suspension & Traction are the top-performing product lines in terms of net revenue.
- Engine also shows high revenues, particularly in August and June.
-
Regional Focus:
- Central is the strongest region in terms of overall revenue.
- North performs well in Frame & Body and Suspension & Traction, indicating a significant market.
Recommendations:
- Optimize for Peak Months: Increase inventory and marketing efforts before peak months (e.g., August) to capitalize on higher demand.
- Focus on High-Performing Regions: Invest more resources in Central and North regions due to their high performance across various product lines.
- Address Lower-Performing Regions: Investigate and improve strategies in the West region to boost performance and balance revenue across all regions.