SQL for Data Analysis: Beyond the Basics

SQL remains one of the most important tools in a data analyst's toolkit. While basic SELECT statements will get you started, mastering advanced SQL techniques can dramatically improve your efficiency and analytical capabilities.

Window Functions

Window functions perform calculations across a set of table rows related to the current row. They're incredibly powerful for analytics:

SELECT 
  department,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

This query shows each employee's salary compared to their department's average.

Common Table Expressions (CTEs)

CTEs make complex queries more readable by breaking them into named temporary result sets:

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as total_sales
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
  month,
  total_sales,
  LAG(total_sales) OVER (ORDER BY month) as prev_month_sales,
  (total_sales - LAG(total_sales) OVER (ORDER BY month)) / LAG(total_sales) OVER (ORDER BY month) * 100 as growth_pct
FROM monthly_sales
ORDER BY month;

This query calculates month-over-month sales growth.

Advanced Joins and Set Operations

Beyond basic joins, techniques like self-joins and set operations can solve complex problems:

-- Self-join to find employees with the same manager
SELECT 
  e1.employee_name,
  e2.employee_name as colleague
FROM employees e1
JOIN employees e2 
  ON e1.manager_id = e2.manager_id
  AND e1.employee_id < e2.employee_id;

Optimizing Query Performance

As your datasets grow, query performance becomes crucial:

1. Use indexes wisely on columns frequently used in WHERE, JOIN, and ORDER BY clauses
2. Be specific in SELECT - avoid SELECT * when you only need specific columns
3. Filter early - apply WHERE conditions before joins when possible
4. Consider query execution plans to identify bottlenecks

By mastering these advanced SQL techniques, you'll be able to extract more insights from your data more efficiently.