SQL for Data Analysis
Exploratory Data Analysis (EDA)
Data Profiling
-- Understand data structure and quality
SELECT COUNT(*) as record_count FROM employees;
SELECT COUNT(DISTINCT department) as unique_departments FROM employees;
SELECT COUNT(*) - COUNT(email) as missing_emails FROM employees;
-- Column value distribution
SELECT salary, COUNT(*) as frequency
FROM employees
GROUP BY salary
ORDER BY frequency DESC;
-- Missing data analysis
SELECT
COUNT(*) as total_records,
COUNT(phone) as non_null_phone,
COUNT(*) - COUNT(phone) as missing_phone,
ROUND(100.0 * (COUNT(*) - COUNT(phone)) / COUNT(*), 2) as missing_percentage
FROM employees;
-- Data type and range checks
SELECT
MIN(salary) as min_salary,
MAX(salary) as max_salary,
ROUND(AVG(salary), 2) as avg_salary,
ROUND(STDDEV(salary), 2) as salary_stddev
FROM employees;Distribution Analysis
-- Value frequency distribution
SELECT
department,
COUNT(*) as emp_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as percentage
FROM employees
GROUP BY department
ORDER BY emp_count DESC;
-- Salary ranges and distribution
SELECT
CASE
WHEN salary < 50000 THEN 'Under 50K'
WHEN salary < 75000 THEN '50K-75K'
WHEN salary < 100000 THEN '75K-100K'
ELSE '100K+'
END as salary_range,
COUNT(*) as emp_count,
MIN(salary) as min_sal,
MAX(salary) as max_sal,
ROUND(AVG(salary), 2) as avg_sal
FROM employees
GROUP BY salary_range
ORDER BY MIN(salary);
-- Distribution visualization data
SELECT
salary,
COUNT(*) as frequency,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct,
RPAD('*', COUNT(*) / 10, '*') as bar_chart
FROM employees
GROUP BY salary
ORDER BY salary;Statistical Analysis
Summary Statistics
-- Comprehensive statistics by group
SELECT
department,
COUNT(*) as count,
ROUND(AVG(salary), 2) as mean_salary,
ROUND(MIN(salary), 2) as min_salary,
ROUND(MAX(salary), 2) as max_salary,
ROUND(STDDEV(salary), 2) as stddev_salary,
ROUND(AVG(ABS(salary - (SELECT AVG(salary) FROM employees WHERE department = e.department))), 2) as avg_deviation
FROM employees e
GROUP BY department
ORDER BY mean_salary DESC;
-- Percentile analysis
SELECT
department,
ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary), 2) as q1,
ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary), 2) as median,
ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary), 2) as q3,
ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary), 2) as p95
FROM employees
GROUP BY department;Outlier Detection
-- Find outliers using standard deviation
SELECT
emp_id,
first_name,
salary,
ROUND(AVG(salary) OVER (), 2) as avg_salary,
ROUND(STDDEV(salary) OVER (), 2) as stddev_salary,
ROUND(ABS(salary - AVG(salary) OVER ()) / NULLIF(STDDEV(salary) OVER (), 0), 2) as z_score
FROM employees
HAVING ABS(salary - AVG(salary) OVER ()) / NULLIF(STDDEV(salary) OVER (), 0) > 3
ORDER BY z_score DESC;
-- IQR method for outliers
WITH salary_stats AS (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) as q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as q3
FROM employees
)
SELECT
emp_id,
salary,
CASE
WHEN salary < (SELECT q1 FROM salary_stats) - 1.5 * ((SELECT q3 FROM salary_stats) - (SELECT q1 FROM salary_stats))
OR salary > (SELECT q3 FROM salary_stats) + 1.5 * ((SELECT q3 FROM salary_stats) - (SELECT q1 FROM salary_stats))
THEN 'Outlier'
ELSE 'Normal'
END as outlier_status
FROM employees;Comparative Analysis
Period-over-Period Comparison
-- Year-over-year sales comparison
SELECT
EXTRACT(QUARTER FROM order_date) as quarter,
EXTRACT(YEAR FROM order_date) as year,
ROUND(SUM(amount), 2) as total_sales,
ROUND(LAG(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date)), 2) as prev_period,
ROUND(SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date)), 2) as yoy_change,
ROUND(100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date))) / LAG(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date)), 2) as yoy_pct_change
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date)
ORDER BY year, quarter;Cohort Analysis
-- User cohort analysis
WITH user_cohorts AS (
SELECT
DATE_TRUNC('month', first_order_date)::DATE as cohort_month,
user_id,
DATE_TRUNC('month', order_date)::DATE as order_month
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
)
SELECT
cohort_month,
DATE_PART('month', order_month - cohort_month) / 1 as months_since_cohort,
COUNT(DISTINCT user_id) as users,
ROUND(100.0 * COUNT(DISTINCT user_id) /
(SELECT COUNT(DISTINCT user_id) FROM user_cohorts WHERE order_month = cohort_month), 2) as retention_rate
FROM user_cohorts
WHERE order_month >= cohort_month
GROUP BY cohort_month, months_since_cohort
ORDER BY cohort_month, months_since_cohort;Correlation & Relationship Analysis
-- Correlation between variables
WITH salary_data AS (
SELECT
years_experience,
salary,
AVG(salary) OVER () as avg_salary,
AVG(years_experience) OVER () as avg_experience,
STDDEV(salary) OVER () as stddev_salary,
STDDEV(years_experience) OVER () as stddev_experience
FROM employees
)
SELECT
ROUND(
SUM((years_experience - avg_experience) * (salary - avg_salary)) /
(COUNT(*) * stddev_salary * stddev_experience),
4
) as correlation
FROM salary_data;
-- Segment analysis
SELECT
CASE
WHEN years_experience < 2 THEN 'Junior'
WHEN years_experience < 5 THEN 'Mid-level'
WHEN years_experience < 10 THEN 'Senior'
ELSE 'Expert'
END as experience_level,
COUNT(*) as count,
ROUND(AVG(salary), 2) as avg_salary,
ROUND(AVG(performance_rating), 2) as avg_rating
FROM employees
GROUP BY experience_level
ORDER BY COUNT(*) DESC;Data Quality Validation
-- Check for invalid values
SELECT
CASE
WHEN salary < 0 THEN 'Negative salary'
WHEN salary > 1000000 THEN 'Unusually high salary'
WHEN email NOT LIKE '%@%' THEN 'Invalid email'
WHEN hire_date > CURRENT_DATE THEN 'Future hire date'
WHEN years_experience > 70 THEN 'Impossible experience'
ELSE NULL
END as data_quality_issue,
COUNT(*) as count
FROM employees
WHERE salary < 0
OR salary > 1000000
OR email NOT LIKE '%@%'
OR hire_date > CURRENT_DATE
OR years_experience > 70
GROUP BY data_quality_issue;
-- Duplicate detection
SELECT
email,
COUNT(*) as occurrence_count,
STRING_AGG(DISTINCT emp_id::text, ', ') as emp_ids
FROM employees
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;Trend Analysis
-- Moving average
SELECT
order_date,
amount,
ROUND(AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) as moving_avg_7day,
ROUND(AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
), 2) as moving_avg_30day
FROM daily_orders
ORDER BY order_date;
-- Growth rate
SELECT
DATE_TRUNC('month', order_date)::DATE as month,
ROUND(SUM(amount), 2) as monthly_revenue,
ROUND((SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date))) /
LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) * 100, 2) as growth_rate_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;Next Steps
Learn advanced SQL concepts and optimization techniques in the advanced-sql skill.