SQL for Data Analysis

Introduction to SQL for Data Analysis

SQL (Structured Query Language) is the most essential tool in a data analyst's arsenal. Virtually every organization stores its data in relational databases, and SQL is the language used to retrieve, filter, aggregate, and transform that data. Regardless of whether you work with PostgreSQL, MySQL, SQL Server, BigQuery, or SQLite, the core syntax is consistent and transferable across platforms.

This article walks through the foundational SQL concepts every data analyst needs: selecting data, filtering rows, sorting results, aggregating with GROUP BY, joining tables, and using subqueries and window functions.

The SELECT Statement

Every SQL query begins with SELECT. It tells the database which columns to return. The FROM clause specifies which table to pull from.

-- Select all columns from a table
SELECT * FROM orders;

-- Select specific columns
SELECT order_id, customer_id, total_amount, order_date
FROM orders;

Using SELECT * retrieves every column, which is convenient for exploration but inefficient in production. Always name the specific columns you need in analytical queries to keep results clear and fast.

You can also create computed columns directly in the SELECT clause:

SELECT
  order_id,
  quantity,
  unit_price,
  quantity * unit_price AS total_revenue
FROM order_items;

Filtering with WHERE

The WHERE clause filters rows based on conditions. Only rows that satisfy the condition are returned.

-- Filter by a single value
SELECT * FROM customers WHERE country = 'France';

-- Multiple conditions with AND / OR
SELECT * FROM orders
WHERE total_amount > 500
  AND status = 'completed';

-- Range filter with BETWEEN
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Filter from a list of values
SELECT * FROM products
WHERE category IN ('Electronics', 'Accessories');

-- Partial text match with LIKE
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

NULL values require special handling — you cannot use = NULL. Instead use IS NULL or IS NOT NULL:

SELECT * FROM customers WHERE phone_number IS NULL;

Sorting with ORDER BY

The ORDER BY clause sorts the result set. ASC (ascending) is the default; DESC sorts from highest to lowest.

-- Sort by a single column
SELECT * FROM products ORDER BY price DESC;

-- Sort by multiple columns
SELECT customer_id, order_date, total_amount
FROM orders
ORDER BY customer_id ASC, order_date DESC;

LIMIT (or TOP in SQL Server) restricts the number of rows returned. This is extremely useful when exploring large tables:

SELECT * FROM events ORDER BY created_at DESC LIMIT 100;

Aggregating Data with GROUP BY

Aggregation is where SQL becomes truly powerful for analysis. The GROUP BY clause groups rows that share a value in one or more columns, and aggregate functions compute a summary value for each group.

Common aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().

-- Total revenue by country
SELECT
  country,
  COUNT(*) AS num_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY country
ORDER BY total_revenue DESC;

-- Monthly sales summary
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS orders,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

The HAVING clause filters groups after aggregation — it works like WHERE but for GROUP BY results:

-- Only show countries with more than 100 orders
SELECT country, COUNT(*) AS num_orders
FROM orders
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY num_orders DESC;

Joining Tables

Real-world databases store related data across multiple tables. JOINs combine rows from two or more tables based on a related column. Understanding joins is one of the most important SQL skills for any analyst.

INNER JOIN returns only rows where there is a match in both tables:

SELECT
  o.order_id,
  c.name AS customer_name,
  o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

LEFT JOIN returns all rows from the left table, and matching rows from the right. Rows with no match get NULL for the right table's columns. This is useful for finding customers with no orders:

-- Customers who have never placed an order
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

You can join more than two tables in a single query:

SELECT
  o.order_id,
  c.name AS customer_name,
  p.product_name,
  oi.quantity,
  oi.unit_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

Subqueries and CTEs

Subqueries are queries nested inside another query. They allow you to break complex problems into steps. A subquery in the WHERE clause can filter using the result of another query: