title: Calculating running sum over partition in BigQuery
body: <p>I'm trying to calculate a running sum over a partition. This seems easier and quicker than the method suggested in <a href="https://stackoverflow.com/questions/14664578/bigquery-sql-running-totals">BigQuery SQL running totals</a>.</p>
  
  <p>For example:</p>
  
  <blockquote>
    <p>SELECT corpus,corpus_date,word_count,
          sum(word_count) over (partition by corpus,corpus_date order by word_count,word DESC) as running_sum
    FROM
       [publicdata:samples.shakespeare]</p>
  </blockquote>
  
  <p>I'm facing 2 problems:</p>
  
  <ol>
  <li><p>I'm unable to let the sum start with the most common word (word with highest word_count). Setting DESC or ASC just doesn't change anything, and the sum starts with the least common word(s). If I change the order by to include only "order by word_count" than the running sum isn't correct since rows with the same order (== same word_count) yield the same running sum.</p></li>
  <li><p>In a similar query I'm executing (see below), the first row of the running sum yields a sum of 0, although the field I sum upon isn't 0 for the first row. Why does this happen? How can I workaround the problem to show the correct running sum? The query is:</p></li>
  </ol>
  
  <blockquote>
    <p>select * from <br/>
    (SELECT <br/>
    mongo_id, <br/>
    account_id, <br/>
    event_date, <br/>
    trx_amount_sum_per_day, <br/>
    SUM (trx_amount_sum_per_day) OVER (PARTITION BY mongo_id,account_id ORDER BY event_date DESC) AS running_sum, <br/>
    ROW_NUMBER() OVER (PARTITION BY mongo_id,account_id ORDER BY event_date DESC) AS row_num <br/>
    FROM [xs-polar-gasket-4:publicdataset.publictable] <br/>
    ) order by event_date desc <br/></p>
  </blockquote>