SoundPulse Advanced SQL & Query Optimization Project

Project Overview

This project performs end-to-end data analysis and SQL query optimization on an audio streaming dataset. It focuses on writing complex multi-join queries, aggregating stream metrics, and optimizing query performance using database indexes and execution plan analyses.

---

Key Features & Objectives

• Complex Data Extraction: Analyzing track engagement, artist performance, and album statistics using advanced SQL (GROUP BY, HAVING, CTEs, and Window Functions).
• Performance Tuning: Benchmarking query execution times before and after implementing targeted table indexes.
• Data Cleaning & Preprocessing: Handling missing values and ensuring type consistency across metrics.

---

Optimization Benchmark

Query execution times were benchmarked before and after applying indexes to primary foreign keys and filter columns.

Metric / Query Type	Execution Time (Before Index)	Execution Time (After Index)	Performance Gain
Track Engagement Join	e.g., 120 ms	e.g., 15 ms	8x Faster
Artist Aggregation Query	e.g., 340 ms	e.g., 42 ms	8.1x Faster

---

Repository Structure

├── cleaned_dataset.csv     # Preprocessed data used for database import
├── queries.sql             # Advanced SQL analytical scripts
├── before_index.png        # Execution plan visual (Before optimization)
├── after_index.png         # Execution plan visual (After optimization)
└── README.md               # Project documentation

Tech Stack & Tools

• Database Engine: PostgreSQL
• SQL Capabilities: DDL, DML, Multi-table Joins, CTEs, Window Functions, Query Optimization (EXPLAIN ANALYZE)
• Database Management: pgAdmin 4 / DBeaver
• Environment: Docker / Local PostgreSQL instance

Setup & Installation Guide

Follow these steps to set up the database, load the data, and run the analytical scripts.

Prerequisites

• PostgreSQL (v12 or higher) installed locally or via Docker
• pgAdmin 4, DBeaver, or the psql command-line tool

---

Step-by-Step Execution

1. Clone the Repository

   git clone [https://github.com/your-username/soundpulse-sql-analytics.git](https://github.com/your-username/soundpulse-sql-analytics.git)
   cd soundpulse-sql-analytics

2. Initialize the Database Connect to PostgreSQL using your preferred interface (psql or pgAdmin) and create a new database:

   CREATE DATABASE soundpulse_db;
   

3.Build the Schema Run the schema creation script to set up tables, primary keys, and initial constraints:

psql -d soundpulse_db -f schema.sql

4.Import the Dataset Import cleaned_dataset.csv into your created tables:

\copy tracks FROM 'cleaned_dataset.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

Using pgAdmin 4: Right-click your target table -> select Import/Export Data -> choose cleaned_dataset.csv -> enable Header and select UTF8.

5.Execute Analytical Queries & Benchmarks Run the core analytical queries:

psql -d soundpulse_db -f queries.sql

Compare performance before and after indexing by running execution plans:

EXPLAIN ANALYZE SELECT * FROM tracks WHERE artist_id = 101;