title: Query to get daily negative account balances by city
body: <p>I have a list of account balances over time. The schema looks like this:  </p>
  
  <pre><code>+-------------+---------+---------+----------------------+
  | customer_id | city_id |  value  |  timestamp           |
  +-------------+---------+---------+----------------------+
  | 1           | 1       |  -500   | 2019-02-12T00:00:00  |
  | 2           | 1       |  -200   | 2019-02-12T00:00:00  |
  | 3           | 2       |  200    | 2019-02-10T00:00:00  |
  | 4           | 1       |  -10    | 2019-02-09T00:00:00  |
  +-------------+ --------+---------+----------------------+
  </code></pre>
  
  <p>I want to aggregate this data, such that I get the daily total negative account balance partitioned by city and ordered by time:</p>
  
  <pre><code>+---------+---------+--------------+
  | city_id |  value  |   timestamp  |
  +---------+---------+--------------+
  | 1       | -500    |  2019-02-12  |
  | 1       | -200    |  2019-02-10  |
  | 1       | -10     |  2019-02-09  |
  + --------+---------+--------------+
  </code></pre>
  
  <p>What I've tried:</p>
  
  <pre><code>SELECT city_id, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as date,
    SUM(value) OVER (PARTITION BY city_id ORDER BY FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp))) negative_account_balance 
  FROM `account_balances`
  WHERE value &lt; 0
  </code></pre>
  
  <p>However this gives me strange account balance values like <code>-5.985856421224E10</code>. Any ideas why? Besides that the query generates entries for the same city and same day multiple times. I would expect it to return a the same city only once for the same day.</p>