Skip to content
Automate Business Metric Reporting with DataCamp Workspace
  • AI Chat
  • Code
  • Report

    • 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
    DataFrameavailable as
    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
    DataFrameavailable as
    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