1. Window Functions

Window functions facilitate calculations across a group of rows tied to the current row. For instance, consider an example where we compute the running total of sales using the SUM() function in conjunction with the OVER() clause. Imagine we have a sales dataset named 'Sales_Data' that logs sales figures over different dates. Our goal is to compute a running total for each date, representing the total sales accrued up to that date.

SELECT

date,

sales,

SUM(sales) OVER (ORDER BY date) AS running_total

FROM

sales_data;

This query yields a running total of sales, allowing for easy tracking of cumulative sales trends.

Output: date | sales | running_total 2023-01-01 | 100 | 100 2023-01-02 | 150 | 250 2023-01-03 | 200 | 450 2023-01-04 | 250 | 700 Window functions are versatile and can be employed for a variety of tasks such as calculating moving averages and ranks without condensing the result set into a single row per group.

2. Common Table Expressions (CTEs)

CTEs provide a mechanism for creating temporary result sets that can be referenced within a query. They enhance readability and help simplify complex queries. Here’s an example of using a CTE to determine the total revenue for each product category.

WITH category_revenue AS (

SELECT category, SUM(revenue) AS total_revenue

FROM sales

GROUP BY category

)

SELECT * FROM category_revenue;

In this instance, we define a CTE called ‘category_revenue’ that calculates the total revenue for each category by summing the revenue from the sales table and grouping by the category column. The main query retrieves all columns from the ‘category_revenue’ CTE, showcasing the computed total revenue for each category.

Output: category | total_revenue A | 5000 B | 7000 C | 4500

3. Recursive Queries

Recursive queries are useful for traversing hierarchical data structures, such as organizational charts. For example, if we have a table that outlines employee relationships, we can find all subordinates under a specific manager.

WITH RECURSIVE subordinates AS (

SELECT employee_id, name, manager_id

FROM employees

WHERE manager_id = 'manager_id_of_interest'

UNION ALL

SELECT e.employee_id, e.name, e.manager_id

FROM employees e

JOIN subordinates s ON e.manager_id = s.employee_id

)

SELECT * FROM subordinates;

This recursive CTE identifies all employees reporting directly or indirectly to a particular manager identified by 'manager_id_of_interest'. It begins with employees who report directly to that manager and then recursively identifies their subordinates, thereby constructing the hierarchy.

Output: employee_id | name | manager_id 2 | Alice | manager_id_of_interest 3 | Bob | 2 4 | Charlie | 3

4. Pivot Tables

Pivot tables convert rows into columns, summarizing data in a structured format. For example, if we have a sales data table, we might want to pivot the data to show total sales for each product across various months.

SELECT product,

SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,

SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,

SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar

FROM sales_data

GROUP BY product;

This query aggregates sales data for each product by month using conditional aggregation. It separately sums sales figures for January, February, and March, resulting in a table that displays total sales for these months per product.

Output: product | Jan | Feb | Mar Product A | 100 | 200 | 150 Product B | 80 | 190 | 220 Product C | 60 | 140 | 130

5. Analytic Functions

Analytic functions compute aggregate values based on groups of rows. For example, we can utilize the ROW_NUMBER() function to assign a unique rank to each record within a dataset.

SELECT customer_id, order_id,

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank

FROM orders;

This query ranks each order for every customer based on the order date, providing a sequential order of purchases made by each customer.

Output: customer_id | order_id | order_rank 1 | 101 | 1 1 | 102 | 2 2 | 201 | 1 2 | 202 | 2 2 | 203 | 3

6. Unpivot

Unpivoting is the reverse of pivoting, where columns are converted into rows. For instance, if we have a table with sales data aggregated by month, we might want to unpivot it to analyze trends over time.

SELECT product, month, sales

FROM sales_data

UNPIVOT (sales FOR month IN (sales_jan AS 'Jan', sales_feb AS 'Feb', sales_mar AS 'Mar')) AS unpivoted_sales;

This query transforms monthly sales columns into rows, facilitating trend analysis over time by product. Each row corresponds to a product's sales for a specific month.

Output: product | month | sales Product A | Jan | 100 Product A | Feb | 150 Product A | Mar | 200 Product B | Jan | 200 Product B | Feb | 250 Product B | Mar | 300

7. Conditional Aggregation

Conditional aggregation applies aggregate functions based on specified criteria. For instance, we might want to calculate the average sales amount solely for orders made by repeat customers.

SELECT customer_id,

AVG(CASE WHEN order_count > 1 THEN order_total ELSE NULL END) AS avg_sales_repeat_customers

FROM (

SELECT customer_id, COUNT(*) AS order_count, SUM(order_total) AS order_total

FROM orders

GROUP BY customer_id

) AS customer_orders;

This query computes the average order total for customers who have made more than one purchase, aggregating both the order count and total order amounts for each customer before calculating the average for repeat customers.

Output: customer_id | avg_sales_repeat_customers 1 | 250 2 | 150 3 | 300

8. Date Functions

Date functions in SQL facilitate manipulation and extraction of date-related information. For example, we can employ the DATE_TRUNC() function to aggregate sales data by month.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY DATE_TRUNC('month', order_date);

This output displays the total sales amount aggregated for each month, represented by the first day of that month (e.g., 2023-01-01 for January).

Output: month | total_sales 2023-01-01 | 15000 2023-02-01 | 20000 2023-03-01 | 17500 2023-04-01 | 22000

9. Merge Statements

Merge statements (often referred to as UPSERT or ON DUPLICATE KEY UPDATE) allow for inserting, updating, or deleting records in a target table based on a join with a source table. For example, suppose we want to synchronize two tables that contain customer data.

MERGE INTO customers_target t

USING customers_source s

ON t.customer_id = s.customer_id

WHEN MATCHED THEN

UPDATE SET t.name = s.name, t.email = s.email

WHEN NOT MATCHED THEN

INSERT (customer_id, name, email) VALUES (s.customer_id, s.name, s.email);

Consider the data in the customers_target and customers_source tables.

customers_target (before merge): customer_id | name | email 1 | John Doe | [email protected] 2 | Jane Smith | [email protected] customers_source: customer_id | name | email 2 | Jane Johnson | [email protected] 3 | Alice Brown | [email protected] Output: customers_target (after merge): customer_id | name | email 1 | John Doe | [email protected] 2 | Jane Johnson | [email protected] 3 | Alice Brown | [email protected] The MERGE statement updates the customers_target table based on the data from customers_source. If a customer_id from customers_source matches one in customers_target, the name and email are updated. If there is no match, a new row is created.

10. Case Statements

Case statements enable conditional logic within SQL queries. For instance, we can use a case statement to categorize customers based on their total purchase amounts.

SELECT customer_id,

CASE

WHEN total_purchase_amount >= 1000 THEN 'Platinum'

WHEN total_purchase_amount >= 500 THEN 'Gold'

ELSE 'Silver'

END AS customer_category

FROM (

SELECT customer_id, SUM(order_total) AS total_purchase_amount

FROM orders

GROUP BY customer_id

) AS customer_purchases;

Example data from the orders table: customer_id | order_total 1 | 200 1 | 300 2 | 800 3 | 150 3 | 400 4 | 1200 Output: customer_id | customer_category 1 | Gold 2 | Gold 3 | Silver 4 | Platinum This query categorizes customers according to their total purchase amounts. Customers with total purchases of $1000 or more are classified as 'Platinum', those between $500 and $999 as 'Gold', and those with under $500 as 'Silver'.

11. String Functions

String functions in SQL allow for text data manipulation. For example, we can use the CONCAT() function to join first and last names.

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM employees;

Example data from the employees table: first_name | last_name John | Doe Jane | Smith Alice | Johnson Bob | Brown Output: full_name John Doe Jane Smith Alice Johnson Bob Brown This query concatenates the first_name and last_name fields from the employees table, adding a space in between to create a full_name for each employee.

12. Grouping Sets

Grouping sets enable data aggregation at various levels of granularity in a single query. For instance, we can calculate total sales revenue by both month and year.

SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, SUM(sales_amount) AS total_revenue

FROM sales

GROUP BY GROUPING SETS ((YEAR(order_date), MONTH(order_date)), YEAR(order_date), MONTH(order_date));

Example data from the sales table: order_date | sales_amount 2023-01-15 | 1000 2023-01-20 | 1500 2023-02-10 | 2000 2023-03-05 | 2500 2024-01-10 | 3000 2024-01-20 | 3500 2024-02-25 | 4000 Output: year | month | total_revenue 2023 | 1 | 2500 2023 | 2 | 2000 2023 | 3 | 2500 2024 | 1 | 6500 2024 | 2 | 4000 2023 | NULL | 7000 2024 | NULL | 10500 NULL | 1 | 9000 NULL | 2 | 6000 NULL | 3 | 2500 This query aggregates sales data by year and month, by year only, and by month only using GROUPING SETS. This results in subtotals for each month of each year, overall totals for each year, and overall totals for each month across all years.

13. Cross Joins

Cross joins generate the Cartesian product of two tables, resulting in every possible combination of rows from each table. For example, we could use a cross join to produce all combinations of products and customers.

SELECT p.product_id, p.product_name, c.customer_id, c.customer_name

FROM products p

CROSS JOIN customers c;

Example data for the products and customers tables: products table: product_id | product_name 1 | Product A 2 | Product B customers table: customer_id | customer_name 101 | Customer X 102 | Customer Y Output: product_id | product_name | customer_id | customer_name 1 | Product A | 101 | Customer X 1 | Product A | 102 | Customer Y 2 | Product B | 101 | Customer X 2 | Product B | 102 | Customer Y The query executes a CROSS JOIN between the PRODUCTS and CUSTOMERS tables, producing a Cartesian product where every product is paired with each customer, resulting in all potential combinations.

14. Inline Views

Inline views (or derived tables) allow for creating temporary result sets within a SQL query. For instance, if we want to identify customers whose purchases exceed the average order value.

SELECT customer_id, order_total

FROM (

SELECT customer_id, SUM(order_total) AS order_total

FROM orders

GROUP BY customer_id

) AS customer_orders

WHERE order_total > (

SELECT AVG(order_total) FROM orders

);

Example data from the orders table: customer_id | order_total 1 | 100 1 | 200 2 | 500 3 | 300 3 | 200 4 | 700 This computes the total order for each customer: customer_id | order_total 1 | 300 2 | 500 3 | 500 4 | 700 Then, it calculates the average order total across all orders before filtering customers with total orders exceeding the average.

Output: customer_id | order_total 2 | 500 3 | 500 4 | 700

15. Set Operators

Set operators such as UNION, INTERSECT, and EXCEPT enable the combination of results from two or more queries. For instance, we can utilize the UNION operator to merge results from two queries into a single dataset.

SELECT product_id, product_name FROM products

UNION

SELECT product_id, product_name FROM archived_products;

This query consolidates results from the products and archived_products tables, removing any duplicate entries to create a unified list of product IDs and names. The UNION operator ensures each product appears only once in the final result.

Output: product_id | product_name 1 | Chocolate Bar 2 | Dark Chocolate 3 | Milk Chocolate 4 | White Chocolate 5 | Almond Chocolate

Utilizing these 15 advanced SQL techniques, you can tackle intricate data challenges with precision and efficiency. Regardless of whether you're a data analyst, engineer, or scientist, enhancing your SQL abilities will significantly improve your data management capabilities.

If you found this article helpful, please clap, comment, and subscribe for more data-related content on medium.com.

Happy Data Analysis!