Project-4

Music Store Analysis || PostgreSQL

Objective:

The primary goal of this analysis was to extract valuable insights from the Music Store Database by answering a series of SQL-based questions. The questions spanned various levels of complexity, requiring a deep understanding of SQL concepts and the ability to manipulate data effectively.

Key Questions Answered:

• Question Set 1 Basic:

  Q1. Who is the senior most employee based on job title?
  Q2. Which countries have the most Invoices?
  Q3. What are top 3 values of total invoice?
  Q4. Which city has the best customers? We would like to throw a promotional Music Festival in the city we made the most money. Write a query that returns one city that has the highest sum of invoice totals. Return both the city name & sum of all invoice totals.
  Q5. Who is the best customer? The customer who has spent the most money will be declared the best customer. Write a query that returns the person who has spent the most money.

• Question Set 2 Medium:

  Q1. Write query to return the email, first name, last name, & Genre of all Rock Music listeners. Return your list ordered alphabetically by email starting with A.
  Q2. Let's invite the artists who have written the most rock music in our dataset. Write a query that returns the Artist name and total track count of the top 10 rock bands.
  Q3. Return all the track names that have a song length longer than the average song length.Return the Name and Milliseconds for each track. Order by the song length with the longest songs listed first.
  

• Question Set 3 Hard :

  Q1. Find how much amount spent by each customer on artists? Write a query to return customer name, artist name and total spent.
  Q2. We want to find out the most popular music Genre for each country. We determine the most popular genre as the genre with the highest amount of purchases. 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.
  Q3. Write a query that determines the customer that has spent the most on music for each country. Write a query that returns the country along with the top customer and how much they spent. For countries where the top amount spent is shared, provide all customers who spent this amount
  

• Technologies Used:

  Database: PostgreSQL
  Query Execution Tool: PgAdmin4
  

• Skills Demonstrated:

  Proficient use of SQL for complex data analysis.
  Understanding and application of relational database concepts.
  Utilization of PostgreSQL and PgAdmin4 for effective database management and querying.
  

• Database Schema :

  

  The database schema depicts seamless interconnections through foreign key relationships, facilitating insightful data analysis. By joining multiple tables, it enables the resolution of diverse problem statements, ensuring comprehensive understanding and decision-making. Used: https://drawsql.app/ to create database schema's visual representation

• Conclusion:

  This project showcases my ability to leverage SQL and relational databases to extract meaningful insights from interconnected datasets. The use of PostgreSQL and PgAdmin4 underscores my proficiency in database management tools, making this project a valuable addition to my portfolio. For more details please visit my GitHub profile.