Implementing Lag or Lead Values (Only For Numeric Data) without Using the Lag() or Lead() Window Functions

Author(s): Kamireddy Mahendra

Originally published on Towards AI.

The concept of Range of Records In SQL With Sum or Average

This member-only story is on us. Upgrade to access all of Medium.

Window functions will help us in different ways to find our required data in a few lines of SQL Queries.

Without using window functions, to return our required data, we might be using joins, subqueries, or CTEs, which will make a query so complex.

For example,

Given employees table with several details. Return those employees whose salary is higher or lower than the average of all employees in the entire organization or each department’s average salary.

We can solve this type of problem using window functions easily. Otherwise, we should use joins and subqueries or CTEs. A bit complex. Agree?

Some window functions will allow us to take any range of records while calculating any response we want using the window functions.

Let’s see how we can do that.

Assume we have a sales details table. Now we need to calculate the running total sales for successive three days.

Yes, we can use window functions by considering the range of records to find this type of response.

A Sample code is mentioned in the code block.

select *, sum(sale_amount) over(order by sale_date rows between 1 preceding and 1 following)from sales_table

If you observe in the above code block,… Read the full blog for free on Medium.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI