SQL PROJECT: Chronic Disease Analysis
STEPS FOR DATA
Step 0: Project Planning
🎯 Objective
Analyze chronic disease data to:
Identify high-risk states
Understand disease trends
Compare health indicators
Step 1: Data Collection
Step 2: DataBase Creation
CREATE DATABASE chronic_disease_db;
Step 3: Understanding data and Table Structure
Data
Column Name Description Example
Type
year_start Integer
Start year of the data record 2018
year_end Integer
End year of the data record 2018
location_abbr Text
State abbreviation CA
location_desc Text
Full state name California
data_source Text
Source of the data BRFSS
topic Text
Disease category Diabetes
Specific metric being % of adults with
question Text
measured diabetes
Survey response category (if
response Text Yes / No
applicable)
data_value_unit Text Unit of measurement %
data_value_type Text Type of metric Prevalence
data_value Numeric Actual value of the metric 8.5
Alternate representation of
data_value_alt Numeric 8.5
value
data_value_footnote_symbol Text Symbol for footnote *
Data
Column Name Description Example
Type
reference
Additional explanation about Estimate
data_value_footnote Text
data suppressed
Lower bound of confidence
low_confidence_limit Numeric 7.8
interval
Upper bound of confidence
high_confidence_limit Numeric 9.2
interval
stratification_category1 Text Type of segmentation Gender
stratification1 Text Segment value Male
stratification_category2 Text Secondary segmentation Age Group
stratification2 Text Secondary segment value 18–25
stratification_category3 Text Tertiary segmentation Race
stratification3 Text Tertiary segment value Asian
response_id Text Unique ID for response RESP001
location_id Text Unique ID for location LOC001
topic_id Text Unique ID for topic TOP001
question_id Text Unique ID for question Q001
Fact:
DataValue
Dimensions:
Time → YearStart, YearEnd
Location → LocationDesc
Topic → Topic, Question
Demographics → Stratification
Step 4: Create the Table
----Raw table--
CREATE TABLE chronic_data_raw (
YearStart TEXT,
YearEnd TEXT,
LocationAbbr TEXT,
LocationDesc TEXT,
DataSource TEXT,
Topic TEXT,
Question TEXT,
Response TEXT,
DataValueUnit TEXT,
DataValueType TEXT,
DataValue TEXT,
DataValueAlt TEXT,
DataValueFootnoteSymbol TEXT,
DataValueFootnote TEXT,
LowConfidenceLimit TEXT,
HighConfidenceLimit TEXT,
StratificationCategory1 TEXT,
Stratification1 TEXT,
StratificationCategory2 TEXT,
Stratification2 TEXT,
StratificationCategory3 TEXT,
Stratification3 TEXT,
Geolocation TEXT,
LocationID TEXT,
TopicID TEXT,
QuestionID TEXT,
ResponseID TEXT,
DataValueTypeID TEXT,
StratificationCategoryID1 TEXT,
StratificationID1 TEXT,
StratificationCategoryID2 TEXT,
StratificationID2 TEXT,
StratificationCategoryID3 TEXT,
StratificationID3 TEXT
);
----I use Text to avoid import errors---
Step 5: Import the Data Into the table
COPY chronic_data_raw
FROM 'D:\Uncodemy\SQL\sql project\Chronic Disease Analysis
using PostgreSQL/U.S._Chronic_Disease_Indicators.csv'
DELIMITER ','
CSV HEADER;
Step 6: Data Cleaning If required
---ETL (Extract, Transform, Load)---
CREATE TABLE chronic_data AS
SELECT
YearStart::INT AS year_start,
YearEnd::INT AS year_end,
TRIM(LocationAbbr) AS location_abbr,
TRIM(LocationDesc) AS location_desc,
TRIM(DataSource) AS data_source,
TRIM(Topic) AS topic,
TRIM(Question) AS question,
TRIM(Response) AS response,
TRIM(DataValueUnit) AS data_value_unit,
TRIM(DataValueType) AS data_value_type,
NULLIF(DataValue, '')::FLOAT AS data_value,
NULLIF(DataValueAlt, '')::FLOAT AS data_value_alt,
NULLIF(LowConfidenceLimit, '')::FLOAT AS low_confidence_limit,
NULLIF(HighConfidenceLimit, '')::FLOAT AS
high_confidence_limit,
TRIM(StratificationCategory1) AS stratification_category1,
TRIM(Stratification1) AS stratification1
FROM chronic_data_raw;
-----I created a cleaned analytical table from the raw dataset by
converting data types, handling missing values using NULLIF, removing
whitespace using TRIM, and standardizing column names into
snake_case for consistency and better query performance.----
---Remove NULL data_value—
DELETE FROM chronic_data
WHERE data_value IS NULL;
Step 7: Exploratory Data Analysis (EDA)
Step 8: Business Analysis Query
Step 9: Write down all the Insights
Step 10: Make a Visualization
Step 11: Make a Github Portfolio Project
Note: Create Data Dictionary for your references
Project Skills Requirements:
SQL
Data Cleaning
Aggregation
Trend Analysis
Dashboard
Evaluation Question :
1. How many total records are present in the dataset ?
2. How many unique states are included in the dataset?
3. How many unique disease topics are present ?
4. What are the distinct years available in the dataset?
5. How many records exist for each state in the dataset?
6. How many records exist for each dataset topic?
7. What is the minimum , maximum and average value of
data_value?
8. How many missing values are present in the data_value and
dataset?
9. What are the top 10 most common questions (indicators) in
the dataset?
10. Which Year has the highest number of records?
11. What is the average disease rate per state?
12. Which 10 states have the highest average disease
rate?
13. Which 10 states have the lowest average disease rate?
14. What is the average topic disease rate per topic?
15. Which disease topic has the highest average value?
16. Which disease topic has the lowest average value?
17. How does the average disease rate change over the
years?
18. For each state , what is the maximum disease value
recorded?
19. Which state and disease combination has the highest
recorded value?
20. What are the top 5 diseases with the highest average
rate in the most recent year?
21. Which state shows the highest increase in disease rate
over the time?
22. Which disease topic shows the largest growth trend
over the years?
23. For each disease topic , which state has the highest
value?
24. Rank States by average disease rate within each topic?
25. Find the top 3 states per disease topic with the highest
values?