Skip to content

Introduction

Here I'm going to practice some queries and let's see what we can find about this dataset.

import pandas as pd
import matplotlib.pyplot as plt
Spinner
DataFrameas
df2
variable
SELECT 
    t.name AS tabela, 
    c.name AS coluna,
    ty.name AS tipo
FROM 
    sys.tables t
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.types ty ON c.user_type_id = ty.user_type_id
ORDER BY 
    t.name, c.column_id;
Spinner
DataFrameas
df3
variable
SELECT 
  store_id,
  COUNT(*) AS number_of_staff
FROM 
  sales.staffs
GROUP BY 
  store_id;
Spinner
DataFrameas
df4
variable
SELECT order_id, COUNT(*)
FROM sales.order_items
GROUP BY order_id
HAVING COUNT(*) > 1;

Revenue per State

Spinner
DataFrameas
df
variable
SELECT sum(a.list_price) as Revenue, c.store_name as Store, c.state
FROM sales.order_items AS a
INNER JOIN sales.orders AS b
	ON a.order_id = b.order_id
INNER JOIN sales.stores AS c
	ON b.store_id = c.store_id
    
GROUP BY c.store_name,c.state
ORDER BY Revenue DESC
import plotly.express as px

fig = px.pie(df, values='Revenue', names='state')
fig.update_traces(textposition='inside')
fig.update_layout(uniformtext_minsize=14, uniformtext_mode='hide')
fig.show()

Revenue for each Year

Spinner
DataFrameas
df
variable
SELECT
    YEAR(b.required_date) AS Sales_year,
    SUM(a.list_price) as Revenue
FROM
	sales.orders AS b
INNER JOIN
	sales.order_items AS a
ON
	b.order_id = a.order_id
GROUP BY
	YEAR(b.required_date)
ORDER BY
    YEAR(b.required_date)
fig = px.bar(df, x="Sales_year", y="Revenue", barmode='group')
fig.update_xaxes(type='category')
fig.show()

Revenue for each Store in each Year

Spinner
DataFrameas
df
variable
SELECT
    YEAR(b.required_date) AS Sales_year,
    SUM(a.list_price) as Revenue,
    c.state as State
FROM
	sales.orders AS b
INNER JOIN
	sales.order_items AS a
ON
	b.order_id = a.order_id
INNER JOIN
    sales.stores AS c
ON
    b.store_id = c.store_id
GROUP BY
	YEAR(b.required_date), c.state
ORDER BY
    YEAR(b.required_date)
import plotly.express as px

fig = px.bar(df, x="Sales_year", y="Revenue", color='State',barmode='group')
fig.show()

Best Seller Models

‌
‌
‌