Music Store Data Analysis

Unveiling Insights through SQL Queries

Project Overview

In this SQL project, I undertook a comprehensive analysis of a music store dataset using advanced SQL techniques. The project encompassed three tiers of difficulty, demonstrating my ability to formulate complex queries, extract meaningful insights, and visualize data trends. I navigated through the dataset to answer various business-related questions, providing valuable information for decision-making.
‍

My Contributions

Starting with straightforward tasks such as identifying top customers and popular genres, I progressed to more intricate challenges like calculating customer spending per artist and determining the best customers per country. I skillfully employed SQL subqueries, joins, aggregation, window functions, and common table expressions to create a robust analytical framework. Additionally, I showcased my proficiency in data transformation, conditional filtering, and result ordering.
The project culminated in a collection of SQL queries that unveil insights into customer behavior, revenue generation, music preferences, and more. The queries are organized in a structured manner, addressing distinct aspects of the dataset and delivering actionable business intelligence. This project not only demonstrates my expertise in SQL but also showcases my analytical thinking and problem-solving abilities, making it a valuable addition to my portfolio.

1) Subqueries:

Have used subqueries in various places to extract relevant data and perform calculations within the context of the larger query.
Examples include finding the maximum reports to value for determining the senior most employee, selecting track IDs for rock music listeners, and calculating the average song length.

2) Join Operations:

Skillfully employed different types of joins (INNER JOINs) to combine data from multiple tables based on matching criteria.
JOINs were used to associate customers with invoices, track information with genres, artists with albums, and more.

3) Grouping and Aggregation:

Have used GROUP BY clauses to aggregate data based on specific columns, allowing you to perform calculations like calculating total spend for each customer or artist.
Aggregation functions such as SUM and COUNT were used to calculate totals and counts.

4) Common Table Expressions (CTEs):

Utilized CTEs to create named result sets that you can reference multiple times within the same query.
CTEs were used for calculations involving sales, track information, and identifying top genres

5) Window Functions:

Used window functions (ROW_NUMBER) in combination with PARTITION BY clauses to rank and order results based on specific criteria, such as finding the top-spending customers per country.

6) Ordering and Limiting Results:

Ordered query results using the ORDER BY clause, often in descending order, to present relevant information first.
The LIMIT clause is used to restrict the number of returned rows, such as retrieving the top 3 invoice values.

7) Conditional Filtering:

Employed WHERE clauses to filter data based on specific conditions, such as selecting rock music listeners or tracks with song lengths above the average in an sub-query.

8) Alias Usage and Column Renaming:

Used aliases to provide more meaningful names for columns or to simplify referencing tables.
Alias usage can be seen in scenarios like renaming columns in the SELECT clause or aliasing tables for easier reference.

9) Alias Usage and Column Renaming:

Queries show familiarity with common SQL patterns like identifying maximum values, calculating totals, ranking results, and grouping data.

10) Alias Usage and Column Renaming:

Performed calculations, such as multiplying unit price by quantity to calculate the total spent.
Calculated fields were then used for aggregation and analysis.

Task Set 1 - Easy:

Determine the senior most employee based on their job title.
Identify the countries with the highest number of invoices.
Retrieve the top 3 values of the total invoice amounts.
Find the city with the best customers, considering the city with the highest sum of invoice totals. Return both the city name and the sum of invoice totals.
Identify the best customer, defined as the individual who has spent the most money. Retrieve the person who holds this title.

Task Set 2 – Moderate:

Retrieve the email, first name, last name, and genre of all listeners who enjoy Rock Music. Order the list alphabetically by email, starting with "A".
Determine the artists who have written the most rock music tracks in the dataset. Provide the artist name and the total count of rock music tracks for the top 10 bands
Retrieve the track names that have a song length longer than the average song length. Display the track name and its duration in milliseconds, ordering the results with the longest songs listed first.

Task Set 3 – Advanced:

Calculate the total amount spent by each customer on artists. Display the customer name, artist name, and the total amount spent.
Identify the most popular music genre for each country, considering the genre with the highest number of purchases. Provide the country along with the top genre. For countries where multiple genres share the maximum number of purchases, include all of them
Determine the customer who has spent the most on music for each country. Display the country, the top-spending customer, and the amount they spent. For countries where multiple customers share the top amount spent, include all of them.