Explore Titanic Dataset: Survival Rates, Age, and Correlations

titanic_passengers

+--------------+--------+--------+-----+----------+
| passenger_id | pclass | sex    | age | survived |
+--------------+--------+--------+-----+----------+
| 1            | 3      | male   | 22  | 0        |
| 2            | 1      | female | 38  | 1        |
| 3            | 3      | female | 26  | 1        |
| 4            | 1      | female | 35  | 1        |
| 5            | 3      | male   | 35  | 0        |
+--------------+--------+--------+-----+----------+

Scenario

HackerRank Jupyter-Notebook task – exploratory analysis of a classic dataset

Question

Perform an EDA on the data: calculate survival rate by sex, average age by class, and the correlation between age and survival. Produce clean visualizations and discuss any insights.

Hints

Write readable pandas code, label plots, and explain findings briefly.

Titanic survival analysis by sex, class, and age correlation

Using the titanic_passengers table, write a single SQL query that returns:

1. The survival rate by sex (average of survived, grouped by sex).
2. The average age by passenger class (pclass).
3. The overall Pearson correlation coefficient between age and survived.

Return all three metrics in one result set with the columns: metric, pclass, sex, value. For rows where pclass or sex does not apply, return NULL in those columns.

Table titanic_passengers

column	type	constraints
passenger_id	INTEGER	PRIMARY KEY
pclass	INTEGER
sex	VARCHAR(6)
age	INTEGER
survived	INTEGER

Sample data:

age	passenger_id	pclass	sex	survived
22	1	3	male	0
38	2	1	female	1
26	3	3	female	1
35	4	1	female	1
35	5	3	male	0

Expected output

A single result set with four columns: metric (label of the metric), pclass (passenger class where applicable), sex (sex where applicable), and value (numeric result). It includes: survival rate by sex, average age by passenger class, and the overall Pearson correlation between age and survival. For the sample data, females have a 100% survival rate and males 0%; average age is 36.5 in class 1 and 27.6667 in class 3; the age–survival correlation is approximately 0.3606.

metric	pclass	sex	value
survival_rate_by_sex	NULL	female	1
survival_rate_by_sex	NULL	male	0
average_age_by_class	1	NULL	36.5
average_age_by_class	3	NULL	27.6667
age_survival_correlation	NULL	NULL	0.3607

Hints

1. Use GROUP BY with AVG to compute survival rate by sex and average age by passenger class.
2. The Pearson correlation between age and survived can be computed from aggregate averages: corr(X,Y) = (E[XY] - E[X]E[Y]) / sqrt((E[X^2] - E[X]^2) * (E[Y^2] - E[Y]^2)).