###Introduction
Death by suicide is commonplace and occurs almost everywhere. However, the suicide rates and numbers differ from country to country. This project is a dataset of suicide cases from 1985-2020. The project aims to find and explore relationships between the suicide rate and age group, generation, country, HDI for the year, GDP for the year, and GDP per capita.
###Project description
The data set has 12 variables: age, country, country-year, GDP for_year, gdp_per_capita, generation, HDI for year, population, sex, suicides_no, suicides/100k pop, year. The data is between the years 1985 and 2020. It was downloaded from the Kaggle open dataset.
The following data-wrangling steps were carried out to clean the data set
HDI for year was removed since it had lots of nulls.
2. An ID column was added to uniquely identify the countries.
Summary of Findings
I observed that over time, GDP per capita has increased and suicide rates have decreased.
2. The number of suicides among males is more than twice that of females.
3. The number of suicides in the Russian Federation is high.
4. The age group 35-54 has high suicide rates.
5. The Baby Boomer generation has high suicide rates.
CREATE database portfolioproject
USE portfolioproject
SHOW tables;
-- Select data we are going to use `suicide m` table
SELECT *
FROM `suicide m`;
-- Data cleaning of suicide dataset
-- Adding id column
ALTER TABLE `suicide m`
ADD COLUMN id int NOT NULL auto_increment Primary Key;
-- checking for distinct number of users
SELECT COUNT(DISTINCT(id))
FROM `suicide m`;
-- `suicide m` has 30556 users
-- checking missing data
SELECT *
FROM `suicide m`
WHERE id IS NULL;
-- deleting column HDI for year which will not use in analysis
ALTER TABLE `suicide m`
DROP COLUMN `HDI for year`
-- Preview of the cleaned data
SELECT *
FROM `suicide m`;
-- ANALYZING OF DATA
-- suicide no in russian federation
SELECT country,SUM(suicides_no)
FROM `suicide m`
WHERE country LIKE'r%'
GROUP BY 1
ORDER BY 2 DESC;
-- age with highest suicide cases
SELECT age,SUM(suicides_no)
FROM `suicide m`
GROUP BY 1
ORDER BY 2 DESC;
-- age with highest rate of suicide case is 35-54
-- gender with highest suicide cases
SELECT sex,SUM(suicides_no)
FROM `suicide m`
GROUP BY sex
ORDER BY 2 DESC;
-- males have a high rate of suicide than female
-- country with highest suicide numbers
SELECT country,`gdp_for_year ($)`,SUM(suicides_no)
FROM `suicide m`
GROUP BY 1,2
ORDER BY 3 DESC;
-- Russian federation has highest suicide cases
-- Looking at total suicides vs population
-- shows what percentage of population has committed suicide
SELECT country,year,population,SUM(suicides_no) AS total_suicidecases,(suicides_no/population)*100 AS suicide_percent
FROM `suicide m`
GROUP BY 1,2,3,5
ORDER BY 5 DESC;
-- Looking at countries with highest suicide rate as compared to the population
-- Using CTE
WITH t1 AS(
SELECT country,population,SUM(suicides_no) AS sum_scases,(SUM(suicides_no)/population)*100 AS suicide_percent
FROM `suicide m`
GROUP BY 1,2)
SELECT country,population,MAX(sum_scases),MAX(suicide_percent)
FROM t1
GROUP BY 1,2
ORDER BY 4 DESC;
-- Republic of Korea has the highest suicide rate as compared to the population
-- Looking at population vs suicide_no
SELECT SUM(population),SUM(suicides_no),SUM(suicides_no)/SUM(population)*100 AS suicide_percent
FROM `suicide m`
ORDER BY 3;
-- world suicide percent is 0.0048
-- suicides vs GDP per capita
SELECT country,SUM(suicides_no),SUM(`gdp_per_capita ($)`)
FROM `suicide m`
GROUP BY 1
ORDER BY 3 DESC;
-- Luxembourg has the highest sum per capita
-- suicides vs generation
SELECT SUM(suicides_no),generation
FROM `suicide m`
GROUP BY 2
ORDER BY 1 DESC;
-- generation boomers have high suicide numbers
-- age group with high suicide numbers
SELECT SUM(suicides_no),age
FROM `suicide m`
GROUP BY 2
ORDER BY 1 DESC;
-- 35-54 age group have high suicide_no
-- Creating View to store data for later visualization
CREATE VIEW countrysuicides_no AS
SELECT country,SUM(suicides_no)
FROM `suicide m`
WHERE country LIKE'r%'
-- GROUP BY 1
-- ORDER BY 2 DESC;
