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 month,
(bal_end - COALESCE(LAG(bal_end) OVER(ORDER BY month), bal_start)) /
COALESCE(LAG(bal_end) OVER(ORDER BY month), bal_start) AS growth
FROM (SELECT month,
MAX(CASE
WHEN m_end = 1 THEN
sum_balance
END) AS bal_end,
MAX(CASE
WHEN m_start = 1 THEN
sum_balance
END) bal_start
FROM (SELECT month,
SUM(COALESCE(CASE
WHEN bal_start = 1 THEN
balance
END,
0) + COALESCE(revenue, 0)) OVER(ORDER BY date) AS sum_balance,
m_end,
m_start,
date
FROM (SELECT DATE_FORMAT(date, '%Y-%m') AS month,
t.*,
ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(date, '%Y-%m') ORDER BY date) AS m_start,
ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(date, '%Y-%m') ORDER BY date DESC) AS m_end,
ROW_NUMBER() OVER(ORDER BY date) AS bal_start
FROM t) AS t0) AS t1
GROUP BY month) AS t2
| month | growth |
|---|---|
| 2021-05 | 0.2500 |
| 2021-06 | -0.4000 |