briankorir006-a11y/KENYA-FOOD-MARKET-PRICES-SQL-ANALYSIS
Domain:
agriculturesocioeconomicRecord type:
dataset
Creator:
bri
Host:
Kenya Food Market Prices — SQL Analysis
# Kenya Food Market Prices — SQL Analysis Project
## Project Overview
This project analyzes food commodity prices across Kenya's markets from 2006 to 2023.
The original dataset was a single flat file containing 10,647 records and 14 columns.
It was normalized into a star schema with 5 dimension tables and 1 fact table to enable
a full SQL analysis experience covering all major SQL concepts.
---
## Dataset
- **Source:** Kenya Food Prices (WFP — World Food Programme)
- **Period:** January 2006 — September 2023
- **Coverage:** 7 regions, 22 towns, 62 markets, 47 commodities
- **Original file:** `data/raw/FOOD_PRICES_IN_KENYA.csv`
---
## Project Structure
```
kenya_food_market_prices/
│
├── data/
│ ├── raw/
│ │ └── FOOD_PRICES_IN_KENYA.csv ← original flat file
│ │
│ └── processed/
│ ├── dim_regions.csv
│ ├── dim_food_categories.csv
│ ├── dim_towns.csv
│ ├── dim_markets.csv
│ ├── dim_commodities.csv
│ └── fact_prices_raw.csv
│
├── sql/
│ ├── setup/
│ │ ├── 01_create_schema.sql
│ │ ├── 02_create_staging.sql
│ │ ├── 03_insert_dimensions.sql
│ │ ├── 04_insert_fact.sql
│ │ └── 05_verify.sql
│ │
│ └── analysis/
│ ├── 01_trends_over_time.sql
│ ├── 02_regional_comparison.sql
│ ├── 03_commodity_analysis.sql
│ └── 04_market_analysis.sql
│
└── README.md
```
---
## Database Schema
The flat file was normalized into a star schema:
```
dim_regions
region_id (PK)
region_name
│
└── dim_towns
town_id (PK)
town_name
region_id (FK → dim_regions)
│
└── dim_markets
market_id (PK)
market_name
town_id (FK → dim_towns)
latitude
longitude
│
dim_food_categories │
category_id (PK) │
category_name │
│ │
└── dim_commodities │
commodity_id (PK)
commodity_name │
category_id (FK → dim_food_categories)
unit │
│ │
└───────────┴──── fact_prices
price_id (PK)
price_date
market_id (FK → dim_markets)
commodity_id (F …