Is there any way to subtract a row with the previous one in sql?

Question

I'm trying to do an accumulated subtraction: I have the months and I need to subtract a month from the previous month to know if the value was greater or not.

Basically the logic is as follows:

    Month.    | Value 
    January   | 1000 
    February  | 1500
    March     | 1200 
    April.    | 2500 
    May.      | 1000 
    June.     | 5000

Query result must be:

    Month.    | Value 
    January.  | 1000
     February | 500
     March    | -300
     April.   | 1300
     May.     | -1500 
    June.     | 4000

Can someone help me? I'm using redshift.

Answer 1

First, you’ll need to build a CTE using the WITH keyword to explicitly define how Redshift should sort your months relative to one another, since it doesn’t know out of the box what each string actually represents. (This becomes even simpler if you do have a properly-typed date attribute in your table that can be compared to each other out of the box instead of arbitrary string data.)

Using that CTE, you can JOIN it to the table in question, then use the LAG window function to grab the "previous" month’s Value to calculate against the current row’s.

Since you didn’t include it in your question, the following example assumes the source table you gave in your example is named Monthly_Sales:

WITH Months AS (
    SELECT "January" AS Month, 0 AS Sequence
    UNION ALL SELECT "February", 1
    UNION ALL SELECT "March", 2
    UNION ALL SELECT "April", 3
    UNION ALL SELECT "May", 4
    UNION ALL SELECT "June", 5
    UNION ALL SELECT "July", 6
    UNION ALL SELECT "August", 7
    UNION ALL SELECT "September", 8
    UNION ALL SELECT "October", 9
    UNION ALL SELECT "November", 10
    UNION ALL SELECT "December", 11
)
SELECT
    Monthly_Sales.Month,
    LAG(Monthly_Sales.Value, 1) OVER(ORDER BY Months.Sequence ASC) - Monthly_Sales.Value AS Value
FROM Monthly_Sales
JOIN Months ON Monthly_Sales.Month = Months.Month
ORDER BY Months.Sequence ASC