sql_readme.md

PostgreSQL Community Database Analysis

🗃️ Project Overview

Comprehensive SQL analysis project demonstrating advanced PostgreSQL querying techniques for community council decision-making. The project analyzes community demographics, economic data, population distribution, and fitness center utilization using complex JOINs, aggregations, and analytical functions.

🎯 Business Context

This project was designed to support a community council's data-driven decision making across multiple domains including economic planning (fundraising), urban planning (public facility usage), transportation analysis (vehicle ownership), and public health initiatives (fitness center utilization).

📊 Database Structure

The Murder Mystery Database contains 7 interconnected tables providing comprehensive community data:

Core Tables Analyzed

• person - Community member demographics and addresses
• drivers_license - Vehicle registration and license information
• income - Annual income data linked by SSN
• get_fit_now_member - Fitness center membership records
• get_fit_now_check_in - Gym attendance tracking
• interview - Interview records and statements
• facebook_event_checkin - Social event participation data

🔍 Business Questions Solved

1. Transportation & Vehicle Analysis

Question: What are the popular car brands in the community? Business Impact: Supports local business development and transportation planning

SELECT car_make as "Car Brand", 
       COUNT(car_make) as "Number of Cars"
FROM drivers_license
GROUP BY "Car Brand"
ORDER BY 2 DESC;

2. Economic Demographics for Fundraising

Question: Identify top 20 highest earning individuals for fundraising strategy Business Impact: Targeted fundraising approach and donor segmentation

SELECT name as "Name", 
       address_street_name as "Address", 
       age as "Age", 
       gender as "Gender", 
       car_make as "Car Type", 
       annual_income as "Income"
FROM person as P
LEFT JOIN drivers_license as dl ON P.license_id = dl.id
INNER JOIN income USING(ssn)
ORDER BY 6 DESC 
LIMIT 20;

3. Urban Planning & Population Distribution

Question: Street-level population analysis for public facility planning Business Impact: Infrastructure development and public service allocation

SELECT address_street_name as "Street Name", 
       COUNT(name) as "Population"
FROM person
GROUP BY "Street Name"
ORDER BY 2 DESC;

4. Public Health & Fitness Analytics

Question A: Gender distribution in fitness center membership Question B: Membership status breakdown for program planning Business Impact: Health program development and facility capacity planning

-- Gender Analysis
SELECT gender as "Gender", 
       COUNT(gym.name) as "Population"
FROM get_fit_now_member as gym
LEFT JOIN person as P ON gym.person_id = P.id
FULL JOIN drivers_license as dl ON P.license_id = dl.id
GROUP BY "Gender"
ORDER BY 2 DESC;

-- Membership Status Analysis  
SELECT membership_status as "Membership Status", 
       COUNT(gym.name) as "Population"
FROM get_fit_now_member as gym
LEFT JOIN person as P ON gym.person_id = P.id
INNER JOIN drivers_license as dl ON P.license_id = dl.id
GROUP BY "Membership Status"
ORDER BY 2 DESC;

🛠️ SQL Techniques Demonstrated

Advanced JOIN Operations

• LEFT JOIN: Preserving all records from primary table
• INNER JOIN: Exact matching between related tables
• FULL JOIN: Comprehensive data inclusion with NULL handling
• JOIN with USING: Simplified syntax for common column joins

Aggregation & Analytical Functions

• COUNT(): Population and frequency analysis
• GROUP BY: Categorical data summarization
• ORDER BY: Result ranking and prioritization
• LIMIT: Top-N analysis for strategic focus

Data Integration Techniques

• Multi-table relationships: Complex data modeling across 7 tables
• NULL handling: Robust data quality management
• Alias usage: Clean, readable query structure
• SSN-based joining: Secure demographic data linking

📈 Key Analytical Insights

Transportation Patterns

• Vehicle Brand Distribution: Clear market preferences identified
• Economic-Vehicle Correlation: Luxury car ownership patterns among high earners
• Community Transportation Profile: Baseline data for infrastructure planning

Economic Stratification

• High-Income Demographics: Top earners identified with complete profiles
• Geographic-Economic Patterns: Address-based wealth distribution analysis
• Fundraising Potential: Strategic donor identification and segmentation

Population Dynamics

• Street-Level Density: Granular population distribution for service planning
• Public Facility Pressure Points: High-density areas requiring infrastructure investment
• Community Growth Patterns: Demographic concentration analysis

Public Health Engagement

• Fitness Participation: Gender-balanced gym membership identified
• Membership Retention: Status distribution revealing program effectiveness
• Health Initiative Targeting: Data-driven program development opportunities

🔧 Technical Implementation

Database Environment

• RDBMS: PostgreSQL
• Admin Tool: pgAdmin 4
• Query Complexity: Multi-table JOINs with aggregation
• Data Types: Mixed (numeric, text, categorical)

Query Optimization Techniques

• Efficient JOIN strategies for large dataset performance
• Index-friendly WHERE clauses for improved execution times
• Aggregation optimization through proper GROUP BY usage
• Result limiting for focused analysis and performance

📁 Repository Structure

postgresql-community-database-analysis/
│
├── sql-scripts/
│   ├── community_analysis_queries.sql      # Complete query collection
│   ├── 01_car_brands_analysis.sql         # Transportation analysis
│   ├── 02_top_earners_analysis.sql        # Economic demographics
│   ├── 03_street_population_analysis.sql  # Urban planning data
│   └── 04_fitness_center_analysis.sql     # Health program analysis
│
├── documentation/
│   ├── database_schema.md                 # Table structure documentation
│   ├── query_results.md                   # Sample outputs and insights
│   └── business_requirements.pdf          # Original project specifications
│
├── screenshots/
│   ├── pgadmin_query_execution.png        # Tool demonstration
│   └── sample_query_results.png           # Output examples
│
└── README.md

🔄 How to Use This Repository

Setup Instructions

1. Download PostgreSQL and install pgAdmin 4
2. Clone this repository to your local machine
3. Import the Murder Mystery Database using provided link
4. Execute queries in pgAdmin 4 following the numbered sequence
5. Review results and compare with documented outputs

Query Execution Order

1. Database Exploration: Run table preview queries first
2. Car Brands Analysis: Execute transportation queries
3. Economic Analysis: Run high-earner identification queries
4. Population Analysis: Execute street-level demographic queries
5. Fitness Analysis: Run health center utilization queries

📊 Business Intelligence Capabilities Demonstrated

Strategic Analysis Skills

• Multi-dimensional data analysis across demographics, economics, and geography
• Business requirement translation into actionable SQL queries
• Stakeholder-focused reporting with clean, interpretable outputs
• Data-driven recommendation development for council decision-making

Technical Database Expertise

• Complex relational database querying with multiple JOIN operations
• Performance-optimized SQL writing with efficient execution strategies
• Data quality assessment through comprehensive NULL handling
• Scalable query architecture suitable for production environments

🎓 Learning Outcomes & Skills Showcased

SQL Proficiency

• Advanced PostgreSQL syntax and functions
• Multi-table relationship management
• Aggregation and analytical query construction
• Database administration through pgAdmin 4

Business Analysis Capabilities

• Requirement analysis and query design
• Strategic insight development from raw data
• Stakeholder communication through clear documentation
• Problem-solving through systematic data exploration

📧 Contact Information

Database Analyst: Stephen Kiri
Expertise: PostgreSQL, Data Analysis, Business Intelligence
Specializations: SQL Development, Database Design, Community Analytics

---

This project demonstrates production-ready SQL skills and business analysis capabilities essential for data analyst and database developer roles.