How to minus by period in another column

Question

I need results in minus column like:

For example, we take first result by A = 23(1)

and we 34(2) - 23(1) = 11, then 23(3) - 23(1)...

And so on. For each category.

+--------+----------+--------+-------+
| Period | Category | Result | Minus |
+--------+----------+--------+-------+
|      1 | A        |     23 | n/a   |
|      1 | B        |     24 | n/a   |
|      1 | C        |     25 | n/a   |
|      2 | A        |     34 | 11    |
|      2 | B        |     23 | -1    |
|      2 | C        |      1 | -24   |
|      3 | A        |     23 | 0     |
|      3 | B        |     90 | 66    |
|      3 | C        |     21 | -4    |
+--------+----------+--------+-------+

Could you help me?

Could we use partitions or lead here?

Answer 1

SELECT
    *,
    Result - FIRST_VALUE(Result) OVER (PARTITION BY Category ORDER BY Period)   AS Minus
FROM
    yourTable

This doesn't create the hello values, but returns 0 instead. I'm not sure returning arbitrary string in an integer column makes sense, so I didn't do it.

If you really need to avoid the 0 you could just use a CASE statement...

CASE WHEN 1 = Period
     THEN NULL
     ELSE Result - FIRST_VALUE(Result) OVER (PARTITION BY Category ORDER BY Period) 
END

Or, even more robustly...

CASE WHEN 1 = ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Period)
     THEN NULL
     ELSE Result - FIRST_VALUE(Result) OVER (PARTITION BY Category ORDER BY Period)
END

(Apologies for any typos, etc, I'm on my phone.)