How to Compare Previous and Current Rows in SQL

One of the easiest ways, to compare this is using the lag function. The lag function will allow you to shift the rows downward so that you can view these rows as one observational row. Here is a simple example of how to use the lag function to shift rows downward. You can Practice FANG SQL Questions.

However, let’s take a quick look at the original table in question so that we can understand the data structure.

select
orderid, 
quantity ,
lag(quantity,1) over(order by orderid) as lag
from orderdetails

We are shifting the quality column downward by 1 with the LAG(quantity,1) function. The OVER(order by orderid) tells the function to perform the function over the order id and preserve the structure of this. So our result will be in the table below:

Feel free to watch the video of how this is done on YouTube.

Other Useful SQL Tutorials

Find Outliers in SQL

9 SQL Questions and Answers