Get sum of difference of rows in sql

November 7, 2022

I have a table with given values

| id  | level  | value |
| --- | ------ | ----- |
| 1   | 30000  | 0.05  |
| 2   | 100000 | 0.06  |
| 3   | 120000 | 0.07  |

I want to create an postgres sql query to get a sum in the following logic. I will be providing a value (level) as as parameter to the query (170000).

(100000- 30000)*0.05 + (120000-100000)*0.06 + (170000-120000)*0.07

(difference of level of row2 and row1) * value of row1 +
(difference of level of row3 and row2) * value of row2 +
(difference of level as input and row3) * value of row3

>Solution :

Use LEAD to see the next row’s value. Use COALESCE to substitute the last missing value with 170000.

select sum(subtotal) as total
from
(
  select
    (coalesce(lead(level) over (order by id), 170000) - level) * value as subtotal
  from mytable
) subtotals;