By Alifia Ganjaraharja
This repository contains a data analysis project using SQL to query the classic Chinook database. The project aims to answer business-related questions about a digital music store, making it excellent for practicing fundamental and intermediate SQL concepts.
This is a classic project using the Chinook database, which mimics a digital music store like iTunes. It's perfect for people who are practicing fundamental concepts, such as joins and aggregations.
-
Goals: Act as a data analyst for the music store and provide insights to the marketing team.
Sample Questions to Answer:
- Top Markets: Which countries have the most invoices? This will help the marketing team decide where to focus their campaigns.
- Top Artists: Who are the top 10 best-selling artists?
- Genre Popularity: Which music genre is the most popular in the USA? Analyze sales by genre to inform purchasing decisions.
- Customer Value: Who is our best customer? Identify the customer who has spent the most money.
-
Dataset: The dataset used for this project was downloaded from the SQLite Tutorial website. You can download the full dataset here or find it within this repository under the file name 'chinook.db' (This dataset is available for SQL Server, MySQL, and more.). The dataset includes about 11 tables with diverse information for each table:
- albums
- artists
- customers
- employees
- genres
- invoice_items
- invoices
- media_types
- playlist_track
- playlists
- tracks
For more detailed information about columns from each table and the data type, please go here
-
Tools Used: SQLite and Looker
Question 1: Which countries have the most invoices?
| Country | Total Invoicies |
|---|---|
| USA | 19103 |
| Canada | 11963 |
| Brazil | 7399 |
| France | 7168 |
| Germany | 4697 |
| United Kingdom | 4382 |
| Czech Republic | 3143 |
| Portugal | 2870 |
| India | 2758 |
| Finland | 1757 |
| See visualization here |
From the total of 412 countries, these are the top 10 countries with the most invoices. The USA is the country with the most invoices, totaling 19,103.
Question 2: Who are the top 10 best-selling artists?
| Artists Name | Total Revenue |
|---|---|
| Iron Maiden | 1233.54 |
| U2 | 895.59 |
| Lost | 833.7 |
| Led Zeppelin | 620.73 |
| Metallica | 599.94 |
| Deep Purple | 550.44 |
| Pearl Jam | 408/87 |
| Lenny Kravitz | 372.51 |
| Van Halen | 336.82 |
| The Office | 328.8 |
| See visualization here |
From the total of 275 artists, these are the top 10 best-selling artists. Iron Maiden is the artist with the most revenue in total $1233.54
Question 3: Which music genre is the most popular in the USA?
From a total of 25 music genres, the most popular in the USA is the Rock genre with total sales $1526.14.
Question 4: Who is our best customer?
From the total of 59 customers, the best customer who had spent the most money is Helena Holý with a total spent of $49.62