title: Calculative cumulative returns using SQL
body: <p>I currently generate a user's "monthly_return" between two months using the code below. How would I turn "monthly_return" into a cumulative "linked" return similar to the StackOverflow question linked below?</p>
  
  <p>Similar question: <a href="https://stackoverflow.com/questions/25700772/running-cumulative-return-in-sql">Running cumulative return in sql</a></p>
  
  <p>I tried: </p>
  
  <pre><code>exp(sum(log(1 + cumulative_return) over (order by date)) - 1)
  </code></pre>
  
  <p>But get the error:</p>
  
  <p>PG::WrongObjectType: ERROR: OVER specified, but log is not a window function nor an aggregate function LINE 3: exp(sum(log(1 + cumulative_return) over (order by date)) - 1... ^ : SELECT portfolio_id, exp(sum(log(1 + cumulative_return) over (order by date)) - 1) FROM (SELECT date, portfolio_id, (value_cents * 0.01 - cash_flow_cents * 0.01) / (lag(value_cents * 0.01, 1) over ( ORDER BY portfolio_id, date)) - 1 AS cumulative_return FROM portfolio_balances WHERE portfolio_id = 16 ORDER BY portfolio_id, date) as return_data;</p>
  
  <p>The input data would be:</p>
  
  <pre><code>1/1/2017: $100 value, $100 cash flow
  1/2/2017: $100 value, $0 cash flow
  1/3/2017: $100 value, $0 cash flow
  1/4/2017: $200 value, $100 cash flow
  </code></pre>
  
  <p>The output would be:</p>
  
  <pre><code>1/1/2017: 0% cumulative return
  1/2/2017: 0% cumulative return
  1/3/2017: 0% cumulative return
  1/4/2017: 0% cumulative return
  </code></pre>
  
  <p>My current code which shows monthly returns which are not linked (cumulative).</p>
  
  <pre><code>SELECT 
  date,
  portfolio_id,
  (value_cents * 0.01 - cash_flow_cents * 0.01) / (lag(value_cents * 0.01, 1) over ( ORDER BY portfolio_id, date)) - 1 AS monthly_return
  FROM portfolio_balances
  WHERE portfolio_id = 16
  ORDER BY portfolio_id, date;
  </code></pre>