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.