Database Essentials - Writing Advanced SQL Queries - Analytic Functions - Windowing

In this article, we will explore various Windowing functions supported by Postgres and understand how they can be used to derive valuable insights from the data.

Key Concepts Explanation

lead and lag

The lead and lag functions are used to get column values from following or prior records. These functions operate based on the ORDER BY specified within the OVER clause. Let’s take a look at the examples:

    lead(order_date) OVER (ORDER BY order_date DESC) AS prior_date,
    lead(revenue) OVER (ORDER BY order_date DESC) AS prior_revenue,
    lag(order_date) OVER (ORDER BY order_date) AS lag_prior_date,
    lag(revenue) OVER (ORDER BY order_date) AS lag_prior_revenue
FROM daily_revenue AS t
ORDER BY order_date DESC

first_value and last_value

These functions are used to get the first and last values of a specified column based on the criteria defined in the PARTITION BY and ORDER BY clauses. Let’s see an example using first_value:

    first_value(order_item_product_id) OVER (PARTITION BY order_date ORDER BY revenue DESC) AS first_product_id,
    first_value(revenue) OVER (PARTITION BY order_date ORDER BY revenue DESC) AS first_revenue,
    max(revenue) OVER (PARTITION BY order_date) AS max_revenue
FROM daily_product_revenue t
ORDER BY order_date, revenue DESC

Hands-On Tasks

  1. Perform a lead and lag analysis on the dataset daily_revenue to understand the shifting of values.
  2. Calculate the first and last values of product IDs and revenues based on the order_date from the dataset daily_product_revenue.


In this article, we have covered the fundamental Windowing functions in Postgres such as lead, lag, first_value, and last_value. These functions play a crucial role in analytical queries to derive meaningful insights from the data. Make sure to practice these concepts by applying them to your own datasets and feel free to engage with the community for further learning opportunities. Happy coding!

Click here to watch the associated video on Windowing Functions in Postgres

Remember, practice makes perfect! So don’t hesitate to dive into the code examples and experiment with the concepts discussed herein. Have fun exploring the world of Windowing functions!

Watch the video tutorial here