Skip to content

Analyzing bicycle sales with SQL and Python

Let's take a look at the dataset. We'll be using Microsoft SQL Server database, SQL cells in Workspace and Python visualization libraries to draw insights.

Spinner
DataFrameas
df
variable
-- Query sales data
SELECT
    o.order_date,
    c.first_name,
    c.last_name,
    p.product_name,
    oi.quantity,
    oi.list_price,
    oi.discount,
    (oi.list_price * oi.quantity * (1 - oi.discount)) AS total_price
FROM
    sales.orders o
    JOIN sales.customers c ON o.customer_id = c.customer_id
    JOIN sales.order_items oi ON o.order_id = oi.order_id
    JOIN production.products p ON oi.product_id = p.product_id
ORDER BY
    o.order_date;
# Importing necessary libraries
import plotly.express as px
import plotly.graph_objects as go
import pandas as pd

# Plot 1
fig1 = px.scatter(df, x='order_date', y='total_price', color='product_name', title='Total Price vs Order Date')
fig1.show()

# Plot 3
fig3 = go.Figure()
fig3.add_trace(go.Scatter(x=df['order_date'], y=df['list_price'], mode='lines', name='List Price'))
fig3.add_trace(go.Scatter(x=df['order_date'], y=df['discount'], mode='lines', name='Discount'))
fig3.update_layout(title='List Price and Discount over Time', xaxis_title='Order Date', yaxis_title='Price')
fig3.show()