CODEWITHSUNDEEP
sqlpresto
user2105632
user2105632

Reputation: 781

SQL Compound Growth Calculation based on previous rows (varying rate)

Given a column for 'Growth Factors' and a starting value I need to compute future values. For example, if a starting value of 1 is provided then the computed 'Value' column would be as shown below. Thus, Value(t2) = Value(t1) x Growth_Factor(t2). Base condition is Value(t1) = Starting_Value x Growth_Factor(t1). Example shown below. How do I compute this in SQL (or Presto) where the computed value is dependent on previous computed values?

Growth Factor Value Time
1.2 1.2 1
1.1 1.32 2
1.5 1.98 3
1.7 3.366 4

Upvotes: 0

Views: 262

Answers (1)

shawnt00
shawnt00

Reputation: 17925

You could sum the logarithms and invert when finished. This will work other than some possibility of small floating point error. But you're also going to introduce error once you multiply more than a few numbers with doubling decimal places at every iteration.

exp(
    sum(ln(growth)) over (order by time)
)

Upvotes: 1

Related Questions