Perform EDA and diagnose data quality
Company: Citadel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a tabular dataset loaded into a pandas DataFrame, write code to perform exploratory data analysis end-to-end: inspect and report column data types, measure feature scales (min, max, mean, std) and identify potential outliers, check and summarize missing values (counts and percentages), compute basic summary statistics, and plot a correlation heatmap to highlight strongly related features. Conclude with a short written summary of quality issues and next steps to address them.
Overview: This question evaluates proficiency in exploratory data analysis and data quality assessment using pandas and related tools, covering inspection of data types, summary statistics, missing-value diagnostics, outlier detection, and feature correlation.
Column-level summary statistics and missing data profile
You are given a table customer_metrics containing basic numeric features for a small set of customers. Using SQL, produce a column-level exploratory data analysis summary for the numeric feature columns (age, annual_income, monthly_spend). For each of these columns, return a single row with:
- column_name
- non_null_count
- missing_count
- missing_pct (percentage of rows where the value is NULL, rounded to 2 decimal places)
- min_value
- max_value
- avg_value (rounded to 2 decimal places)
Only include rows for the three feature columns (age, annual_income, monthly_spend), not for customer_id.
Tables
customer_metrics(customer_id INT, age INT, annual_income DECIMAL(10,2), monthly_spend DECIMAL(10,2))
Hints
- Use COUNT(column) to count non-null values and COUNT(*) to count all rows.
- Stack per-column summaries using UNION ALL, and use ROUND on averages and percentages for readability.
Compute pairwise correlations between numeric features
Using the same customer_metrics table, compute pairwise Pearson correlation coefficients between the numeric feature columns age, annual_income, and monthly_spend. Assume your SQL dialect provides a CORR(x, y) aggregate function that ignores NULL values.
Return one row per unordered column pair with:
- col_x (first column name)
- col_y (second column name)
- corr_value (Pearson correlation between the two columns)
Filter the result to only include pairs where the absolute value of the correlation is at least 0.9, and order the output by descending absolute correlation and then by col_x, col_y.
Tables
customer_metrics(customer_id INT, age INT, annual_income DECIMAL(10,2), monthly_spend DECIMAL(10,2))
Hints
- Manually enumerate the column pairs you want to correlate and combine them with UNION ALL.
- Use the CORR aggregate on each pair and then filter on ABS(corr_value) to keep only strongly related features.