NHL Stats API

REST API for NHL statistics analysis built with Python, FastAPI, PostgreSQL, and Docker.

Quick Setup

Prerequisites

Docker Desktop installed and running

Start the API

# 1. Start container
docker compose up -d --build

# 2. Wait 30 seconds for database initialization
sleep 30

# 3. Upload data
curl -X POST "http://localhost:8000/upload-csv/" \
  -F "file=@./data/data_dump.csv"

# 4. Verify
curl "http://localhost:8000/health"

API Documentation: http://localhost:8000/docs

API Endpoints

POST /upload-csv/

Upload NHL statistics CSV file to database.

curl -X POST "http://localhost:8000/upload-csv/" \
  -F "file=@./data/data_dump.csv"

Response:

{
  "message": "Successfully uploaded 951 records",
  "columns": ["player_id", "player", "team", "position", "games_played", "goals", "assists", "points", "penalty_minutes", "shots", "shooting_pct", "season"],
  "sample_data": [
    {
      "player_id": 8481720,
      "player": "Nick Abruzzese",
      "team": "TOR",
      "goals": 0,
      "games_played": 2
    }
  ]
}

GET /top-scorers

Players with most goals in specified season.

curl "http://localhost:8000/top-scorers?season=2022-2023&limit=5"

Response:

[
  {
    "player": "Connor McDavid",
    "team": "EDM",
    "goals": 64,
    "games_played": 82,
    "goals_per_game": 0.78
  },
  {
    "player": "David Pastrnak",
    "team": "BOS",
    "goals": 61,
    "games_played": 82,
    "goals_per_game": 0.74
  },
  {
    "player": "Mikko Rantanen",
    "team": "COL",
    "goals": 55,
    "games_played": 82,
    "goals_per_game": 0.67
  }
]

GET /penalty-leaders

Players with most penalty minutes.

curl "http://localhost:8000/penalty-leaders?season=2022-2023&limit=5"

Response:

[
  {
    "player": "Arber Xhekaj",
    "team": "MTL",
    "penalty_minutes": 81,
    "games_played": 44,
    "pim_per_game": 1.84
  },
  {
    "player": "Matt Rempe",
    "team": "NYR",
    "penalty_minutes": 71,
    "games_played": 17,
    "pim_per_game": 4.18
  }
]

GET /team-stats

Team aggregated statistics (goals and shots).

curl "http://localhost:8000/team-stats?season=2022-2023"

Response:

[
  {
    "team": "ANA",
    "total_goals": 219,
    "total_shots": 2467,
    "total_goals_without_multi_team_players": 188,
    "total_shots_without_multi_team_players": 2111
  },
  {
    "team": "ARI",
    "total_goals": 251,
    "total_shots": 2352,
    "total_goals_without_multi_team_players": 183,
    "total_shots_without_multi_team_players": 1582
  }
]

GET /team-changes

Players who changed teams during the season.

curl "http://localhost:8000/team-changes?season=2022-2023&limit=5"

Response:

[
  {
    "player": "Vladimir Tarasenko",
    "teams": "NYR,OTT,STL",
    "team_count": 3
  },
  {
    "player": "Noel Acciari",
    "teams": "STL,TOR",
    "team_count": 2
  },
  {
    "player": "Tanner Jeannot",
    "teams": "NSH,TBL",
    "team_count": 2
  }
]

GET /team-roster/{team}

Active roster for a specific team.

curl "http://localhost:8000/team-roster/TOR?season=2022-2023"

Response:

[
  {
    "player": "Auston Matthews",
    "position": "C",
    "goals": 40,
    "assists": 46,
    "points": 86,
    "games_played": 74,
    "shots": 348
  },
  {
    "player": "William Nylander",
    "position": "R",
    "goals": 40,
    "assists": 47,
    "points": 87,
    "games_played": 82,
    "shots": 299
  },
  {
    "player": "Mitchell Marner",
    "position": "R",
    "goals": 29,
    "assists": 70,
    "points": 99,
    "games_played": 82,
    "shots": 234
  }
]

All endpoints accept optional ?season=2022-2023 parameter (defaults to 2022-2023).

Architecture

Tech Stack: FastAPI, PostgreSQL, Docker, SQLAlchemy, Pandas

Key Decisions:

SQL queries separated into sql_queries/*.sql files for maintainability
PostgreSQL for robust statistical aggregations
Single-table schema for MVP simplicity
Dynamic season parameter for multi-season analysis

Data Processing:

Cleans and normalizes CSV column names
Handles NULL values and multi-team players
Converts season format: 20222023 → 2022-2023

Known Limitations

Team Statistics Accuracy: The /team-stats endpoint excludes players who changed teams mid-season to avoid double-counting their statistics. The CSV format "STL,TOR" doesn't specify which goals were scored with which team. Current implementation prioritizes completeness over accuracy by including two separate totals columns (goals & shots; with and without multi team players).

Production Solution: Would require game-by-game data or NHL API integration to accurately split traded players' statistics between teams.

Project Structure

nhl-stats-api/
├── main.py                 # FastAPI application
├── docker-compose.yml      # Container orchestration
├── Dockerfile             # Python container
├── requirements.txt       # Dependencies
├── sql_queries/           # SQL query files
│   ├── query_loader.py
│   └── *.sql
└── data/
    └── data_dump.csv

Testing

# Test all endpoints
curl "http://localhost:8000/health"
curl "http://localhost:8000/top-scorers?limit=3"
curl "http://localhost:8000/penalty-leaders?limit=3"
curl "http://localhost:8000/team-stats"
curl "http://localhost:8000/team-changes?limit=5"
curl "http://localhost:8000/team-roster/TBL"

Interactive Demo

Run the included demo script for a guided walkthrough of all endpoints:

chmod +x demo.sh
./demo.sh

The demo script will:

Check API health status
Display top 5 goal scorers
Show team statistics with goals and shots
List penalty leaders
Identify players who changed teams
Display a specific team roster (Tampa Bay)
Provide an overall season summary

Each demo pauses between sections, allowing you to review the results before continuing.

Stop API: docker compose down

Development Notes

Time Spent: ~4-5 hours

Breakdown:

Docker/Environment Setup (~45 min): Initial Docker Compose configuration, understanding container networking and health checks
FastAPI Development (~90 min): Endpoint implementation, request/response handling, automatic documentation setup
Database Design & SQL (~60 min): Schema decisions, writing queries with Postgres-specific functions, handling NULL values
Data Processing (~45 min): CSV parsing with Pandas, column mapping, multi-team player logic
Testing & Documentation (~45 min): Manual endpoint testing, README writing, deployment verification

Technologies Learned

Docker Compose (New):

First time orchestrating containerized applications
Learned about best practices (example: health checks)
Debugged file naming issue (docker-compose.yml vs docker_compose.yml)

FastAPI (Familiar, but mostly new):

Coming from other Python frameworks, FastAPI's automatic OpenAPI docs were impressive
Type hints and Pydantic validation were intuitive
Async capabilities available but not required for this use case

Python - (Familiar, but seriously expanded):

While familiar with Python, this project pushed me to explore more advanced patterns
Implemented custom utility classes (QueryLoader) with proper encapsulation
Leveraged lambda functions (new) and Pandas apply() for data transformations

Challenges & Solutions

Challenge 1: Multi-Team Players

Issue: Players traded mid-season appear as "STL,TOR" in a single field
Initial Approach: Tried to split and aggregate normally, realized this double-counted statistics
Solution: For team stats, created two totals columns (with and without multi team players) in favor of functionality.
Learning: Sometimes data constraints require choosing between completeness and accuracy

Challenge 2: Dynamic SQL Query Loading

Issue: Wanted to separate SQL from Python code for maintainability
Solution: Built QueryLoader utility class to read .sql files at runtime
Trade-off: Queries are cached in memory, requiring container restart for changes
Benefit: Much cleaner separation of concerns, easier to review SQL independently

Challenge 3: Season Parameter Design

Issue: Initially hardcoded "2022-2023" in all queries
Improvement: Made it a parameter with sensible default
Implementation: Added season: str = "2022-2023" to all endpoints
Result: API is now future-proof for multi-season data

Challenge 4: CSV Column Variations

Issue: Source CSV had inconsistent naming (spaces, capitalization, empty columns)
Solution: Created flexible column mapping dictionary and strip/normalize logic
Edge Case: Handled string "None" values that should be NULL

Challenge 5: Docker Deployment Testing

Issue: Needed to ensure it works on reviewer's machine
Solution: Tested by extracting to fresh directory and running from scratch
Discovery: Important to include data file in submission and document exact startup steps

Name		Name	Last commit message	Last commit date
Latest commit History 7 Commits
.vscode		.vscode
data		data
sql_queries		sql_queries
.DS_Store		.DS_Store
README.md		README.md
demo.sh		demo.sh
docker-compose.yml		docker-compose.yml
dockerfile		dockerfile
main.py		main.py
requirements.txt		requirements.txt
startup.sh		startup.sh

Navigation Menu

Repository files navigation

NHL Stats API

Quick Setup

Prerequisites

Start the API

API Endpoints

POST /upload-csv/

GET /top-scorers

GET /penalty-leaders

GET /team-stats

GET /team-changes

GET /team-roster/{team}

Architecture

Known Limitations

Project Structure

Testing

Interactive Demo

Development Notes

Time Spent: ~4-5 hours

Technologies Learned

Challenges & Solutions

About

Releases

Packages

Contributors

Languages

Folders and files

Latest commit

History

Repository files navigation

NHL Stats API

Quick Setup

Prerequisites

Start the API

API Endpoints

POST /upload-csv/

GET /top-scorers

GET /penalty-leaders

GET /team-stats

GET /team-changes

GET /team-roster/{team}

Architecture

Known Limitations

Project Structure

Testing

Interactive Demo

Development Notes

Time Spent: ~4-5 hours

Technologies Learned

Challenges & Solutions

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages