{ "cells": [ { "cell_type": "markdown", "id": "93c8feee", "metadata": {}, "source": [ "## Chinook Music Group Micro-Analysis\n", "**Background**\n", "
\n", "
\n", "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.\n", "\n", "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.\n", "\n", "**Instructions**\n", "* 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.\n", "* Write a short statement describing your results, and providing a possible interpretation." ] }, { "cell_type": "markdown", "id": "5a458d26", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "id": "5d2a2eba", "metadata": {}, "source": [ "## Sales Support Agent Performance" ] }, { "cell_type": "code", "execution_count": 1, "id": "9b50c313", "metadata": {}, "outputs": [], "source": [ "%%capture\n", "%load_ext sql\n", "%sql sqlite:///chinook.db" ] }, { "cell_type": "code", "execution_count": 2, "id": "ac15420c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///chinook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
Total Sales
4709
" ], "text/plain": [ "[(4709,)]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT CAST(SUM(total) AS Int) \"Total Sales\"\n", "FROM invoice;" ] }, { "cell_type": "code", "execution_count": 3, "id": "b8765de5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///chinook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
Number of Customers
59
" ], "text/plain": [ "[(59,)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT COUNT(customer_id) \"Number of Customers\"\n", "FROM customer" ] }, { "cell_type": "code", "execution_count": 4, "id": "cd809509", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///chinook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sales Support AgentTotal SalesPercent Total SalesAverage SaleNumber of CustomersNumber of Countries
Jane Peacock172336.68.172110
Margaret Park157733.57.42012
Steve Johnson138929.57.411813
" ], "text/plain": [ "[('Jane Peacock', 1723, 36.6, 8.17, 21, 10),\n", " ('Margaret Park', 1577, 33.5, 7.4, 20, 12),\n", " ('Steve Johnson', 1389, 29.5, 7.41, 18, 13)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH\n", "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", " ),\n", "avg_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", " )\n", "SELECT\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\"\n", "FROM customer c\n", "LEFT JOIN cust_totals ct ON ct.customer_id = c.customer_id\n", "LEFT JOIN employee e ON e.employee_id = c.support_rep_id\n", "LEFT JOIN avg_sale avs ON avs.employee_id = e.employee_id\n", "GROUP BY 1;" ] }, { "cell_type": "code", "execution_count": 5, "id": "7974385f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///chinook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sales Support AgentCountryTotal Sales
Margaret ParkArgentina39
Margaret ParkAustralia81
Steve JohnsonAustria69
Margaret ParkBelgium60
Jane PeacockBrazil190
Margaret ParkBrazil166
Steve JohnsonBrazil69
Jane PeacockCanada371
Steve JohnsonCanada91
Margaret ParkCanada70
Steve JohnsonChile97
Margaret ParkCzech Republic144
Steve JohnsonCzech Republic128
Margaret ParkDenmark37
Jane PeacockFinland79
Jane PeacockFrance172
Margaret ParkFrance151
Steve JohnsonFrance64
Steve JohnsonGermany167
Jane PeacockGermany167
Jane PeacockHungary78
Jane PeacockIndia182
Jane PeacockIreland114
Steve JohnsonItaly50
Steve JohnsonNetherlands65
Margaret ParkNorway72
Margaret ParkPoland76
Margaret ParkPortugal184
Steve JohnsonSpain98
Steve JohnsonSweden75
Margaret ParkUSA497
Steve JohnsonUSA337
Jane PeacockUSA204
Jane PeacockUnited Kingdom166
Steve JohnsonUnited Kingdom79
" ], "text/plain": [ "[('Margaret Park', 'Argentina', 39),\n", " ('Margaret Park', 'Australia', 81),\n", " ('Steve Johnson', 'Austria', 69),\n", " ('Margaret Park', 'Belgium', 60),\n", " ('Jane Peacock', 'Brazil', 190),\n", " ('Margaret Park', 'Brazil', 166),\n", " ('Steve Johnson', 'Brazil', 69),\n", " ('Jane Peacock', 'Canada', 371),\n", " ('Steve Johnson', 'Canada', 91),\n", " ('Margaret Park', 'Canada', 70),\n", " ('Steve Johnson', 'Chile', 97),\n", " ('Margaret Park', 'Czech Republic', 144),\n", " ('Steve Johnson', 'Czech Republic', 128),\n", " ('Margaret Park', 'Denmark', 37),\n", " ('Jane Peacock', 'Finland', 79),\n", " ('Jane Peacock', 'France', 172),\n", " ('Margaret Park', 'France', 151),\n", " ('Steve Johnson', 'France', 64),\n", " ('Steve Johnson', 'Germany', 167),\n", " ('Jane Peacock', 'Germany', 167),\n", " ('Jane Peacock', 'Hungary', 78),\n", " ('Jane Peacock', 'India', 182),\n", " ('Jane Peacock', 'Ireland', 114),\n", " ('Steve Johnson', 'Italy', 50),\n", " ('Steve Johnson', 'Netherlands', 65),\n", " ('Margaret Park', 'Norway', 72),\n", " ('Margaret Park', 'Poland', 76),\n", " ('Margaret Park', 'Portugal', 184),\n", " ('Steve Johnson', 'Spain', 98),\n", " ('Steve Johnson', 'Sweden', 75),\n", " ('Margaret Park', 'USA', 497),\n", " ('Steve Johnson', 'USA', 337),\n", " ('Jane Peacock', 'USA', 204),\n", " ('Jane Peacock', 'United Kingdom', 166),\n", " ('Steve Johnson', 'United Kingdom', 79)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "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", " )\n", "SELECT\n", " e.first_name || \" \" || e.last_name \"Sales Support Agent\",\n", " c.country \"Country\",\n", " SUM(cust_totals.cust_total) \"Total Sales\"\n", "FROM customer c\n", "LEFT JOIN cust_totals ON cust_totals.customer_id = c.customer_id\n", "LEFT JOIN employee e ON e.employee_id = c.support_rep_id\n", "GROUP BY 2, 1\n", "ORDER BY 2, 3 DESC" ] }, { "cell_type": "markdown", "id": "490db314", "metadata": {}, "source": [ "**Sales Support Agent Performance Conclusion**\n", "* 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. \n", "* Steve Johnson, 3rd place, has the same average sale as Margaret Park in 2nd place, but she has more customers.\n", "* 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.\n", "* 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." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.7" } }, "nbformat": 4, "nbformat_minor": 5 }