All language subtitles for 10. LAG and LEAD

Now the next function we have is lag or lead function.

No lag or lead function will give you the value of previous rows in case of leg function or the next

rows in the lead function.

For example.

We have these three columns customer data and revenue.

If I use lack of one in my revenue column, it will give me the value of previous row of revenue column.

For example, the lag of revenue one in second row will give me the value of revenue in the first row

that is hundred.

So I am getting this 100 over here if I am calculating the lag of one on revenue.

Similarly, for the third row, the lag of one is 200.

So I'm getting to 100 over here.

Similarly for this row, the leg value with interval one is 300.

So I'm getting 300 for this row.

The leg of one is 300, so I'm getting 300 over here.

So with leg you can have the previous values of any column.

Similarly with lead, you can have the next values of particular column.

And again, as you can see, if the leg value is not available, such as in this first row, the lag

value is not available since there is nothing about this hundred.

In such cases you will get a null value.

If you look at the syntax in length, you have to give two parameters.

First one is the column for which you want the leg value, and second is the interval interval of the

length.

For example, if you write one, it will just pick the.

Above value if you write to it will pick the value that is two intervals above.

If you write three, it will pick the value that is three intervals.

And similarly, you can use partition by and order by.

Partitioned by to group your data and order by.

To order your data and to specify on which order you want to get the legs.

All leads.

Now let's go back to our page admin and calculate like.

So suppose if in this data I want to calculate the lag values on customer orders.

So for each order, I want to calculate what was the order value of the previous order from that particular

customer.

So suppose if one customer is ordering multiple times, I want to add a column which will specify what

was the order value of the previous order that customer has placed.

So let's see how to do that.

All right, select now we want customer ID.

We want Order.

Date.

Or that I'd.

Sales value.

And then leg.

Of sales value.

I want the previous order.

So leg of one.

Over.

Partition by customer.

So I want to do this analysis on customer bases.

I want to calculate for each customer what was their previous order value.

So that's why partition by customer.

I'd.

And order by.

Order date.

So my orders can be arranged using the order date.

So.

Well, right.

Ordered by order.

Date?

Yes.

Previous.

See, this is the alias.

Similarly, we can also.

The previous order.

I'd.

So I will just copy it.

I will calculate the lag on order ID.

The interval is one partition by order, by order, date and this time write previous order ID.

From.

Murder roll up state.

Let's run this.

You can see.

For some variety.

This 10315.

There are.

Five orders.

This fight.

These orders are arranged by order date since we are mentioned.

Order by order, date and window function.

P have the sales value of each order and we have the leg value stored as previous sales and previous

order.

So for the first row there is no previous order ID, order sales or order.

So we are getting null.

For the second order.

The previous sales amount was 7 to 6, and the previous order ID was this 128055128055.

So we are getting the leg values over here.

If we jump to the next customer for the next customer, the first value is null since there are no previous

order for this particular customer.

So these two columns are null.

And from the next column we are getting the data of previous order, which is sales 16.52 and.

Or that it which is this you can also use.

Different intervals.

So.

If you want to calculate the lag of two, you can also do that.

In that case, your first two rows will be null and in the third row you will get the information of

your first row.

So that's how you can use lag or lead.

Let me show you lead as well.

In lead, you will get the data of next rose.

So.

So here you can see if you are using lead, you will get null for the last row of any particular customer

and in the end, in the other rows you will get the information of next rows.

For example, in first row.

See, the next sales is 29.5.

So you are getting 29.5 where the next order it is 138100.

So you are getting 13100 here.

So that's how you can use lag and lead functions.

Lack functions are very important if you are working with time, series data and lags are usually used

for forecasting as well.