Query to get daily negative account balances by city

Question

I have a list of account balances over time. The schema looks like this:

+-------------+---------+---------+----------------------+
| 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  |
+-------------+ --------+---------+----------------------+

I want to aggregate this data, such that I get the daily total negative account balance partitioned by city and ordered by time:

+---------+---------+--------------+
| city_id |  value  |   timestamp  |
+---------+---------+--------------+
| 1       | -500    |  2019-02-12  |
| 1       | -200    |  2019-02-10  |
| 1       | -10     |  2019-02-09  |
+ --------+---------+--------------+

What I've tried:

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 < 0

However this gives me strange account balance values like -5.985856421224E10. 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.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT city_id, account_balance, `date` FROM (
  SELECT city_id, `date`, 
    SUM(value) OVER(PARTITION BY city_id ORDER BY `date`) account_balance 
  FROM (
    SELECT city_id, DATE(TIMESTAMP(t.timestamp)) AS `date`, SUM(value) value
    FROM `project.dataset.account_balances` t
    GROUP BY city_id, `date` )
)
WHERE account_balance< 0   

You can test, play with above using sample/dummy data as in below example

#standardSQL
WITH `project.dataset.account_balances` AS (
  SELECT 1 customer_id, 1 city_id, -500 value, '2019-02-12T00:00:00' `timestamp` UNION ALL
  SELECT 2, 1, -200, '2019-02-12T00:00:00' UNION ALL
  SELECT 5, 1, 100, '2019-02-13T00:00:00' UNION ALL
  SELECT 3, 2, 200, '2019-02-10T00:00:00' UNION ALL
  SELECT 4, 1, -10, '2019-02-09T00:00:00' 
)
SELECT city_id, account_balance, `date` FROM (
  SELECT city_id, `date`, 
    SUM(value) OVER(PARTITION BY city_id ORDER BY `date`) account_balance 
  FROM (
    SELECT city_id, DATE(TIMESTAMP(t.timestamp)) AS `date`, SUM(value) value
    FROM `project.dataset.account_balances` t
    GROUP BY city_id, `date` )
)
WHERE account_balance< 0   

which produces below result

Row city_id account_balance date     
1   1       -10             2019-02-09   
2   1       -710            2019-02-12   
3   1       -610            2019-02-13   

Answer 2

I took a simpler approach and used this sql (BTW When I tried your original query I got a result which seems ok)

SELECT city_id, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as date,
  SUM(value) as value
FROM `account_balances`
GROUP BY city_id, timestamp
HAVING value < 0

I used this data to check it out (Note: I changed the date format to match BigQuery format although the result is the same either way)

WITH account_balances as (
SELECT 1 AS customer_id, 1 as city_id, -500 as value, '2019-02-12 00:00:00' as timestamp UNION ALL
SELECT 2 AS customer_id, 1 as city_id, -200 as value, '2019-02-12 00:00:00' as timestamp UNION ALL
SELECT 3 AS customer_id, 2 as city_id, 200 as value, '2019-02-10 00:00:00' as timestamp UNION ALL
SELECT 4 AS customer_id, 1 as city_id, -10 as value, '2019-02-09 00:00:00' as timestamp
)

SELECT city_id, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as date,
  SUM(value) as value
FROM `account_balances`
GROUP BY city_id, timestamp
HAVING value < 0

This is the result: