@waruikelvin

Posted on Mar 2

SQL Beyond the Basics: Mastering Joins and Window Functions

#luxdevhq #sql #postgres #analytics

Introduction

SQL mastery comes from diving into advanced concepts that allow you to query, manage, and optimize databases with precision. While these concepts may add a layer of complexity to your existing data management and analysis skills, learning to apply them is both rewarding and essential for anyone serious about becoming a power user in SQL.

Today, we’ll explore two of those powerful concepts: Window Functions and Joins.

Let’s dive in.

SQL Window Functions

What Are SQL Window Functions?

SQL window functions are calculation functions that perform operations across a set of table rows defined by an OVER() clause.

They are similar to aggregate functions. However, unlike traditional aggregate operations such as GROUP BY, window functions do not collapse rows into a single result. Instead, they retain individual rows while performing calculations across a defined window of data.

Window functions use values from one or multiple rows to return a value for each row. This is what makes them different from traditional aggregate functions, which return a single value for multiple rows.

General Syntax

SELECT column_1, column_2, column_3,
       function() OVER (
           PARTITION BY partition_expression
           ORDER BY order_expression
       ) AS output_column_name
FROM table_name;

Explanation

function() → The window function (e.g., ROW_NUMBER, RANK, SUM)
OVER() → Defines how the window is constructed
PARTITION BY → Splits rows into groups
ORDER BY → Defines the logical order of rows inside each partition
AS output_column_name → Alias for the calculated result

Important Note: Window functions are processed after WHERE, GROUP BY, and HAVING, but before the final ORDER BY. This means you cannot use a window function directly in a WHERE clause. You must use a subquery or CTE instead.

Dataset Used in This Article

For demonstration purposes, we’ll use the following practice tables.

Students Table

CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50),
math_score FLOAT,
english_score FLOAT
);

INSERT INTO students (student_id, first_name, last_name, gender, math_score, english_score)
VALUES
(1, 'Sascha', 'Bolden', 'Female', 99.65, 86.72),
(2, 'Pyotr', 'Pepperd', 'Male', 14.72, 81.92),
(3, 'Konrad', 'Benedite', 'Male', 24.16, 35.11),
(4, 'Eduino', 'Habershaw', 'Male', 63.27, 54.66),
(5, 'Henka', 'Grellier', 'Female', 99.96, 65.71),
(6, 'Devonna', 'Cesaric', 'Female', 49.18, 93.29),
(7, 'Reggy', 'Marson', 'Male', 73.1, 19.25),
(8, 'Richmond', 'Kennealy', 'Male', 54.66, 50.52),
(9, 'Jo', 'Sea', 'Female', 52.1, 74.27),
(10, 'Gerard', 'Murphey', 'Male', 2.3, 77.36);

Stream table

CREATE TABLE stream (
student_id INT, 
class_stream VARCHAR(50)
);

INSERT INTO stream (student_id, class_stream)
VALUES
(2, 'North'),
(8, 'South'),
(3, 'East'),
(7, 'North'),
(5, 'North'),
(6, 'South'),
(7, 'East'),
(11, 'West'),
(9, 'North'),
(10, 'East');

Class Stream table

CREATE TABLE class_stream (
student_id INT, 
class_stream VARCHAR(50)
);

INSERT INTO class_stream (student_id, class_stream)
VALUES
(1, 'North'),
(2, 'South'),
(3, 'East'),
(4, 'West'),
(5, 'North'),
(6, 'South'),
(7, 'East'),
(8, 'West'),
(9, 'North'),
(10, 'East');

NOTE: The datasets used here are purely for practice purposes.

Types of SQL Window Functions

Window functions can generally be grouped into three categories:

Aggregate Window Functions
Ranking Window Functions
Value Window Functions

1. Aggregate Window Functions

Examples include:

AVG()
MAX()
MIN()
SUM()
COUNT()
STDEV()
VAR()

These functions perform calculations across a window of rows without collapsing the result set.

2. Ranking Window Functions

ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()

These assign ranking positions to rows based on specified ordering logic.

3. Value Window Functions

LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
CUME_DIST()

These functions allow comparisons between rows within a defined window.

Practical Window Function Examples

1. ROW_NUMBER()

Assigns a unique sequential number to each row.

SELECT first_name, last_name, math_score,
       ROW_NUMBER() OVER (ORDER BY math_score DESC) AS row_number
FROM students;

Each student receives a unique ranking based on their math score.

2. RANK()

Assigns ranks with gaps when ties occur.

SELECT first_name, last_name, math_score,
       RANK() OVER (ORDER BY math_score DESC) AS rank
FROM students;

If two students tie, they receive the same rank, and the next rank is skipped.

3. DENSE_RANK()

Similar to RANK() but without gaps.

SELECT first_name, last_name, math_score,
       DENSE_RANK() OVER (ORDER BY math_score DESC) AS dense_rank
FROM students;

4. PERCENT_RANK()

Calculates the relative rank between 0 and 1.

SELECT first_name, last_name, english_score,
       ROUND(PERCENT_RANK() OVER (ORDER BY english_score DESC)::numeric, 2)
       AS percentile_rank
FROM students;

5. SUM() with PARTITION (Running Total)

SELECT student_id, first_name, last_name, gender, english_score,
       SUM(english_score) OVER (
           PARTITION BY gender
           ORDER BY student_id
       ) AS cumulative_scores
FROM students;

PARTITION BY gender divides the rows into groups.
ORDER BY student_id defines the order inside each partition.
SUM() calculates the running total.

SQL Joins

Joins combine rows from two or more tables based on related columns.
Understanding joins is essential when working with normalized databases and relational systems.

Types of Joins

Inner Join
Left Join
Right Join
Full Outer Join
Cross Join
Self Join

1. Inner Join

Returns only matching rows from both tables.
students

Stream

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
INNER JOIN stream c
ON s.student_id = c.student_id;

Students without a matching stream are excluded.

2. Left Join

Returns all rows from the left table.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
LEFT JOIN stream c
ON s.student_id = c.student_id;

Students without a stream appear with NULL.

3. Right Join

Returns all rows from the right table.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
RIGHT JOIN stream c
ON s.student_id = c.student_id;

Streams without students appear with NULL values.

4. Full Outer Join

Returns all rows from both tables.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
FULL OUTER JOIN stream c
ON s.student_id = c.student_id;

Unmatched rows from either side are included.

5. Cross Join

Returns every possible combination of rows.

SELECT s.first_name, s.last_name, c.class_stream
FROM students s
CROSS JOIN class_stream c;

This produces a Cartesian product.

6. Self Join

A self join joins a table to itself.

SELECT 
    s.student_id,
    s.first_name AS student_first_name,
    s.last_name AS student_last_name,
    t.student_id AS teacher_id,
    t.first_name AS teacher_first_name,
    t.last_name AS teacher_last_name
FROM students s
LEFT JOIN students t
    ON s.teacher_id = t.student_id
ORDER BY s.student_id;

This allows us to relate rows within the same table.

Complete SQL Queries Used in This Article

Below is a structure you can use to group all queries together for readers who want to test everything at once:

-- Create students table
-- Insert student data
-- Create stream table
-- Insert stream data
-- Window function examples
-- Join examples
-- Self join example

Providing a complete runnable script makes your article more beginner-friendly and practical.

Conclusion

Window functions and joins are two of the most powerful tools in SQL.

Window functions allow you to perform advanced analytical calculations without collapsing rows, making them ideal for ranking, running totals, and row comparisons.

Joins allow you to connect related tables and extract meaningful insights from structured relational databases.

When combined, these concepts unlock a new level of SQL capability, enabling you to build reports, perform advanced analytics, and solve real-world database problems efficiently.

Mastering them takes practice, but once you understand how they work, your confidence and SQL problem-solving skills will increase significantly.

Keep practicing. Keep querying!

DEV Community