SQL for Data Science: Querying and Analyzing Data

SQL is the most important data skill you can learn. Every organization stores data in relational databases. Every data scientist — regardless of whether they use Python, R, or Julia — needs SQL to extract, filter, and aggregate that data. No other tool provides such direct, efficient access to production data.

The SELECT Statement

The SELECT statement retrieves data from tables. It is the foundation of everything else.

SELECT first_name, last_name, email
FROM customers;

Filtering with WHERE

SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND status = 'completed'
  AND amount > 100;

Use WHERE to filter rows before aggregation. This reduces data volume and speeds up queries. Understand the order of operations: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

Sorting with ORDER BY

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

Limiting Results

LIMIT restricts the number of returned rows. Useful for quick looks at data and pagination. Tied with OFFSET for scrollable result sets.

Joining Tables

Real-world data is normalized across multiple tables. Joins reconstruct the complete picture.

INNER JOIN

Returns only rows with matching keys in both tables:

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

LEFT JOIN

Returns all rows from the left table, with matching data from the right table (NULL where no match):

SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

This is useful for finding customers who have never placed an order — filter WHERE o.order_id IS NULL.

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables. These are less commonly used in practice.

Self-Joins

Join a table to itself, typically using aliases:

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Aggregation and Grouping

Aggregations summarize data at a higher level.

Aggregate Functions

SELECT COUNT(*) AS total_orders,
       SUM(amount) AS total_revenue,
       AVG(amount) AS avg_order_value,
       MIN(amount) AS min_order,
       MAX(amount) AS max_order
FROM orders;

GROUP BY

Group rows that share a value and apply aggregate functions per group:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

HAVING

Filter groups after aggregation (WHERE filters before):

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Window Functions

Window functions perform calculations across sets of rows while preserving individual row details. They are among the most powerful SQL features for data analysis.

ROW_NUMBER, RANK, DENSE_RANK

SELECT customer_id, order_date, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS order_rank
FROM orders;

Running Totals

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Moving Averages

SELECT order_date, amount,
       AVG(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS seven_day_avg
FROM orders;

LAG and LEAD

Access previous or next row values:

SELECT order_date, amount,
       LAG(amount, 1) OVER (ORDER BY order_date) AS previous_day_revenue,
       amount - LAG(amount, 1) OVER (ORDER BY order_date) AS daily_change
FROM daily_revenue;

Common Table Expressions

CTEs make complex queries readable and maintainable. They define temporary result sets within a query.

WITH customer_revenue AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id, total_spent
    FROM customer_revenue
    ORDER BY total_spent DESC
    LIMIT 100
)
SELECT c.customer_name, tc.total_spent
FROM customers c
INNER JOIN top_customers tc ON c.customer_id = tc.customer_id;

CTEs also enable recursive queries for hierarchical data like org charts and product categories.

Subqueries

Subqueries are nested queries within a main query. They can appear in SELECT, FROM, and WHERE clauses.

SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE name = 'Electronics'
);

Correlated subqueries reference the outer query and execute per row:

SELECT product_name, price
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id = p.category_id
);

Performance Considerations

SQL performance matters when working with large datasets. Index the columns used in WHERE, JOIN, and ORDER BY clauses. Write queries that minimize data scanned — filter early, select only needed columns, avoid SELECT *. Understand your database’s query plan using EXPLAIN ANALYZE.

Practical Data Science Queries

Cohort analysis:

SELECT DATE_TRUNC('month', first_order_date) AS cohort_month,
       months_since_first_order,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(revenue) AS revenue
FROM cohort_data
GROUP BY cohort_month, months_since_first_order;

RFM analysis:

SELECT customer_id,
       DATEDIFF('day', MAX(order_date), CURRENT_DATE) AS recency,
       COUNT(*) AS frequency,
       SUM(amount) AS monetary
FROM orders
GROUP BY customer_id;

SQL is the language of data. It is not going away. Investing time in SQL proficiency pays dividends across every data role. Master the fundamentals — SELECT, joins, aggregations, window functions, CTEs — and you can extract any insight from any database.

SQL for Data Analysis

Exploratory Queries

SELECT COUNT(*), COUNT(DISTINCT customer_id),
       MIN(order_date), MAX(order_date) FROM orders;

SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders GROUP BY month ORDER BY month;

SELECT order_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id
                         ORDER BY order_date) AS customer_ltv
FROM orders;

Common Table Expressions

WITH customer_revenue AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders GROUP BY customer_id
)
SELECT c.name, cr.total_spent
FROM customers c
JOIN customer_revenue cr ON c.id = cr.customer_id
WHERE cr.total_spent > 1000;

Data Quality Checks

Check for nulls, duplicates, and referential integrity using SQL aggregation and JOINs.

Performance Best Practices

Use appropriate data types. Filter early with WHERE clauses. Use EXPLAIN to understand query plans. Create indexes on join and filter columns. Avoid SELECT *. For large datasets, use approximate COUNT(DISTINCT).

Advanced SQL Techniques for Data Science

Window Functions

Window functions are essential for data scientists working with sequential data. The ROW_NUMBER window function assigns a unique rank to each row within a partition, enabling deduplication and top-N analysis. LAG and LEAD access rows before and after the current row, making it trivial to calculate period-over-period changes, moving averages, and time differences without self-joins. For example, computing a 7-day moving average of daily active users requires only a window frame specification — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — eliminating complex subqueries. Running totals, cumulative distributions, and percentiles are all cleanly expressed with window functions.

Common Table Expressions

CTEs (WITH clauses) dramatically improve query readability and enable recursive operations. A non-recursive CTE defines a named subquery that can be referenced multiple times in the main query, which is especially valuable for multi-step analysis pipelines. Recursive CTEs traverse hierarchical data like organizational charts, bill-of-materials structures, and network graphs. A recursive CTE consists of an anchor member (the base case) and a recursive member (referencing itself), terminated when no new rows are produced. This replaces procedural loops with declarative, set-based logic that runs entirely within the database engine.

Query Performance Optimization

Data scientists working with large tables must understand execution plans. The EXPLAIN ANALYZE command shows whether queries use index scans, sequential scans, hash joins, or nested loops. Slow queries often result from missing indexes, inefficient join orders, or scanning unnecessary data. Partitioned tables divide large datasets by date or key range, so queries against recent data scan only relevant partitions. Materialized views precompute expensive aggregations and refresh on a schedule. The single most impactful optimization is selective column access — avoid SELECT * and fetch only the columns needed for analysis.

Frequently Asked Questions

Do data scientists really need advanced SQL? Yes. SQL is the lingua franca of data access in most organizations. Even with Python and R for modeling, data scientists must extract, join, aggregate, and sample data from production databases. Advanced SQL skills reduce dependency on engineers, enable self-service analysis, and produce more efficient data pipelines.

What is the difference between WHERE and HAVING? WHERE filters rows before aggregation — it operates on individual source rows. HAVING filters groups after aggregation — it operates on the results of GROUP BY. If you need to filter on an aggregate like COUNT(*) > 10, you must use HAVING. For row-level filters like status = ‘active’, use WHERE for better performance.

Should I use Python or SQL for data transformation? Use SQL for operations that the database can execute efficiently: filtering, joining, aggregation, and window functions on large datasets. Use Python for operations that require procedural logic, custom functions, machine learning preprocessing, or integration with non-database data sources. The optimal workflow often combines both: SQL for extraction and initial transformation, Python for modeling and visualization.

How do I handle NULL values in SQL? NULL represents unknown or missing data and does not equal anything, including itself. Use IS NULL and IS NOT NULL for comparison, COALESCE to replace NULL with a default value, and NULLIF to convert a specific value to NULL. Be careful with aggregate functions — COUNT(*) includes NULL rows, but COUNT(column_name) excludes them. AVG, SUM, MIN, and MAX also ignore NULLs.

For a comprehensive overview, read our article on Bayesian Statistics Guide.

For a comprehensive overview, read our article on Big Data Tools Guide.