How do I find difference in today's amount and the amount the last time a transaction was posted in SQL?

I have a single table in SQL that has several different products. For example purposes, the table has 100 products and each product has a row in the table for each day of the year.

Some amounts are NULL because no data was reported that day but the row exists for that day nonetheless. To give you an example of the table, see below:

ProductID / Date / Value
Product 1 / 2020-06-25 / 15.00
Product 1 / 2020-06-24 / 14.00
Product 1 / 2020-06-23 / 13.50
Product 1 / 2020-06-22 / NULL
Product 1 / 2020-06-21 / NULL
Product 1 / 2020-06-20 / 11.50
Product 2 / 2020-06-25 / 10.00
Product 2 / 2020-06-24 / 9.00
Product 2 / 2020-06-23 / 8.50
Product 2 / 2020-06-22 / 8.00
Product 2 / 2020-06-21 / 7.00
Product 2 / 2020-06-20 / 6.50

I am trying to create a view that show's the rate of change for each product, by day, and excludes NULL values. The view should find the latest date that is not today, and the value is not null, and then compare it to today's amount for each product.

In other words, I want the view to show the following:

a.ProductID / a.Date / a.Value / b.ProductID / b.Date / b.Value / ChangeinValue
Product 1 / 2020-06-25 / 15.00 / Product 1 / 2020-06-24 / 14.00 / 1.00
Product 1 / 2020-06-24 / 14.00 / Product 1 / 2020-06-23 / 13.50 / 0.50
*Product 1 / 2020-06-23 / 13.50 / Product 1 / 2020-06-20 / 11.50 / 2.00*
Product 2 / 2020-06-25 / 10.00 / Product 2 / 2020-06-24 / 9.00 / 1.00
Product 2 / 2020-06-24 / 9.00 / Product 2 / 2020-06-23 / 8.50 / 0.50
Product 2 / 2020-06-23 / 8.50 / Product 2 / 2020-06-22 / 8.00 / 0.50
Product 2 / 2020-06-22 / 8.00 / Product 2 / 2020-06-21 / 7.00 / 1.00
Product 2 / 2020-06-21 / 7.00 / Product 2 / 2020-06-20 / 6.50 / 0.50

Any assistance in how I would go about creating this query would be greatly appreciated.

Solution

You can use window functions and some filtering:

select *
from (
    select
        t.*,
        lag(date)   over(partition by productID order by date) lag_date,
        lag(value)  over(partition by productID order by date) lag_value,
        value - lag(value) over(partition by productID order by date) change
    from mytable t
    where value is not null
) t
where lag_value is not null