#!/usr/bin/env python
# coding: utf-8

# ## Chinook Music Group Micro-Analysis
# **Background**
# <br>
# <br>
# Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. You have been asked to analyze the purchases of customers belonging to each employee to see if any sales support agent is performing either better or worse than the others.
# 
# You might like to consider whether any extra columns from the employee table explain any variance you see, or whether the variance might instead be indicative of employee performance.
# 
# **Instructions**
# * Write a query that finds the total dollar amount of sales assigned to each sales support agent within the company. Add any extra attributes for that employee that you find are relevant to the analysis.
# * Write a short statement describing your results, and providing a possible interpretation.

# <img src="chinook_schema.jpg" width="350" align="center">

# ## Sales Support Agent Performance

# In[1]:


get_ipython().run_cell_magic('capture', '', '%load_ext sql\n%sql sqlite:///chinook.db\n')


# In[2]:


get_ipython().run_cell_magic('sql', '', 'SELECT CAST(SUM(total) AS Int) "Total Sales"\nFROM invoice;\n')


# In[3]:


get_ipython().run_cell_magic('sql', '', 'SELECT COUNT(customer_id) "Number of Customers"\nFROM customer\n')


# In[4]:


get_ipython().run_cell_magic('sql', '', 'WITH\ncust_totals AS\n    (\n     SELECT\n        i.customer_id customer_id,\n        CAST(SUM(i.total) AS Int) cust_total\n     FROM invoice i GROUP BY 1\n    ),\navg_sale AS\n    (\n     SELECT\n        e.employee_id,\n        ROUND(AVG(i.total), 2) average_sale\n     FROM employee e\n     INNER JOIN customer c ON c.support_rep_id = e.employee_id\n     INNER JOIN invoice i ON i.customer_id = c.customer_id\n     GROUP BY 1\n    )\nSELECT\n    e.first_name || " " || e.last_name "Sales Support Agent",\n    SUM(ct.cust_total) "Total Sales",\n    ROUND(SUM(ct.cust_total)*100/(SELECT CAST(SUM(total) AS Float) FROM invoice), 1) "Percent Total Sales",\n    avs.average_sale "Average Sale",\n    COUNT(c.customer_id) "Number of Customers",\n    COUNT(DISTINCT c.country) "Number of Countries"\nFROM customer c\nLEFT JOIN cust_totals ct ON ct.customer_id = c.customer_id\nLEFT JOIN employee e ON e.employee_id = c.support_rep_id\nLEFT JOIN avg_sale avs ON avs.employee_id = e.employee_id\nGROUP BY 1;\n')


# In[5]:


get_ipython().run_cell_magic('sql', '', 'WITH cust_totals AS\n    (\n     SELECT\n        i.customer_id customer_id,\n        CAST(SUM(i.total) AS Int) cust_total\n     FROM invoice i GROUP BY 1\n    )\nSELECT\n    e.first_name || " " || e.last_name "Sales Support Agent",\n    c.country "Country",\n    SUM(cust_totals.cust_total) "Total Sales"\nFROM customer c\nLEFT JOIN cust_totals ON cust_totals.customer_id = c.customer_id\nLEFT JOIN employee e ON e.employee_id = c.support_rep_id\nGROUP BY 2, 1\nORDER BY 2, 3 DESC\n')


# **Sales Support Agent Performance Conclusion**
# * The three sales support agents are separated by about 3% of Chinook Music Groups total sales for 1st, 2nd, and 3rd place. Jane Peacock has the most sales, but also has the largest number of customers and the highest average sale. 
# * Steve Johnson, 3rd place, has the same average sale as Margaret Park in 2nd place, but she has more customers.
# * Steve has the most countries in his sales profile, so that doesn't appear to be strongly correlated to having a high percent of total sales.
# * Also, when comparing sales within the same country, Jane often has the highest total but not always. Margaret and Steve do not show any particular advantage. Margaret's US market has the most sales for any country.