Explore Titanic Dataset: Survival Rates, Age, and Correlations
Company: Spokeo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates exploratory data analysis, descriptive statistics, group-wise aggregation, correlation interpretation, and data visualization skills in a Data Manipulation (SQL/Python) context for a data scientist role.
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.
Tables
titanic_passengers(passenger_id INTEGER, pclass INTEGER, sex VARCHAR(6), age INTEGER, survived INTEGER)
Hints
- Use GROUP BY with AVG to compute survival rate by sex and average age by passenger class.
- 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)).