Hello Data Bees! Welcome to Analytics Arena!!

Advanced SQL for Business Analysis: Taking Your Skills to the Next Level

Welcome back, Data Enthusiasts! In our last post, we explored the foundational SQL concepts like JOINS, subqueries, and CASE statements - tools essential for any business/data analyst. If you’ve been practicing, you’re probably feeling more confident in navigating your datasets. Now, it's time to level up! In this post, we’ll delve into more advanced SQL topics, such as window functions, CTEs (Common Table Expressions), stored procedures and triggers and events. These advanced features will enable you to tackle even more complex data problems and streamline your queries for efficient analysis. Let’s keep building those SQL skills to empower your data analysis journey!

Window Functions:

Window functions in SQL are an advanced tool that allow you to perform calculations across a set of table rows related to the current row. Unlike aggregate functions, which group rows into a single result, window functions retain the individual rows while still allowing for complex calculations.

These functions are incredibly powerful in business analysis, as they allow you to perform tasks like ranking, running totals, and moving averages without collapsing data into groups.

Syntax:

<window_function>() OVER ([PARTITION BY <column>] [ORDER BY <column>])

You can use PARTITION BY to divide the dataset into groups and ORDER BY to define the logical order of rows within each partition.

Common Window Functions:

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE()

LEAD() and LAG()

SUM(), AVG(), MIN(), MAX() (as window functions)

Example 1: ROW_NUMBER()

Suppose you have a sales table and want to assign a unique rank to each sale within its respective region.

SELECT sales_id, region, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

This assigns a unique row number to each sale within each region, ordered by the sales amount.

Example 2: RANK() vs. DENSE_RANK()

Let’s rank employees by salary, but with handling ties differently.

SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

RANK() will skip numbers if there’s a tie (e.g., 1, 2, 2, 4).
DENSE_RANK() will not skip numbers for ties (e.g., 1, 2, 2, 3).

Example 3: Running Totals with SUM()

Window functions are great for calculating running totals, which can be useful for analyzing cumulative sales over time.

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

Here, each row shows the total sum of all previous amount values up to the current row, ordered by order_date.

Example 4: LEAD() and LAG()

These functions allow you to access data from subsequent or preceding rows without using a self-join.

LEAD(): Fetches the value of a subsequent row.

LAG(): Fetches the value of a preceding row.

Example, if you want to compare each sale to the previous one:

SELECT sales_id, amount,
LAG(amount) OVER (ORDER BY sales_id) AS previous_sale
FROM sales;

This shows the sales amount from the previous row, allowing you to calculate the difference between consecutive sales.

CTEs (Common Table Expressions):

Common Table Expressions (CTEs) in SQL: Simplifying Complex Queries

Common Table Expressions (CTEs) are temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. They are especially useful for breaking down complex queries into more readable, manageable parts. Unlike subqueries, CTEs can be recursive, which allows for more advanced operations such as hierarchical data traversal.

CTEs enhance code readability and make it easier to maintain large SQL scripts, especially when you need to reuse a temporary result multiple times in a query.

Syntax:

WITH cte_name AS (

-- Your query here

)

SELECT * FROM cte_name;

Example: Simplifying Queries

Let’s say you want to find the average salary by department and then select employees earning above the average in their department. Without a CTE, this could be a bit messy, but with a CTE, it becomes much cleaner.

WITH avg_salary_by_dept AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.employee_id, e.salary, d.avg_salary
FROM employees e
JOIN avg_salary_by_dept d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Here, the CTE avg_salary_by_dept calculates the average salary per department, which is then used in the main query to find employees earning above that average.

CTEs vs Subqueries

• CTEs are more readable than subqueries, especially when dealing with complex operations or multiple references to the same temporary result.
• CTEs allow recursive queries, while subqueries do not.
• CTEs are typically easier to debug and maintain due to their structured, named component

Temp Tables:

Temporary tables in SQL allow you to store intermediate results for further manipulation within the same session. These tables act like regular tables, but they are created temporarily and automatically dropped when the session ends or the user explicitly drops them. They are especially useful for breaking down complex queries or performing multiple steps of data processing when handling large datasets.

Temporary tables are often used in complex business analysis where data needs to be pre-processed before applying further calculations, aggregations, or joins.

Syntax:

To create a temporary table:

CREATE TEMPORARY TABLE temp_table_name AS
SELECT * FROM original_table WHERE condition;

Temporary tables can be created using the CREATE TEMPORARY TABLE statement, followed by a query that populates it.

Example: Simple Temporary Table Creation

Let’s say you want to create a temporary table for customers who made purchases above $1,000. This allows you to focus only on these high-value customers in subsequent queries.

CREATE TEMPORARY TABLE high_value_customers AS
SELECT customer_id, name, total_purchase
FROM customers
WHERE total_purchase > 1000;

Now, you can run additional queries on the high_value_customers table, such as calculating the total revenue from these customers:

SELECT SUM(total_purchase) AS total_revenue
FROM high_value_customers;

Stored Procedures:

Stored Procedures in SQL: Automating Repetitive Tasks

Stored procedures are a powerful feature in SQL that allow you to encapsulate SQL queries and logic into a single unit. A stored procedure is a set of SQL statements that can be stored in the database and executed repeatedly. They are especially useful for automating repetitive tasks, complex operations, and standardizing business logic across an organization.

Stored procedures not only improve code reuse but also enhance security and performance, as they allow you to pre-compile and optimize frequently used queries.

Why Use Stored Procedures?

Code Reusability: You can create a stored procedure once and reuse it whenever needed.

Improved Performance: Stored procedures are precompiled, which reduces query execution time.

Security: You can grant users access to execute stored procedures without giving them direct access to the underlying tables.

Maintainability: Centralized business logic makes it easier to update and maintain code, especially for complex queries.

Syntax:

Here’s the basic syntax for creating a stored procedure:

CREATE PROCEDURE procedure_name
AS
BEGIN
-- SQL statements go here
END;

To execute a stored procedure:

EXEC procedure_name;

Example: Simple Stored Procedure

Let’s say you frequently run a query to get the total number of orders placed by customers. Instead of typing out the query each time, you can create a stored procedure:

CREATE PROCEDURE GetTotalOrders
AS
BEGIN
SELECT COUNT(*) AS total_orders FROM orders;
END;

Now, whenever you want to retrieve the total number of orders, you can simply execute the stored procedure:

EXEC GetTotalOrders;

Triggers and Events:

Triggers and events are two powerful features in SQL that allow you to automate actions within a database based on certain conditions or time-based schedules. They can enhance your database’s functionality by executing pre-defined tasks in response to changes or scheduled events.

Triggers: Automatically execute SQL code when a specific change (like INSERT, UPDATE, or DELETE) occurs in a table.

Events: Allow you to schedule SQL statements or tasks to run automatically at a specified time or recurring intervals.

Both are vital tools for managing data integrity, logging, and automation in database-driven applications.

Triggers in SQL

A trigger is a special kind of stored procedure that automatically executes when a specific event occurs in a database, such as a row being inserted or updated. Triggers are often used to enforce business rules, audit changes, or maintain data integrity by automating actions like validation or logging.

Why Use Triggers?

Data Integrity: Ensure certain conditions are met before or after data is inserted, updated, or deleted.

Auditing: Automatically track changes to important data (e.g., logging modifications).

Automation: Automatically execute SQL statements in response to changes in data without manual intervention.

Syntax:

Here’s the basic syntax for creating a trigger:

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements to execute when the trigger is fired
END;

Example: Trigger for Auditing

Let’s create a trigger to track any changes made to the employees table by logging updates to a separate employee_audit table:

CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, old_salary, new_salary, change_date)
VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;

In this example, whenever an employee’s salary is updated, the trigger logs the changes in the employee_audit table.

Events in SQL

Events in SQL allow you to schedule SQL statements or tasks to run at a specific time or on a recurring schedule. They are useful for performing database maintenance, periodic data updates, or scheduled report generation.

Why Use Events?

Automated Maintenance: Schedule tasks like cleaning up old records, recalculating aggregates, or refreshing data.

Recurring Reports: Automatically generate reports or backup data at specific intervals.

Task Automation: Automate repetitive tasks to run without manual intervention.

Syntax:

To create an event, use the following syntax:

CREATE EVENT event_name
ON SCHEDULE AT 'YYYY-MM-DD HH:MM:SS' -- For one-time events
DO
-- SQL statements to execute;

For recurring events:

CREATE EVENT event_name
ON SCHEDULE EVERY interval_value INTERVAL interval_type
DO
-- SQL statements to execute;

Example: One-Time Event

You can create a one-time event to delete old records from a table at a specific date and time:

CREATE EVENT cleanup_old_data
ON SCHEDULE AT '2024-10-01 00:00:00'
DO
DELETE FROM orders WHERE order_date < '2023-01-01';

This event will run on October 1st, 2024, and delete any orders older than January 1st, 2023.