In this project, we'll be answering questions about a fictional digital music shop - kind of like a mini-iTunes store.
As this is mostly SQL practice, we'll attempt to find answers to the following:
We'll be using a modified version of the Chinook database. The Chinook database contains information about the artists, songs, and albums from the music shop, as well as information on the shop's employees, customers, and the customers purchases.
Let's connect to the database and view the tables:
While we have 11 distinct tables, they're all interconnected in some way. For example, an
invoice can have many
invoice lines, while an
invoice line can only refer to one
Let's start our analysis with finding the best selling genres in the USA.
This highlights that Rock, Alternative & Punk, and Metal are the top selling USA genres from our dataset.
Each customer for the Chinook store gets assigned a sales support agent when they first make a purchase. We'll analyse this connection to identify the sales support agents who are performing the best.
We can see that Jane Peacock is the best performing Sales Support Agent. However, this may be due to the fact that she has worked at the company for the longest period of time.
Here, we'll calculate the following for each country:
Where a country has only one customer, we'll ignore them.
While USA has the highest number of customers, sales and average sales, Czech Republic has the highest average order value.
Let's find out what percentages of purchases comes from albums versus individual tracks
We can note that album purchases account for just 18.6% of purchases, meaning that listeners prefer to buy tracks in isolation.
From our analysis, we've learned the following: