3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.
DevOps.dev - Fr… 3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr…
SQL (Structured Query Language) is a versatile tool
Freedium Freedium
for managing and querying data in relational
< Go to the original
databases. While basic SQL queries are essential,
advanced SQL queries take your data manipulation
and analysis skills to the next level. In this guide,
we'll explore the top 10 advanced SQL queries with
practical code examples.
1. Recursive Common Table Expressions (CTE)
Query: Retrieve all employees and their managers
in a hierarchical structure using a recursive CTE.
Top 10 Advanced SQL Queries Copy
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, employee_name, manager_id
SQL Fundamentals FROM employees
Follow WHERE manager_id IS NULL
UNION ALL
DevOps.dev · ~3 min read · SELECT e.employee_id, e.employee_name, e.manager
November 17, 2023 (Updated: November 29, 2023) · Free: No FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.e
https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 1/9 https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 2/9
3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr… 3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr…
) Query: Calculate the running total of sales by order
Freedium
SELECT * FROM EmployeeHierarchy; Freedium
date.
Copy
2. Pivot Data
SELECT order_date, sales_amount, SUM(sales_amount)
FROM sales
Query: Pivot the sales table to display product
categories as columns and the total sales for each
month.
4. Ranking with Window Functions
Copy
Query: Rank employees by their salary within each
SELECT * department.
FROM (
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month
FROM sales
Copy
) AS pivoted
PIVOT (
SELECT department, employee_name, salary,
SUM(sales_amount)
RANK() OVER (PARTITION BY department ORDER
FOR product_category IN ('Electronics', 'Cloth
FROM employees;
) AS pivoted_sales;
3. Window Functions 5. Find Gaps in Sequential Data
https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 3/9 https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 4/9
3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr… 3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr…
Query: Identify missing order numbers in a SELECT product_id, attribute_name, attribute_value
Freedium Freedium
sequence. FROM products
UNPIVOT (
attribute_value FOR attribute_name IN (color, si
Copy ) AS unpivoted_data;
WITH Sequences AS (
SELECT MIN(order_number) AS start_seq, MAX(order
FROM orders
) 7. Finding Consecutive Events
SELECT start_seq + 1 AS missing_sequence
FROM Sequences Query: Identify consecutive order dates for the
WHERE NOT EXISTS (
same product
SELECT 1
FROM orders o
WHERE o.order_number = Sequences.start_seq + 1
) Copy
WITH ConsecutiveOrders AS (
SELECT product_id, order_date,
LAG(order_date) OVER (PARTITION BY produc
FROM orders
6. Unpivot Data )
SELECT product_id, order_date, prev_order_date
Query: Unpivot data from a table with multiple FROM ConsecutiveOrders
columns representing different attributes. WHERE order_date - prev_order_date = 1;
Copy
https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 5/9 https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 6/9
3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr… 3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr…
CAST(order_details ->> 'quantity' AS INTEGE
Freedium Freedium
FROM orders;
8. Aggregation with FILTER Clause
Query: Calculate the average salary of employees
in the Sales department.
10. Creating and Using Temp Tables
Query: Create a temporary table to store
Copy
intermediate results and join it with other tables.
SELECT department,
AVG(salary) FILTER (WHERE department = 'Sal
FROM employees Copy
GROUP BY department
-- Create a temporary table
CREATE TEMPORARY TABLE temp_product_sales AS
SELECT product_id, SUM(sales_amount) AS total_sale
FROM sales
GROUP BY product_id;
9. JSON Data Extraction
Query: Extract data from a JSON column. -- Join with other tables
SELECT p.product_name, t.total_sales
FROM products p
JOIN temp_product_sales t ON p.product_id = t.prod
Copy
SELECT order_id, customer_id,
order_details ->> 'product' AS product_name
Conclusion
https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 7/9 https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 8/9
3/19/24, 8:33 AM Top 10 Advanced SQL Queries | by SQL Fundamentals | in DevOps.dev - Fr…
Advanced SQL queries allow you to tackle complex
Freedium
data analysis tasks and solve intricate data
manipulation challenges. By mastering these
queries, you'll be well-equipped to handle a wide
range of data-related tasks and make more
informed decisions based on your data.
As you continue to work with SQL and relational
databases, regularly practicing and applying these
advanced queries will help solidify your expertise
and enhance your data analysis capabilities.
SQL Fundamentals
🚀
💫
Thank you for your time and interest! You can find
even more content at SQL Fundamentals
#sql #data-science #data-scientist #data-analysis #data-analytics
https://freedium.cfd/https://blog.devops.dev/top-10-advanced-sql-queries-dd5717b7e902 9/9
