How to calculate the difference between current row and the last row of last month?

        update_time         |   net_value 
----------------------------+---------------------------
 2021-03-03 00:33:00.13055  |    0.6
 2021-03-02 14:23:18.173233 |    0.5
 2021-03-01 01:31:00.146775 |    0.3
 2021-02-28 00:33:00.13055  |    0.2

I have a postgres table net_value as above, one row one day.

How can I calculate the difference between current row and the last row of last month? Look like the output as below:

        update_time         |   monthly_diff 
----------------------------+---------------------------
 2021-03-03 00:33:00.13055  |    0.4 // 0.6 - 0.2
 2021-03-02 14:23:18.173233 |    0.3 // 0.5 - 0.2
 2021-03-01 01:31:00.146775 |    0.1 // 0.3 - 0.2
 2021-02-28 00:33:00.13055  |    0.2 // 0.2 - 0

Thanks!

Answer

Hmmm . . . Here is a method with a join:

select t.*,
       (value - prev_eom_net_value) 
from t join
     (select yyyymm, eom_net_value,
             lag(eom_net_value) over (order by yyyymm) as prev_eom_net_value
      from (select date_trunc('month', update_time) as yyyymm,
                   (array_agg(value order by update_time desc))[1] as eom_net_value
            from t
            group by yyyymm
           ) t
     ) x
     on x.yyyymm = date_trunc('month', update_time);

I also think an approach using window functions and a window frame should also work:

select t.*,
       (net_value -
        max(net_value) over (partition by date_trunc('month', update_time)
                             order by date_trunc('month', update_time)
                             range between '1 month' preceding and '1 month' preceding
                            )
      )
from (select t.*,
             first_value(net_value) over (partition by date_trunc('month', update_time) order by update_time desc) as eom_net_value
      from t
     ) t;