By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t( date DATE, revenue INT, balance INT);
INSERT INTO t VALUES
('2021-05-03',0,1000),
('2021-05-21',500,1500),
('2021-05-23',-250,1250),
('2021-06-02',-500,750)
select year(date), month(date),
(-1 +
(max(case when seqnum_desc = 1 then balance end) /
max(case when seqnum_asc = 1 then coalesce(prev_balance, balance) end)
)
) as growth
from (select t.*,
row_number() over (partition by year(date), month(date) order by date) as seqnum_asc,
row_number() over (partition by year(date), month(date) order by date desc) as seqnum_desc,
lag(balance) over (order by date) as prev_balance
from t
) t
group by year(date), month(date)
| year(date) | month(date) | growth |
|---|---|---|
| 2021 | 5 | 0.2500 |
| 2021 | 6 | -0.4000 |