yogeshkasar778 / digital_music_store_data_analysis_using_sql

Digital Music Store Data Analysis Using SQL

Project Overview:

In this project, we shall analyze and answer a few business questions or tasks regarding an online music store. With the help of SQL, the analysis will be conducted on the dataset and help the store understand the business questions they had put forward in order to meet their requirements.

Data sources:

Album data: The primary dataset used for this analysis is the Album file, containing detailed information about each Album made by the company.

Artist data: The primary dataset used for this analysis is the Artist file, containing detailed information about each Artist made by the company.

Customer data: The primary dataset used for this analysis is the Customer file, containing detailed information about each Customer made by the company.

Employees data: The primary dataset used for this analysis is the Employees file, containing detailed information about each Employee made by the company.

genre data: The primary dataset used for this analysis is the genre file, containing detailed information about each genre made by the company.

Invoice data: The primary dataset used for this analysis is the Invoice file, containing detailed information about each Invoice made by the company.

Invoice_line data: The primary dataset used for this analysis is the Invoice_line file, containing detailed information about each Invoice_line made by the company.

Media_type data: The primary dataset used for this analysis is the Media_type file, containing detailed information about each Media_type made by the company.

Playlist data: The primary dataset used for this analysis is the Playlist file, containing detailed information about each Playlist made by the company.

Playlist_track data: The primary dataset used for this analysis is the Playlist_track file, containing detailed information about each Playlist_track made by the company.

Track data: The primary dataset used for this analysis is the Track file, containing detailed information about each Track made by the company.

Database and Tools used:

• MS SQL Server
• Microsoft SQL Server Management Studio

Digital Music Store Database relationship Schema diagram:

Data Analysis Questions:

1. Which countries have the most invoices?
2. What are the top 3 values of total invoices?
3. Which city has the best customers? (The company would like to organize a promotional Music Festival in the city where they made the most money)
4. Who is the best customer? (The customer who has spent the most money will be declared the best customer)
5. What’s the name and email ID of the listeners who enjoy Rock Music?
6. Let's invite the artists who have written the most rock music in our dataset. Who is it?
7. Which tracks in the dataset have a song length more than the average song length, and what’s their duration?
8. Which tracks in the dataset have a song length more than the average song length, and what’s their duration?
9. Which is the most popular music genre for each country? (Write a query that returns each country along with the top genre. For countries where the maximum number of purchases is shared, return all genres)
10. Determine the customer who has spent the most on music for each country. (Write a query that returns the country name, the top customer’s name, and how much they spent. For countries where the top amount spent is shared, provide all the customers’ names who spent this amount.)