SQLize Online / PHPize Online  /  SQLtest Online

create table t (dt date, perc float); insert into t values ('2022-12-01', null), ('2023-01-01', 0.1), ('2023-02-01', 0.15), ('2023-03-01', 0.5), ('2023-04-01', 0.7), ('2023-05-01', 0.3); select dt, perc, --exp(sum(ln(perc)))::numeric(3, 2), --1000 * (exp(sum(ln(1 + coalesce(perc, 1))) over (order by dt)) - 1) as new lag(1000 * coalesce(perc, 1)) over (order by dt) from t; SELECT t.dt, t.perc, t.perc * r.perc AS total_rate FROM t LEFT JOIN LATERAL ( SELECT r.perc FROM t r where r.dt <= t.dt -- "on or before that time entry" ORDER BY r.dt DESC NULLS LAST LIMIT 1 ) r ON true;