8 Essential SQL Techniques for Data Analysis and Machine Learning

ByJordan Brown

SQL (Structured Query Language) is a critical skill for data analysts, data scientists, and anyone working with structured data. In fact, a recent survey by Burtch Works found that SQL is the most commonly used tool among data scientists, with 91% reporting that they use it regularly.[^1]

As the volume and complexity of data continues to grow, so does the demand for professionals who can effectively extract insights from databases. The global data analytics market is expected to reach $684 billion by 2030, representing a CAGR of 29.6% from 2023 to 2030.[^2] Mastering SQL is key to taking advantage of these opportunities.

In this article, we‘ll dive into 8 essential SQL techniques that every data professional should know. These techniques are particularly valuable for common data analysis and machine learning tasks, such as feature engineering, data cleaning, and aggregation.

Whether you‘re just starting to learn SQL or looking to deepen your existing knowledge, mastering these concepts will make you a more effective and efficient data practitioner. Let‘s jump in!

1. Aggregation Functions

Aggregation functions are a cornerstone of SQL and are used extensively in data analysis and machine learning. They allow you to perform calculations on a set of values and return a single result.

Common aggregation functions include:

  • COUNT(): returns the number of rows matching the query criteria
  • SUM(): calculates the sum of values
  • AVG(): calculates the average (arithmetic mean) of values
  • MIN(): finds the minimum value
  • MAX(): finds the maximum value

For example, let‘s say you‘re working with a "sales" table that contains columns for product, quantity, and price. To calculate the total revenue, you could use:

SELECT SUM(quantity * price) AS total_revenue
FROM sales;

You can also use aggregation functions with GROUP BY to calculate aggregated values for different categories or segments. For instance, to get the average order value by product category:

SELECT 
    product_category,
    AVG(quantity * price) AS avg_order_value
FROM sales
GROUP BY product_category;

In machine learning, aggregation is often used in the feature engineering process to create new input variables. For example, you might calculate the average purchase frequency or total lifetime value for each customer, and use these as features in a predictive model.

2. Window Functions

Window functions are a powerful feature in SQL that allow you to perform calculations across a set of rows that are related to the current row. They are particularly useful for time-series analysis and ranking problems.

Some commonly used window functions are:

  • ROW_NUMBER(): assigns a unique sequential number to each row within a partition
  • RANK(): assigns a rank to each row within a partition, with gaps for ties
  • DENSE_RANK(): like RANK but with no gaps in rank values when there are ties
  • LAG()/LEAD(): access values from preceding or following rows within the window

Window functions require an OVER clause to define the window of rows to operate on. You typically use PARTITION BY to group rows and ORDER BY to define the order of rows within each partition.

For example, to calculate a running total of sales by date:

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

In machine learning, window functions are often used for feature engineering tasks involving sequential data. For instance, you might use LAG to create features based on previous time steps, or use ROW_NUMBER to split data into training and test sets based on timestamp.

3. Subqueries and Common Table Expressions (CTEs)

Subqueries and CTEs are both ways to break complex queries into smaller, more manageable parts. They can make your SQL code more readable, maintainable, and efficient.

A subquery is a SELECT statement nested inside another query. They can be used in the SELECT, FROM, WHERE, or HAVING clauses of the outer query. For example:

SELECT 
    product_name,
    price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

CTEs (Common Table Expressions) are essentially named subqueries that can be referenced multiple times within a larger query. They are defined using a WITH clause:

WITH avg_price AS (
    SELECT AVG(price) AS avg_price
    FROM products
)
SELECT
    product_name,
    price
FROM products
WHERE price > (SELECT avg_price FROM avg_price);

Subqueries and CTEs are invaluable for data analysis tasks that require multi-step transformations or calculations. They allow you to modularize your code and avoid repetition.

In machine learning pipelines, CTEs can be used to encapsulate feature engineering logic or data preprocessing steps. This makes your code more readable and allows you to easily reuse these transformations for different models or datasets.

4. Joins

Joins are used to combine rows from two or more tables based on a related column. They are essential for working with relational databases and normalised data structures.

The main types of joins are:

  • INNER JOIN: returns only rows that have a match in both tables
  • LEFT/RIGHT JOIN: returns all rows from the left/right table and matched rows from the other table
  • FULL OUTER JOIN: returns all rows from both tables, using NULL for unmatched rows

For example, to combine customer data with their order history:

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

In data analysis and machine learning, joins are often used to bring together data from multiple sources. For instance, you might join customer demographic data with their transaction history and web activity to create a 360-degree view of each customer for churn prediction or segmentation analysis.

Joins can also be used to create features that capture relationships between entities. For example, to predict employee attrition, you might join employee data with data about their manager or department to create features like "manager tenure" or "department turnover rate".

5. CASE Statements

CASE statements allow you to add conditional logic to your SQL queries. They are often used to create new categorical variables or to perform different calculations based on certain criteria.

The basic syntax is:

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

For example, to categorize customers into "High", "Medium", and "Low" segments based on their total spend:

SELECT
    customer_id,
    CASE
        WHEN total_spend > 1000 THEN ‘High‘
        WHEN total_spend > 500 THEN ‘Medium‘
        ELSE ‘Low‘
    END AS segment
FROM customer_summary;

CASE statements are incredibly versatile and can be used in many different ways in data analysis and machine learning. Some common use cases include:

  • Binning continuous variables into discrete categories
  • Creating indicator variables (0/1) for certain conditions
  • Handling outliers or missing values
  • Implementing complex business rules or logic

6. String Functions

SQL provides a variety of string manipulation functions that are useful for data cleaning, parsing, and feature extraction. Some of the most commonly used ones are:

  • SUBSTRING(): extracts a substring from a string
  • CHARINDEX(): searches for a substring within a string and returns its starting position
  • REPLACE(): replaces all occurrences of a substring within a string
  • CONCAT(): concatenates two or more strings together
  • TRIM(): removes leading and trailing spaces from a string

For example, to parse the SKU code from a product description:

SELECT
    product_desc,
    SUBSTRING(product_desc, 1, CHARINDEX(‘-‘, product_desc) - 1) AS sku
FROM products;

String functions are essential for working with unstructured or semi-structured data, such as text, log files, or web data. They allow you to extract structured features from raw text that can be used in machine learning models.

For instance, you might use string functions to:

  • Parse URLs to extract domain, path, or query string parameters
  • Extract entities like names, locations, or dates from free text fields
  • Normalize or standardize text fields for consistency
  • Generate ngrams or other text features for NLP tasks

7. Pivoting and Reshaping Data

Pivoting data means converting rows into columns (or vice versa). This is often used for creating matrix-style reports or for aggregating data across multiple dimensions.

In SQL, pivoting is typically done using CASE statements along with aggregate functions. For example, to pivot sales data to show total revenue by region and year:

SELECT
    product,
    SUM(CASE WHEN region = ‘North‘ AND year = 2022 THEN revenue END) AS north_2022,
    SUM(CASE WHEN region = ‘North‘ AND year = 2023 THEN revenue END) AS north_2023,
    SUM(CASE WHEN region = ‘South‘ AND year = 2022 THEN revenue END) AS south_2022,
    SUM(CASE WHEN region = ‘South‘ AND year = 2023 THEN revenue END) AS south_2023
FROM sales
GROUP BY product;

The opposite of pivoting is unpivoting or melting, which converts columns into rows. This is often necessary when working with data that has been pivoted for reporting purposes but needs to be converted back into a normalised form for analysis.

In data science and machine learning, pivoting and reshaping are often used to:

  • Transform data between long and wide formats
  • Create sparse matrix representations of categorical variables
  • Compute summary statistics or aggregations across multiple dimensions
  • Reshape data to fit the input requirements of certain models or algorithms

8. Window Functions for Time Series Analysis

Window functions are particularly powerful for analyzing time series data. They allow you to perform calculations across a sliding window of rows, which is useful for computing moving averages, running totals, or lagged values.

Some common window functions for time series analysis include:

  • LAG()/LEAD(): access previous or future rows within a time window
  • FIRST_VALUE()/LAST_VALUE(): get the first or last value within a time window
  • SUM()/AVG() with OVER: compute moving sums or averages

For example, to calculate a 7-day moving average of daily sales:

SELECT
    date,
    sales,
    AVG(sales) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM daily_sales;

In machine learning, time series techniques are used for a variety of tasks, such as:

  • Demand forecasting
  • Anomaly detection
  • Predictive maintenance
  • Financial modeling

Window functions allow you to create lagged features, rolling statistics, and other time-based variables that can significantly improve the performance of machine learning models on sequential data.

Conclusion and Additional Resources

SQL is an essential tool for data analysis and machine learning, and these 8 techniques cover many of the core concepts and methods you‘ll need to be effective.

However, SQL is a deep topic, and there‘s always more to learn. To further develop your skills, I recommend checking out the following resources:

Remember, the best way to learn is by doing. Practice writing queries on real datasets, and don‘t be afraid to experiment and make mistakes. With time and experience, you‘ll develop a strong intuition for how to use SQL to solve complex data problems.

I hope this article has been helpful in your journey to master SQL for data analysis and machine learning. If you have any questions or suggestions, feel free to reach out. Happy analyzing!

[^1]: Burtch Works Survey: https://www.burtchworks.com/big-data-analyst-salary/big-data-career-tips/the-burtch-works-study-salaries-of-data-scientists-2022/
[^2]: Grand View Research – Data Analytics Market Size: https://www.grandviewresearch.com/industry-analysis/data-analytics-market

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts