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 product_line,
CASE
WHEN EXTRACT(MONTH from date) = 1 THEN 'January'
WHEN EXTRACT(MONTH from date) = 2 THEN 'February'
WHEN EXTRACT(MONTH from date) = 3 THEN 'March'
WHEN EXTRACT(MONTH from date) = 4 THEN 'April'
WHEN EXTRACT(MONTH from date) = 5 THEN 'May'
WHEN EXTRACT(MONTH from date) = 6 THEN 'June'
WHEN EXTRACT(MONTH from date) = 7 THEN 'July'
WHEN EXTRACT(MONTH from date) = 8 THEN 'August'
WHEN EXTRACT(MONTH from date) = 9 THEN 'September'
WHEN EXTRACT(MONTH from date) = 10 THEN 'October'
WHEN EXTRACT(MONTH from date) = 11 THEN 'November'
WHEN EXTRACT(MONTH from date) = 12 THEN 'December'
END as month,
warehouse,
SUM(total) - SUM(payment_fee) as net_revenue
FROM public.sales
WHERE client_type = 'Wholesale'
GROUP BY product_line, month, warehouse
ORDER BY product_line,
month,
warehouse, net_revenue DESC;import plotly.express as px
import plotly.graph_objects as go
from ipywidgets import widgets
from IPython.display import display
# Create a dropdown widget for selecting warehouses
warehouse_dropdown = widgets.Dropdown(
options=df['warehouse'].unique(),
description='Warehouse:',
disabled=False,
)
# Function to update the chart based on selected warehouse
def update_chart(warehouse):
filtered_df = df[df['warehouse'] == warehouse]
fig = px.sunburst(filtered_df,
path=['month', 'product_line'],
values='net_revenue',
title=f'Net Revenue by Product Line and Month for {warehouse} Warehouse',
labels={'net_revenue': 'Net Revenue', 'month': 'Month', 'product_line': 'Product Line'},
height=600)
fig.update_layout(margin=dict(t=50, l=25, r=25, b=25))
fig.show()
# Display the dropdown and update the chart based on selection
widgets.interactive(update_chart, warehouse=warehouse_dropdown)
display(warehouse_dropdown)