Stadium SQL Analytics

This project is a standalone SQL-focused project designed to demonstrate advanced SQL skills, relational database design, and data modeling.

The schema is based on a simplified stadium management system, covering ticket sales, event management, gate entry tracking, and concession sales.

Repository Structure

• /queries - Collection of SQL queries demonstrating various analysis capabilities
• /schema.sql - Database schema definition
• /indexes.sql - Performance optimization indexes
• /seed.sql - Sample data generation

Schema Overview

The database models a stadium management system with five core tables. Each table serves a specific purpose and includes constraints to ensure data integrity.

Core Tables

Users

• Primary user information and loyalty tracking
• Fields: id, name, email, phone_number, loyalty_points
• Constraints: unique email, non-negative loyalty points

Events

• Stadium event details
• Fields: id, name, event_date, event_type
• Constraints: future dates only

Tickets

• Ticket sales and seat assignments
• Fields: id, user_id, event_id, seat_number, price, purchased_at
• Constraints: unique seats per event, non-negative prices
• Format: Seat numbers follow S01R01 pattern (Section 01, Row 01)

ConcessionTransactions

• Food and beverage purchases
• Fields: id, user_id, event_id, item_name, quantity, price_each, transaction_time
• Constraints: positive quantities, non-negative prices

GateEntries

• Stadium entry tracking
• Fields: id, user_id, event_id, gate_number, entry_time
• Constraints: valid gate numbers (1-100), no duplicate scans

Relationships

erDiagram
    Users ||--o{ Tickets : purchases
    Users ||--o{ ConcessionTransactions : makes
    Users ||--o{ GateEntries : enters
    Events ||--o{ Tickets : has
    Events ||--o{ ConcessionTransactions : hosts
    Events ||--o{ GateEntries : records

Loading

Key Constraints

1. Referential Integrity

   • All foreign keys properly constrained
   • Cascading rules prevent orphaned records

2. Data Validation

   • Non-negative monetary values
   • Future dates for events
   • Valid gate numbers
   • Unique seat assignments

3. Uniqueness Rules

   • No duplicate email addresses
   • No double-booked seats
   • No duplicate gate entries

For complete technical details, see schema.sql.

Technical Highlights

• Advanced SQL concepts (CTEs, window functions, complex joins)
• Strategic indexing for performance
• Data integrity constraints
• Realistic data modeling
• Comprehensive documentation

Query Examples

The /queries directory contains a variety of SQL queries demonstrating:

• Revenue analysis
• Customer behavior patterns
• Operational metrics
• Performance optimization

See the Queries README for detailed explanations of each query.

Database Setup

Prerequisites

• PostgreSQL 15+
• psql client (optional, for direct database access)

Schema Setup

1. Create database:

CREATE DATABASE stadium_db;

2. Load schema:

psql -d stadium_db -f schema.sql

3. Load indexes:

psql -d stadium_db -f indexes.sql

4. Load sample data:

psql -d stadium_db -f seed.sql

Notes

This project is designed as a demonstration of SQL expertise, focusing on:

• Query writing and optimization
• Database design
• Data modeling
• Performance considerations

For detailed analysis of the queries and technical concepts demonstrated, please see the Queries README.

---

Why This Project?

SQL is an area I wanted to strengthen further. I built this project to practice and demonstrate my ability to quickly learn and apply advanced SQL patterns and data modeling best practices. In addition to improving my SQL fluency, this project also reinforces professional best practices in query design, data integrity, performance tuning, and clear documentation — all important for contributing to backend systems at scale.

---

Getting Started

Prerequisites

• Docker
• Docker Compose
• Make (optional, for using Makefile commands)

Setup & Running

Option 1: Using Makefile (Recommended)

# Start the database
make start

# Initialize schema and data
make init

# Other useful commands:
make psql      # Connect to psql console
make logs      # View container logs
make stop      # Stop the container
make restart   # Restart the container
make clean     # Remove the container
make reset     # Full reset: remove container and start fresh

Option 2: Manual Docker Commands

1. Start the PostgreSQL container:

docker run --name stadium-db -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=stadium_db -p 5432:5432 -d postgres:15

2. Initialize the database:

# Load schema
docker exec -i stadium-db psql -U postgres -d stadium_db < schema.sql

# Load seed data
docker exec -i stadium-db psql -U postgres -d stadium_db < seed.sql

# Apply indexes
docker exec -i stadium-db psql -U postgres -d stadium_db < indexes.sql

Useful Commands

# Connect to psql console
docker exec -it stadium-db psql -U postgres -d stadium_db

# View logs
docker logs stadium-db

# Stop container
docker stop stadium-db

# Remove container
docker rm stadium-db

# Restart container
docker restart stadium-db

Database Connection Details

• Host: localhost
• Port: 5433
• Database: stadium_db
• Username: postgres
• Password: postgres

You can connect using any PostgreSQL client with these credentials.