Skip to content

Automate Business Metric Reporting with DataCamp Workspace

You can consult the solution by visiting notebook-solution.ipynb in the file browser.

# Install necessary packages
!pip install slack_sdk
from slack_sdk import WebClient
import os
from datetime import datetime
print (datetime.now())
Spinner
DataFrameas
df3
variable
SELECT @@version

Total sales by month

Spinner
DataFrameas
df1
variable
-- Get total sales over time
SELECT SUM(quantity*list_price*(1-discount)) AS total_sales
FROM sales.orders o
INNER JOIN sales.order_items oi ON o.order_id=oi.order_id
Spinner
DataFrameas
sales_over_time
variable
SELECT 
	DATEFROMPARTS ( DATEPART(year, order_date), DATEPART(month, order_date), 1) AS month,
	SUM(quantity * list_price * (1 - discount)) AS total_sales
FROM sales.orders o
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
WHERE FORMAT(o.order_date,'yyy-MM') ='2018-04'
GROUP BY DATEFROMPARTS ( DATEPART(year, order_date), DATEPART(month, order_date), 1)

Use plotply instead Visualize bar chart for sales_over_time, showing Month on x axis, total_sales on y axis is , dark blue color

import plotly.express as px
from IPython.display import display

# Create a bar chart using Plotly
fig = px.bar(sales_over_time, x='month', y='total_sales', 
             title='Total Sales Over Time',
             labels={'month': 'Month', 'total_sales': 'Total Sales'},
             color_discrete_sequence=['darkblue'])

# Update layout for better visualization
fig.update_layout(xaxis_title='Month', yaxis_title='Total Sales',
                  xaxis_tickformat='%b %Y',
                  bargap=0.2)

# Display the figure
fig.show()
Spinner
DataFrameas
df
variable
SELECT SUM(quantity * list_price * (1 - discount)) AS total_sales,
    FORMAT(order_date, 'yyyy-MM') AS month
FROM sales.orders o
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
GROUP BY FORMAT(order_date, 'yyyy-MM')

Surface top sellers

Spinner
DataFrameas
top_sellers
variable
-- Get top three of best sellers in April 2018 (by staff)
SELECT TOP 3
	CONCAT(s.first_name,' ', s.last_name) AS seller,
	SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS total_sales
FROM sales.orders o
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
INNER JOIN sales.staffs s ON o.staff_id = s.staff_id
WHERE FORMAT(o.order_date,'yyy-MM') ='2018-04'
GROUP BY CONCAT(s.first_name,' ', s.last_name)
ORDER BY total_sales DESC
Spinner
DataFrameas
df2
variable
-- Get top three of best sellers in April 2018 (by product)
SELECT 
p.product_id,
p.product_name, 
FORMAT(o.order_date,'yyyy-MM') AS month,
SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS total_sales
FROM production.products p
INNER JOIN sales.order_items oi ON p.product_id = oi.product_id
INNER JOIN sales.orders o ON o.order_id = oi.order_id
WHERE FORMAT(o.order_date,'yyyy-MM') = '2018-04'
GROUP BY p.product_id, p.product_name, FORMAT(o.order_date,'yyyy-MM')
ORDER BY total_sales DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

Report metrics to Slack

!pip install slack_sdk
import os

slack_oauth_token = os.environ["SLACK_OAUTH_TOKEN"]
‌
‌
‌