LAG() function in Snowflake?

Explained: LAG() function in Snowflake?

by

Are you looking to find how to use the LAG() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the LAG function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use the LAG() function. I will explain the LAG() function by taking a practical example. So, don’t waste time let’s start a step-by-step guide to understanding what is the LAG() expression.

What is LAG() Function?

The LAG() function helps to extend the delay of an action or operation that needs to be performed. For example, We need to pay the bill amount by the 25th of January, If we apply the LAG for one day then we can pay the amount by the 26th of January( One day will be extended).

How can we use LAG() Function?

The LAG() function is used to extend the delay or fall behind to perform an action.

  • When the Ignore Nulls clause is set, any row whose expression evaluates to the Null is not included when offset rows are counted. If { Ignore | Respect } NULL is not specified then default will be Respect Nulls.
  • Order by partition clause will orders the data within each partition.

Syntax for LAG function in Snowflake

-- Syntax : 

LAG ( Expression, [ Offset, default] ) [ { Ignore | Respect | Nulls } ] 
Over ( [ Partition by Expression-1 ] Order by Expression-2 [ { ASC | DESC } ] )

LAG() Argument Details :

ExpressionString value
OffsetInteger value
DefaultSupports any expression which is compatible with Expression

Expression:

String expression to be returned.

Offset:

The number of rows back from the current row from which to obtain a value. For example, an Offset of 2 returns the expression value with the interval of 2 rows.

Note: Setting a negative offset has the same effect as using the LEAD function.

Default:

This expression is used to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with the expression.

Examples

Full Example for how to make use of LAG() function in Snowflake?

-- Create a table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
	AMOUNT NUMBER(38,0)
);

-- Insert Data

insert into emp(id,name,amount) values 
(1,'Ram',2000),
(2,'RAJU',2500),
(3,'Sameer',1500),
(4,'Sai Kumar',250),
(5,'Naveen Sai',1000),
(6,'Seshi Kumar',13500),
(7,'Rajiv Ram',18000),
(8,NULL,NULL)

-- Applying LAG Function to the table data

select id, name, amount, lag(AMOUNT, 1,0) over (order by id) as LAG_AMOUNT
from EMP1  order by id;

+----+-------------+--------+------------+
| ID | NAME        | AMOUNT | LAG_AMOUNT |
|----+-------------+--------+------------|
|  1 | Ram         |   2000 |          0 |
|  2 | RAJU        |   2500 |       2000 |
|  3 | Sameer      |   1500 |       2500 |
|  4 | Sai Kumar   |    250 |       1500 |
|  5 | Naveen Sai  |   1000 |        250 |
|  6 | Seshi Kumar |  13500 |       1000 |
|  7 | Rajiv Ram   |  18000 |      13500 |
|  8 | NULL        |   NULL |      18000 |
+----+-------------+--------+------------+

The above results show the difference between Amount and Lag Amount in which the amount data will be lag by one day ( lag(AMOUNT, 1,0) ).

When you should use LAG Function in Snowflake?

There are certain use case scenarios when it is recommended to use the LAG function within the Snowflake cloud data warehouse which are as follows:

  • If we want to make the data fall behind the expected data then we can apply the LAG function. For example, we are having id 1, 2 followed by the amounts 1000, 2000. After applying LAG by one offset then the result will be 1,2 id’s followed by the amounts 0, 1000.

Real World Use Case Scenarios for LAG Function in Snowflake

  • The due amount can be paid later by some extension ( LAG ).
  • The order needs to be received at 9 AM but, arrived at 10 AM which is one hour LAG or we can use date lag.

LAG Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about LAG() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar