This project's primary objective is to conduct a thorough analysis of music store data using SQL. The overarching goal of this project is to extract valuable insights and address a multitude of inquiries related to the music store's functioning through the application of SQL queries to the dataset.
The "Music Store Data Analysis" project offers a comprehensive analysis of the music store's data to facilitate better decision-making, identify trends, and understand customer behavior. By leveraging SQL queries and data exploration, this project provides valuable answers to optimize inventory management, target marketing campaigns, and make informed business decisions.
PostgreSQL and pgAdmin4
- Use of SELECT and WHERE statements along with LIMIT and ORDER BY
- Use of Aggreagte Functions such as SUM, COUNT
- Use of GROUP BY and JOINS
- Use of WINDOW Functions and Common Table Expression(CTE)
The dataset for this project has 11 tables: Employee, Customer, Invoice, InvoiceLine, Track, MediaType, Genre, Album, Artist, PlaylistTrack, and Playlist, as well as their associations.
![schema_diagram](https://private-user-images.githubusercontent.com/142779836/268498510-820c6cdc-0976-4479-bdf0-bc612df237a6.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTg5ODMzMDQsIm5iZiI6MTcxODk4MzAwNCwicGF0aCI6Ii8xNDI3Nzk4MzYvMjY4NDk4NTEwLTgyMGM2Y2RjLTA5NzYtNDQ3OS1iZGYwLWJjNjEyZGYyMzdhNi5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwNjIxJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDYyMVQxNTE2NDRaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0xODc0MmVjMzJkN2Y5MzRhZjY0ZDQ3NTNhNmQxNzY2YWI0ZDA3MjBhNzIyMjdmNjEzNTUyODcyZDdmZmNkZWU5JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.BQAbwdual2rRGmed_eqLqVEm0SdJD-ItiGoLxwx_VrA)
Set 1 Questions - Easy
➥
Output :
➥
Output :
➥
Output :
➥
Output :
➥
Output :
Set 2 Question - Moderate
➥
Output :
➥
Output :
➥
Output :
Set 3 Questions - Advance
➥
Output :
➥
Output :
➥
Output :
Identifying Valuable Clients: Identifying Valuable Clients: Through a thorough analysis of customer purchase records and spending habits, we've pinpointed our most valuable clients based on their lifetime contribution and purchase frequency. This valuable data can be harnessed to craft tailored marketing initiatives, loyalty programs, and exclusive promotions aimed at deepening connections and preserving these cherished customer relationships.
Country-Based Revenue Insights: By consolidating sales data and examining customer locations, we've identified the countries that generate the highest revenue for our digital music store. This valuable insight allows us to direct our marketing campaigns, localization efforts, and customer support resources towards these specific regions, ensuring we tap into their full revenue potential.
Identifying Preferred Music Genres: Through an analysis of customer preferences and purchasing behaviors, we've pinpointed the most favored music genres within our customer community. This valuable data serves as a guide for our content curation strategy, promotional initiatives, and collaborations with artists from these genres, allowing us to effectively meet customer demands and preferences.
Thank you for your interest and time. Feel free to give your valuable suggestions and connect with me on https://www.linkedin.com/in/harshitt-gahlaut/