Add an extra column in sql to show the relationship with the previous row

Question

I have an SQL table with this format:

SELECT period_id, amount FROM table;
+--------------------+
| period_id | amount |
+-----------+--------+
| 1         |    12  |
| 2         |    11  |
| 3         |    15  |
| 4         |    20  |
| ..        |    ..  |
+-----------+--------+

I would like to add an extra column (only in my select statement) that calculates the growth rate with the previous amount, for example:

SELECT period_id, amount, [insert formula here] AS growth FROM table;
+-----------------------------+
| period_id | amount | growth |
+-----------+-----------------+
| 1         |    12  |        |
| 2         |    11  |   0.91 |  <-- 11/12
| 3         |    15  |   1.36 |  <-- 15/11
| 4         |    20  |   1.33 |  <-- 20/15
| ..        |    ..  |     .. |
+-----------+-----------------+

You just need to figure out how to perform the operation on the line earlier. Not interesting to add to the table. Any help appreciated :)

** I also want to indicate that period_id is ordered, but does not necessarily increase gradually

Answer 1

The Lag () window function will be good here.

, (amount+0.0). , AMOUNT INT NullIf(),

Declare @YourTable table (period_id int,amount int)
Insert Into @YourTable values
( 1,12),
( 2,11),
( 3,15),
( 4,20)


Select period_id
      ,amount
      ,growth  = cast((amount+0.0) / NullIf(lag(amount,1) over (Order By Period_ID),0) as decimal(10,2))
 From  @YourTable

period_id   amount  growth
1           12      NULL
2           11      0.92
3           15      1.36
4           20      1.33

Answer 2

SQL Server 2012+, John Cappelletti.

, , 2008 .

Declare @YourTable table (period_id int,amount int)
Insert Into @YourTable values
( 1,12),
( 2,11),
( 3,15),
( 4,20)

;WITH CTE AS (
    SELECT ROW_NUMBER() OVER (
            ORDER BY period_id
            ) SNO
        ,period_id
        ,amount
    FROM @YourTable
    )
SELECT C1.period_id
    ,C1.amount
    ,CASE 
        WHEN C2.amount IS NOT NULL AND C2.amount<>0
            THEN CAST(C1.amount / CAST(C2.amount AS FLOAT) AS DECIMAL(18, 2))
        END AS growth
FROM CTE C1
LEFT JOIN CTE C2 ON C1.SNO = C2.SNO + 1

, LAG.

+-----------+--------+--------+
| period_id | amount | growth |
+-----------+--------+--------+
|         1 |     12 | NULL   |
|         2 |     11 | 0.92   |
|         3 |     15 | 1.36   |
|         4 |     20 | 1.33   |
+-----------+--------+--------+