Advanced SQL Queries for Data Analysis

SQL is one of the most valuable tools for working with data. There are some advanced SQL queries you don’t learn while learning the fundamentals of SQL, but are helpful in your job while analyzing data. So, if you want to know about some advanced SQL queries for Data Analysis, this article is for you. In this article, I’ll take you through a guide to some advanced SQL queries for Data Analysis and when you can use them.

Advanced SQL Queries for Data Analysis

Below are some of the advanced SQL queries you should know for Data Analysis. Understanding these advanced SQL queries will enhance your ability to derive insights, perform data transformations, and make strategic decisions based on data.

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row. You can use window functions for running totals, ranking, moving averages, and cumulative sums without collapsing your results.

For example, look at the table below:

The query below calculates the average salary within each department without grouping the entire table by department, allowing each employee’s salary to be directly compared to their department average:

SELECT 
  employee_id,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) as avg_department_salary
FROM employees;

Window functions are supported by PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.25.0+), and IBM DB2.

Common Table Expressions (CTEs)

A CTE provides a way to create a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. You can use CTEs for temporary result sets that are referenced multiple times within a single query, or to improve readability and organization of complex queries.

For example, look at the table below:

The query below calculates total sales by region and then selects only those regions where sales exceed the average regional sales:

WITH RegionalSales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region
)
SELECT region, total_sales
FROM RegionalSales
WHERE total_sales > (SELECT AVG(total_sales) FROM RegionalSales);

CTEs are supported by PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.8.3+), and IBM DB2.

Recursive CTEs

A recursive CTE is a common table expression that references itself to repeatedly execute, thereby allowing for hierarchical or recursive queries. You can use recursive CTEs for querying hierarchical data, such as organizational charts, or for recursive calculations like Fibonacci sequences.

For example, the recursive CTE below generates a series of numbers from 1 to 10:

WITH RECURSIVE NumberSeries AS (
  SELECT 1 AS value
  UNION ALL
  SELECT value + 1 FROM NumberSeries WHERE value < 10
)
SELECT * FROM NumberSeries;

Recursive CTEs are supported by PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.8.3+), and IBM DB2.

PIVOT Queries

The PIVOT operator turns the unique values of a specified column into multiple columns in the output, allowing for cross-tabulation of the data. You can use PIVOT for transforming rows into columns, often for creating reports or comparative analyses.

For example, look at the table below:

The query below pivots the sales data so that each row represents an employee and each column represents a year’s total sales:

SELECT *
FROM 
  (SELECT EmployeeName, Year, Sales FROM EmployeeSales) AS SourceTable
PIVOT
  (
    SUM(Sales)
    FOR Year IN ([2020], [2021], [2022])
  ) AS PivotTable;

Pivot Queries are only supported by SQL Server and Oracle.

Dynamic SQL

Dynamic SQL involves constructing SQL queries dynamically as strings and then executing them. It is typically done within stored procedures or scripts. You can use dynamic SQL when the structure of your query is not known until runtime, such as when column names or filter criteria are specified by user input.

For example, the query below dynamically constructs a query to select all from a specified table and executes it:

DECLARE @TableName NVARCHAR(100) = N'Employees';
DECLARE @SQLQuery AS NVARCHAR(MAX);

SET @SQLQuery = N'SELECT * FROM ' + @TableName;
EXEC sp_executesql @SQLQuery;

Dynamic SQL is supported by SQL Server, Oracle, PostgreSQL, MySQL, and IBM DB2.

MERGE Statement

The MERGE statement allows you to perform multiple actions like INSERT, UPDATE, or DELETE in a single statement based on the condition specified. It’s often used to synchronize two tables. You can use the MERGE statement when you need to update a table based on another table’s content, insert new rows, update existing ones, or delete those that no longer match.

For example, look at the tables below:

The query below synchronizes the TargetTable with SourceTable, updating names where IDs match, inserting new entries from SourceTable, and deleting entries in TargetTable that no longer have corresponding entries in SourceTable:

MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name) VALUES (source.ID, source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

The MERGE Statement is supported by SQL Server, Oracle, and DB2.

Summary

So these were some of the advanced SQL queries for Data Analysis you should know. Understanding these advanced SQL queries will enhance your ability to derive insights, perform data transformations, and make strategic decisions based on data.

I hope you liked this article on Advanced SQL queries for Data Analysis. Feel free to ask valuable questions in the comments section below. You can follow me on Instagram for many more resources.