How to use SQL to get column count for a previous date?

I have the following table,

id    status   price      date
2    complete   10    2020-01-01 10:10:10
2    complete   20    2020-02-02 10:10:10
2    complete   10    2020-03-03 10:10:10
3    complete   10    2020-04-04 10:10:10
4    complete   10    2020-05-05 10:10:10

Required output,

id  status_count   price   ratio
2        0          0        0
2        1         10        0
2        2         30       0.33

I am looking to add the price for previous row. Row 1 is 0 because it has no previous row value. Find ratio ie 10/30=0.33

Answer

You can use analytical function ROW_NUMBER and SUM as follows:

SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) - 1 AS status_count,
    COALESCE(SUM(price) OVER (PARTITION BY id ORDER BY date), 0) - price as price
FROM yourTable;

DB<>Fiddle demo