Unlocking the Power of PostgreSQL Window Functions Using DVD rental Dataset
If you've ever needed to rank top customers, calculate monthly revenue trends, or find the first rental of each film, you’ve probably bumped into a challenge: how to do this without aggregating away your details.
That’s where window functions in PostgreSQL come in. They allow you to perform analytics across a set of rows related to the current row, without losing the full granularity of your data.
In this blog, we’ll explore window functions using the DVD Rental dataset — a perfect playground for SQL learners and analysts alike.
What Are Window Functions?
A window function performs a calculation across a set of rows that are related to the current row — a "window" of rows. Unlike aggregate functions (SUM, AVG, etc.), window functions don’t collapse rows — they return a value for each row.
Basic Syntax:
function_name(arguments) OVER (
PARTITION BY column
ORDER BY column
ROWS BETWEEN ... AND ...
)
Common Window Functions
Let’s go over some popular ones with examples.
1. ROW_NUMBER() – Number of Rentals Per Customer
Want to track the order in which each customer made their rentals?
SELECT
customer_id,
rental_id,
rental_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY rental_date
) AS rental_number
FROM rental;
2. RANK() – Top-Spending Customers
Rank customers by total amount spent.
SELECT
customer_id,
SUM(amount) AS total_spent,
RANK() OVER (
ORDER BY SUM(amount) DESC
) AS spend_rank
FROM payment
GROUP BY customer_id;
3. FIRST_VALUE() / LAST_VALUE()
First Film Rented by Each Customer
SELECT DISTINCT c.customer_id,
FIRST_VALUE(f.title) OVER (
PARTITION BY c.customer_id
ORDER BY r.rental_date
) AS first_film
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id;
4. NTILE(n) – Customer Quartiles by Spending
SELECT customer_id,
SUM(amount) AS total_paid,
NTILE(4) OVER (
ORDER BY SUM(amount)
) AS spend_quartile
FROM payment
GROUP BY customer_id;
5. Running Total – Cumulative Monthly Revenue
SELECT
DATE_TRUNC('month', payment_date) AS month,
SUM(amount) AS monthly_revenue,
SUM(SUM(amount)) OVER (
ORDER BY DATE_TRUNC('month', payment_date)
) AS cumulative_revenue
FROM payment
GROUP BY month
ORDER BY month;
Breakdown of OVER() Clause
Clause | What it does |
PARTITION BY | Groups rows for window calculation |
ORDER BY | Determines row order inside each partition |
ROWS BETWEEN | Defines frame for moving window (optional) |
Why Window Functions Matter
With window functions, you can:
Analyze time-based patterns
Create rankings and leaderboards
Implement customer segmentation
Build trend dashboards for business teams
They're more powerful than aggregates and more efficient than subqueries for many analytics problems.
Conclusion:
PostgreSQL window functions are your best friend when working with structured, time-stamped, or transaction-level data like that in the DVD Rental dataset. Whether you're building customer insights, operational KPIs, or revenue reports — they give you analytical superpowers with just a few lines of SQL.
