LAG() Window Function — with examples in PostgreSQL.

Having recently covered the LEAD()Window Function here on Digital Owl’s Prose, it only makes sense to follow-up with a post on its counterpart: LAG(). By all means, don’t lag behind. Read on and learn something with me…

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

• Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
• PostgreSQL 11.2

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

I’ll use a couple of tables from the PostgreSQL practice DVD Rental database for the example queries below.

Throughout this post, let’s assume we have this CTE that returns the below query results:

WITH valued_customers AS (
SELECT
 c.first_name AS first_name,
 c.last_name AS last_name,
 SUM(p.amount) AS total_spent
 FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id
GROUP by c.first_name, c.last_name
HAVING SUM(p.amount) > 150)
SELECT * FROM valued_customers;
 first_name  | last_name  |…