Calculate avg acquire price for cumulative balance of each day by SQL using weighted average

Question

enter image description here

I have a table like this: each row represents a type of transaction (positive: receive, negative: send).

The idea is to calculate the cumulative balance for each day and the average price to acquire (weighted average), excluding the sending value.

The expected result and the method of calculating avg_acq_price are as follows:

• 2023-10-01: cumulative_balance = 5, avg_acq_price = 5
• 2023-10-02: cumulative_balance = 5+2 = 7, avg_acq_price = (510 + 212) / 7 = 10.571
• 2023-10-03: cumulative_balance = 7-3 = 4, avg_acq_price is the one from the previous day = 10.571
• 2023-10-04: cumulative_balance = 4+7 = 11, avg_acq_price = (10.5714 + 730) / 11

I've been struggling to get the right value for the last day so far

This is my SQL script

WITH Temp AS (
    SELECT
        block_date,
        address,
        amount,
        price,
        SUM(amount) OVER (PARTITION BY address ORDER BY block_date) AS cumulative_balance,
        COALESCE(
            SUM(amount * price) OVER (PARTITION BY address ORDER BY block_date)
            / NULLIF(SUM(amount) OVER (PARTITION BY address ORDER BY block_date), 0),
            0
        ) AS avg_acq_price
    FROM
        your_table_name
)
SELECT
    block_date,
    address,
    amount,
    price,
    LAG(cumulative_balance, 1, 0) OVER (PARTITION BY address ORDER BY block_date) AS prev_cumulative_balance,
    LAG(avg_acq_price, 1, 0) OVER (PARTITION BY address ORDER BY block_date) AS prev_avg_acq_price,
    cumulative_balance,
    CASE
        WHEN amount >= 0 THEN avg_acq_price
        ELSE LAG(avg_acq_price, 1, 0) OVER (PARTITION BY address ORDER BY block_date)
    END AS avg_acq_price
FROM Temp
ORDER BY
    block_date;

all is good except the avg_acq_price for the last day (2023-10-04). I think that the way I calculate it after the sending movement is still wrong.

Did Ido something wrong? Thanks in advance!