Bicycle Sales Database
👋 Welcome to your workspace! Here, you can run SQL queries, write Python code, and add text in Markdown. This workspace is automatically connected to a Microsoft SQL Server database containing two schemas (sales and production), which in turn have various details about the sales and inventory for bike stores (source).
You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables for each schema.
There is a short query and a visualization of the number of bicycles in stock by year and category rendered in Plotly to get you started.
SELECT
model_year,
category_name,
SUM(quantity) AS total
FROM production.products AS pp
INNER JOIN production.categories AS pc
ON pp.category_id = pc.category_id
INNER JOIN production.brands AS pb
ON pp.brand_id = pb.brand_id
INNER JOIN production.stocks AS ps
ON pp.product_id = ps.product_id
GROUP BY model_year, category_name
ORDER BY model_year, category_name # Import libraries
import pandas as pd
import plotly.express as px
# Create bar chart
fig = px.bar(
bicycle_sales,
x=bicycle_sales["model_year"].astype(str),
y="total",
color="category_name",
)
# Create labels and show plot
fig.update_layout(
title="Bicycle Inventory<br><sup>By Quantity and Category of Product</sup>",
title_x=0.5,
xaxis_title="Year",
yaxis_title="Quantity in Stock",
legend_title="Bicycle Category",
template="plotly_dark",
)
fig.show()This is an interactive plot! Hover over different elements of the plot to explore the data!
💪 Now it's your turn to construct your own queries and analyze the data! Remember, you can review the tables in the database at any point using the "Browse tables" button.
select count(*) count from production.products;