# Start writing code here...

Shopify Data Science Intern Challenge

Questions from Part 1

On Shopify, we have exactly 100 sneaker shops, and each of these shops sells only one model of shoe. We want to do some analysis of the average order value (AOV). When we look at orders data over a 30 day window, we naively calculate an AOV of $3145.13. Given that we know these shops are selling sneakers, a relatively affordable item, something seems wrong with our analysis.

Think about what could be going wrong with our calculation. Think about a better way to evaluate this data. What metric would you report for this dataset? What is its value?

#import all of the packages needed for this project import pandas as pd ##import matplotlib.pyplot as plt ## Found a better graphing package and it worked perfectly for this project import plotly.express as px

#Let's load the file into the dataframe first df = pd.read_csv('/work/datasheet.csv')

df.head()

order_idint64

shop_idint64

df.shape

df.dtypes

Converting created_at columns to datetime data type in order to query the data....

df['created_at'] = pd.to_datetime(df['created_at'])

df.dtypes

df.query('20170301 < created_at < 20170331')

order_idint64

1 - 5000

shop_idint64

1 - 100

4515

4516

4657

4658

1190

1191

347

348

2587

2588

2759

2760

948

949

388

389

3316

3317

2225

2226

4347

4348

2030

2031

1824

1825

707

708

3276

3277

374

375

4358

4359

4028

4029

4908

4909

2941

2942

4153

4154

728

729

3110

3111

2948

2949

4678

4679

1597

1598

2524

2525

588

589

2078

2079

1167

1168

3968

3969

3470

3471

1290

1291

4633

4634

1546

1547

4982

4983

605

606

2193

2194

4784

4785

1020

1021

932

933

1377

1378

4460

4461

1589

1590

4394

4395

2820

2821

856

857

3569

3570

4454

4455

df.duplicated().sum()

df.nunique()

df.describe()

order_idfloat64

shop_idfloat64

count

5000

mean

2500.5

50.0788

std

1443.520003

29.00611766

min

25%

1250.75

50%

2500.5

75%

3750.25

max

5000

100

#calculating AOV for each shop and then take then average d=[] group = df.groupby('shop_id') for key, value in group: d.append([key,value['order_amount'].sum()/value['total_items'].sum()]) df1 = pd.DataFrame(d, columns=["shop_id","AOV Per Shop"]) print(df1) n = len(pd.unique(df['shop_id'])) df1["AOV Per Shop"].sum()/n ##double checking our calculations ##a= df.loc[df['shop_id'] == 1, "order_amount"].sum() ##b = df.loc[df['shop_id'] == 1, "total_items"].sum() ##print(a/b)

    shop_id  AOV Per Shop
0         1         158.0
1         2          94.0
2         3         148.0
3         4         128.0
4         5         142.0
..      ...           ...
95       96         153.0
96       97         162.0
97       98         133.0
98       99         195.0
99      100         111.0

[100 rows x 2 columns]

#Plotting this result and visualizing fig = px.box(df1, y="AOV Per Shop") fig.show()

def find_outliers_IQR(df1): q1=df1.quantile(0.25) q3=df1.quantile(0.75) IQR=q3-q1 outliers = df1[((df1<(q1-1.5*IQR)) | (df1>(q3+1.5*IQR)))] return outliers outliers = find_outliers_IQR(df1["AOV Per Shop"]) print("number of outliers: "+ str(len(outliers))) print("max outlier value: "+ str(outliers.max())) print("min outlier value: "+ str(outliers.min()))

number of outliers: 2
max outlier value: 25725.0
min outlier value: 352.0

df2 = df1[df1['AOV Per Shop'] != 25725] n = len(pd.unique(df2['shop_id'])) AOV_no = df2["AOV Per Shop"].sum()/n print(round(AOV_no))

mode_aov = df2["AOV Per Shop"].mode() print(mode_aov) mode_aov = df1["AOV Per Shop"].mode() print(mode_aov) mean_aov = df1["AOV Per Shop"].mean() print(mean_aov)

0    153.0
dtype: float64
0    153.0
dtype: float64
407.99

Conclusion for Part 1

I would've use mode AOV as the metric as it reduces the effect of outliers. However, if we choose to perform the above analysis and eliminate the outliers from the original dataset, we could also just use the mean calculated from the without outlier dataset. In this case, I would say the value would be 153 and it would be a good starting point for businesses to consider on how to improve their business to maximize revenue. I hope you enjoyed my analysis.

Questions from Part 2 SQL

For this question you’ll need to use SQL.

Follow this link to access the data set required for the challenge. Please use queries to answer the following questions. Paste your queries along with your final numerical answers below.

A. How many orders were shipped by Speedy Express in total?

""" Select Count(OS.OrderID) AS Num_Of_Orders_Shipped_By_SpeedyExpress From Orders AS OS Where (Select SH.ShipperID From Shippers AS SH Where SH.ShipperName = "Speedy Express") = OS.ShipperID; """

B. What is the last name of the employee with the most orders?

""" SELECT Employees.LastName, COUNT(*) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID GROUP BY Employees.LastName ORDER BY NumberOfOrders DESC LIMIT 1; """

C. What product was ordered the most by customers in Germany?

""" SELECT Products.ProductName, SUM(OrderDetails.Quantity) AS "TotalOrdered" FROM Orders JOIN Customers ON Customers.CustomerID = Orders.CustomerID JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID JOIN Products ON Products.ProductID = OrderDetails.ProductID WHERE Customers.Country = 'Germany' GROUP BY OrderDetails.ProductID ORDER BY TotalOrdered DESC LIMIT 1; """