SQL Concepts: Window Functions, Joins, CTEs, and Subqueries

Introduction

• Relational databases store data across multiple related tables therefore, answering business questions means combining and analyzing several tables at once. SQL offers four core tools for this namely window functions, joins, CTEs and subqueries. This report explains each tool with practical examples.

1. Window Functions

• They help to perform calculations such as aggregations on a specific set of related rows know as "the window" and return a result for each row without loosing the level of details of each row.
• It is similar to the group by function but the key difference is that for window functions, the rows are not collapsed hence, the granularity does not change.
• The window functions can be group into 3 that is:
  • Aggregate functions - perform a calculation on a set of rows and return a single summary value.
  • Rank functions - assign a position (rank) to each row within a partition.
  • Value (analytical) functions - return a value from another row in the same partition, relative to the current row. They are used to compare a row with its neighbors. The diagram above shows the three main window functions with their appropriate datatypes

Over Clause()

• It tells SQL that the function used is a window function and defines a window or subset of data.

Partition By

• It is written inside the over clause and is used to divide the dataset(rows) into windows/groups based on the columns.
• This clause enables you to perform aggregate calculations such as Sum() based on different categories.
• The example below shows how partition by() is used to calculate the total sales for each book, grouped by book_id, while keeping every individual order in the result Result

Order By

• It helps in sorting data within a window either in ascending or descending.

Window frame

• It defines the subset of rows within a partition that a window function uses to calculate the result for the current row. The frame moves as the current row changes, that is like having a window within a window.
• Here the frame clause can only be used together with the Order by() clause and the lower value must be below the higher value.

a. Aggregate functions

• They perform a calculation on a set of rows and return a single summary value.
• When an aggregate function is followed by Over(), it becomes a window function. It calculates over a window of rows but returns a value for every row instead of collapsing them. Examples of the aggregate functions are:
  • Sum()- calculates the group total
  • Avg()- calculates the group average
  • Count()- counts rows or non null values
  • Min()- finds the lowest value in the window
  • Max()- finds the highest value in the window Result

b. Rank functions

• They assign a position (rank) to each row within a partition based on the Order by() inside Over(). Every row is kept and each receives a rank value.
• The rank functions include;
  • Row_number()- assigns a unique number to every row.
  • Rank()- assigns a rank to each row while assigning the same rank to tied values then skips subsequent ranks.
  • Dense_rank()- assigns a rank to each row while assigning the same rank to tied values but does not skip ranks.
  • Ntile(n)- divides rows into a specified number of approximately equal groups called buckets. Result

c. Value(analytical) functions

• They return a value from another row in the same partition, relative to the current row. It is mainly in comparing values.
• The value functions include;
  • Lead()- allows you to access a value from the next row within a window.
  • Lag()- allows you to access a value from the previous row within a window.
  • First_value()- allows you to access a value from the first row within a window.
  • Last_value()- allows you to access a value from the first row within a window. For this, you cannot use the default window frame, you have to customize the frame ie "rows between current rows and unbounded following"

-Lead and Lag can be used in time series analysis ie Year over year(YoY) and Month over month(MoM) as well as to perform a customer retention analysis.

Advantages of Window Functions

1. Keeps the row level detail - return a result for every row instead of collapsing rows like in group by.
2. Flexible grouping - different functions in one query can use different partitions and orderings hence, allowing you to add more information on the select statement without having errors.
3. Advanced analytics - supports running totals, moving averages, rankings, and period over period comparisons.

Limitations of the Window Functions

1. They can only be used in the Select() and Order By() clauses.
2. They cannot be used to filter data using the Where() clause that is, the two clauses cannot be used in the same query.
3. Nested window functions are not allowed that is, you cannot have one window function inside another one.

2. Joins

• A join combines rows from two or more tables based on a related column or a specified condition. This is due to a relationship between them, usually a foreign key matching a primary key.
• I will use a bookstore database containing the following tables to demonstrate this concept:
  • Customers_table- stores customer information
  • Books_table- stores books information
  • Orders_table-links the other two, recording which customer purchased which book.

Inner Join

• It returns only the rows that have matching values in both tables according to the join condition.
• List only the customers who have purchased books in the orders table and are also in the customers table.
• Use: for sales reports, order confirmations and invoices, where only real purchases matter.

Left Join

• It returns all records from the left table and any matching records from the right table.
• Lists all customers in the customers table even those who have not made any purchase since, and matching in the orders table
• Use: when business wants to identify all its customers including those who have never purchased anything.

Right Join

• It returns all records from the right table and matching records from the left table
• List all customers who have made a purchase in the orders table including those are not in the customers table.
• Use: when a business wants to list every order in the orders table even when customer information is missing or does not match.

Full Outer Join

• It returns all matching records plus unmatched records from both tables
• Lists all customers in the customers table even those who have not made any purchase and also orders in the in the orders table with no customer records.
• Use: when a company is reconciling customer records from two systems and wants to identify records that match, as well as those that exist in only one system.

Cross Join

• It returns every possible combination of rows from two tables that each row in the first table is paired up with each row of the second table.
• In the above query, the cross join will multiply the number of customers with the number of orders.
• Use: when preparing planning scenarios where every possible combination must be considered.

Self Join

• It is used when a table needs to be joined to itself. It allows you to compare or connect records within the same table.
• Use: when analyzing hierarchical relationships within an organization.

3. Common Table Expressions (CTEs)

• A Common Table Expression (CTE) is a temporary named result set that you define using the With() clause and reference within a single SQL statement.
• It helps break complex queries into smaller, more readable steps.

Basic CTE

• It defines a query result, gives it a name and then uses that name in the main query.

Example: List the customers who reside in Nairobi

• The CTE Nairobi_Customers selects customers whose city is Nairobi.
• The main query retrieves the rows returned by the CTE.
• The result contains only customers from Nairobi.

Multiple CTEs

• Several CTEs are separated with commas using a single With() clause. Later the CTEs can reference earlier ones.
• Let's use the bookstore database to demonstrate this:

Example :Finding customers whose total spending exceeds 6,000

• First CTE is Customer_Spending which calculates the total amount spent by each customer.
• Second CTE is High_Value_Customers which filters the first CTE to keep customers whose spending exceeds or equals 6,000.
• Main query displays the high value customers and their total spending.

Recursive CTEs

• Is a CTE that references itself to process data repeatedly until a stopping condition is reached.
• It has two main parts:
  • Anchor member which is the initial query that identifies the starting rows.
  • Recursive member which is the query that references the CTE and finds the next level of related rows. These parts are commonly combined using Union All
• It is commonly used to analyze hierarchical data such as organizational structures.

4. Subqueries

• Also know as inner query is a sql query nested inside another sql statement. The inner query produces a result that the outer query uses as a value or table.
• I will still be using the bookstore database to discuss on the different types of subqueries.

Scalar subqueries

• It returns a single value that is one row and one column otherwise the database returns an error.

Example: Find customers who have spent more than the average sales total

• The inner query calculates the average spending amount of all customers which is ksh 6,228.5
• The outer query compares each customer's spending with that average.
• Only customers who have spent more than the average are returned.

Non-correlated subqueries

• It runs independently of the outer query and does not reference columns from the outer query.
  • The outer query lists names of customers who have purchases while the inner query lists their respective customer_ids, the two queries run independently.

Correlated subqueries

• It references a column from the outer query and its result depends on the current outer row.

Example: We want to find the orders larger than the customer’s usual:

• The query returns customer with customer_id 3 because their average order quantity is 2 and they twice purchased 3 books, exceeding that average.
  • Use :when comparing each employees salary with their department's average, when identifying products priced above the average for their category.

Nested subqueries

• Is a subquery inside another subquery.

Example: We want to find customers who bought a book written by "Susan Njeri".

• Evaluation goes from the inside out. First from books table we get book 1 and 9 then orders table gives customers 2 and 3, and lastly customers table gives Peter and Mary as the customers who have purchased books by the author Susan Njeri.
  • Use: When a question requires multiple stages of filtering. and when one query depends on the result of another query.

Using subqueries in Select, From, and Where clauses

Subqueries in the Select clause

• Can calculate a value to display alongside the main query's results that is it adds a calculated column to each row.

Example: Find book's prices and the overall average books' price

Results

• The subquery calculates the overall average price of all books and the average_price is the alias for the calculated column.

Subqueries in the From clause

• It creates a derived table. This lets you treat the result of an inner query as a temporary table for further analysis.

Example: Find the average amount spent per customer.

• The result is ksh 8220 which is amount spent per customer

Subqueries in the Where clause

• It filters records according to a condition involving another query.
• It is them most common among the three.

Comparison

• The table below highlights the difference between subqueries and CTEs

Conclusion

• In conclusion, window functions, JOINs, CTEs, and subqueries are important SQL concepts used to retrieve, combine, and analyze data effectively. Understanding these concepts helps in writing clear, efficient, and accurate queries. They are essential skills for data analysis, reporting, and database management.