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

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

Query Execution Order

Database Exploration: Run table preview queries first
Car Brands Analysis: Execute transportation queries
Economic Analysis: Run high-earner identification queries
Population Analysis: Execute street-level demographic queries
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.

stephenkiri/postgresql-community-database-analysis