A practical SQL and Python portfolio built around two real-world analytics case studies: COVID-19 exploration and Nashville housing data cleaning.
The repository demonstrates how raw spreadsheet data can be loaded into MySQL, explored with analytical SQL, cleaned with repeatable transformations, and converted into recruiter-friendly result outputs.
From the historical snapshot included in this repository:
- the generated summary contains approximately 150.6 million reported cases and 3.18 million reported deaths across country-level rows;
- the corresponding aggregate case-fatality ratio is approximately 2.11%;
- Andorra has the highest recorded peak total-case share in this snapshot at approximately 17.1% of population, followed by Montenegro and Czechia;
- the United States appears within the top ten at approximately 9.77%.
The generated tables are available under outputs/, including covid_global_summary.csv, covid_top_infection_rates.csv, and covid_vaccination_progress.csv.
These figures are based on the historical files committed to this repository and are not current COVID-19 statistics.
The included Nashville dataset contains 56,477 rows. The reproducible data-quality summary identifies 29 missing property addresses before cleaning and 206 rows involved in duplicate groups using the project business key. After normalizing Y/N and Yes/No, approximately 91.7% of rows are classified as No for SoldAsVacant, versus 8.3% as Yes.
Generated summaries are available in outputs/housing_data_quality_summary.csv and outputs/housing_sold_as_vacant_distribution.csv.
Business / analytical questions
- How did recorded cases and deaths evolve over time?
- What share of a country's population had recorded cases?
- Which countries had the highest infection and death totals?
- What did global case/death totals look like?
- How did cumulative vaccination activity evolve by country?
SQL techniques used
- joins across deaths and vaccination tables
- aggregate functions and grouped metrics
- CTEs
- window functions
NULLIFfor safer ratio calculations- reusable SQL views
Use the corrected portfolio version:
case_studies/covid_exploration.sql
A key correction from the original exploratory script is that vaccination analysis now joins CovidDeaths to the actual CovidVaccinations table rather than accidentally self-joining the deaths table.
Data-quality problems addressed
- missing property addresses
- compound address fields
- inconsistent
Y/NversusYes/Nocategories - duplicate records
- reusable filtering logic
SQL techniques used
- self joins and
UPDATE COALESCE- string parsing with
SUBSTRING_INDEX - schema changes with
ALTER TABLE CASEtransformationsROW_NUMBER()for duplicate detection- stored procedures
Use the corrected portfolio version:
case_studies/nashville_cleaning.sql
The original SQL files remain in the repository as historical exploratory work. The case_studies/ versions are the recommended files to review and run.
generate_portfolio_outputs.py reproduces the headline result tables and charts directly from the included Excel snapshots, without requiring a running MySQL server:
python generate_portfolio_outputs.pyIt writes compact CSV summaries to outputs/ and static charts to assets/. This gives reviewers immediate access to findings while the SQL case studies remain the primary demonstration of query design.
A GitHub Actions workflow runs the generator when the source datasets, generator, or workflow change and commits refreshed outputs automatically. The first workflow run completed successfully.
load_data.py loads the included Excel files into MySQL using repository-relative paths and environment-based credentials.
Install dependencies:
pip install -r requirements.txtConfigure MySQL:
export MYSQL_USER="root"
export MYSQL_PASSWORD="your-password"
export MYSQL_HOST="localhost"
export MYSQL_PORT="3306"
export MYSQL_DATABASE="ptf_project"Then load all datasets:
python load_data.pyThe loader uses SQLAlchemy's structured URL.create(...), so passwords containing characters such as @, :, or / are handled safely.
Excel source files
↓
load_data.py ───────────────→ MySQL / ptf_project
↓ ↓
generate_portfolio_outputs.py case_studies/*.sql
↓ ↓
CSV summaries + charts analysis-ready views/results
sql-data-analytics-portfolio/
├── case_studies/
│ ├── covid_exploration.sql
│ └── nashville_cleaning.sql
├── outputs/ # Generated compact result tables
├── assets/ # Generated portfolio charts
├── generate_portfolio_outputs.py # Reproducible results generator
├── load_data.py # Portable Excel → MySQL loader
├── .github/workflows/ # Automated output generation
├── SQL_import_PY.ipynb # Original exploratory import notebook
├── Covid_ Data_Exploration.sql # Historical exploratory SQL
├── wrangling_cleaning_technique_SQL.sql
├── CovidDeaths.xlsx
├── CovidVaccinations.xlsx
├── Nashville Housing Data for Data Cleaning.xlsx
├── requirements.txt
└── README.md
- analytical SQL and business-question translation
- joins and multi-table analysis
- CTEs and window functions
- aggregation and ratio metrics
- data cleaning and standardization
- duplicate detection and removal
- schema transformation
- SQL views and stored procedures
- Python/pandas → MySQL ingestion
- automated analytical output generation
- matplotlib visualization
- GitHub Actions / reproducible workflows
- SQLAlchemy and environment-based configuration
The datasets are based on:
- Our World in Data — COVID-19 deaths and vaccination data
- Kaggle — Nashville housing data-cleaning dataset
The included files are historical snapshots used for the original project; this repository is intended to demonstrate analytics techniques rather than provide current COVID-19 reporting.
Potential next steps are SQL linting, an integration test against a disposable MySQL service in CI, and moving the large source spreadsheets to a documented data-download step or Git LFS to reduce repository size.