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

Total sales by month

Spinner
DataFrameas
sales_over_time
variable
-- Get total sales over time
SELECT 
	DATEFROMPARTS(DATEPART(year, o.order_date), DATEPART(month, o.order_date),1) AS month,
	SUM(quantity * list_price * (1-discount)) AS total_sales
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi ON o.order_id = oi.order_id 
WHERE o.order_date < '2018-05-01'
GROUP BY DATEFROMPARTS(DATEPART(year, o.order_date), DATEPART(month, o.order_date),1)
ORDER BY month
import plotly.express as px

fig = px.line(sales_over_time, x='month', y='total_sales', title='Total Sales Over Time')
fig.update_layout(xaxis_tickangle=-45)
fig.show()

Surface top sellers

Spinner
DataFrameas
top_sellers
variable
-- Get top three of best sellers in April 2018
-- Get total sales over time
SELECT TOP 3
	CONCAT(first_name, ' ', last_name) AS full_name,
	SUM(quantity * list_price * (1-discount)) AS sales_per_staff
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi ON o.order_id = oi.order_id
INNER JOIN sales.staffs AS s ON o.staff_id = s.staff_id
WHERE 
	DATEPART(year, o.order_date) = 2018
	AND DATEPART(month, o.order_date) = 4
GROUP BY CONCAT(first_name, ' ', last_name)
ORDER BY sales_per_staff DESC

Report metrics to Slack

# Build a message to paste to Slack