SQL for Data Analysis

SQL is essential for any data professional. Learn the queries you'll use daily.

Basic SELECT Queries

The foundation of all SQL queries.

-- Simple select

SELECT * FROM customers;

-- Select specific columns

SELECT name, email, signup_date FROM customers;

-- Add WHERE clause

SELECT * FROM orders WHERE amount > 100;

-- Use AND, OR operators

SELECT * FROM orders

WHERE amount > 100 AND status = 'completed';

Aggregation Functions

Summarize data with these powerful functions.

-- COUNT, SUM, AVG, MIN, MAX

SELECT

COUNT(*) as total_orders,

SUM(amount) as total_revenue,

AVG(amount) as average_order,

MIN(amount) as smallest_order,

MAX(amount) as largest_order

FROM orders;

-- GROUP BY to aggregate by category

SELECT

category,

COUNT(*) as product_count,

AVG(price) as avg_price

FROM products

GROUP BY category;

-- HAVING clause to filter groups

SELECT

category,

COUNT(*) as product_count

FROM products

GROUP BY category

HAVING COUNT(*) > 10;

Joins

Combining data from multiple tables.

-- INNER JOIN

SELECT c.name, o.order_id, o.amount

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id;

-- LEFT JOIN

SELECT c.name, COUNT(o.order_id) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.name;

-- JOIN multiple tables

SELECT c.name, o.order_id, p.product_name

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id;

Advanced Queries

Powerful techniques for complex analysis.

-- Window functions

SELECT

name,

salary,

AVG(salary) OVER (PARTITION BY department) as dept_avg

FROM employees;

-- CTE (Common Table Expression)

WITH high_value_customers AS (

SELECT customer_id, SUM(amount) as total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(amount) > 10000

)

SELECT * FROM high_value_customers;

-- CASE statements

SELECT

name,

salary,

CASE

WHEN salary > 100000 THEN 'Senior'

WHEN salary > 75000 THEN 'Mid-level'

ELSE 'Junior'

END as level

FROM employees;

Query Optimization Tips

Use indexes on frequently queried columns

Avoid SELECT * - specify needed columns

Use JOIN instead of subqueries when possible

Limit results with WHERE clauses

Use EXPLAIN to analyze query performance

Common Data Analysis Patterns

**Cohort Analysis** - Track user groups over time

**RFM Analysis** - Recency, Frequency, Monetary value

**Time Series** - Data aggregated over time periods

**Churn Analysis** - Who's leaving and why

SQL is the universal language of data. Master these concepts and you'll be able to extract insights from any database!