CODEWITHSUNDEEP
google-bigquery
DarkLeafyGreen
DarkLeafyGreen

Reputation: 70466

Query the amount of consecutive negative account balance days in BigQuery

I have a collection of account balances:

+---------+---------------+---------+------------+
|    ID   |  customer_id  |  value  | timestamp  |
+---------+---------------+---------+------------+
| 1       | 1             |  -200   | 2019-11-18 |
| 2       | 1             |  100    | 2019-11-17 |
| 3       | 1             |  -500   | 2019-11-16 |
| 4       | 1             |  -200   | 2019-11-15 |
| 5       | 2             |  200    | 2019-11-15 |
| 6       | 1             |  0      | 2019-11-14 |
+---------+---------------+---------+------------+

I want to get the amount of consecutive days where the customer had a negative account balance, since that last positive account balance. The result should look like that:

+---------------+---------------------------------+------------+
|  customer_id  |  Negative account balance since |    Date    |
+---------------+---------------------------------+------------+
| 1             |  1 day                          | 2019-11-18 |
+---------------+---------------------------------+------------+

Customer #1 had a couple of negative days, however the counter kind of restarts because there was a positive day at 2019-11-17. The date column shows the date of the most recent negative account balance record of that customer. Customer #2 is not in the result, because he had no negative days at all. How can I create such query in BQ?

Upvotes: 0

Views: 243

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL 

#standardSQL
WITH last_positive AS (
  SELECT customer_id, ARRAY_AGG(`timestamp` ORDER BY `timestamp` DESC LIMIT 1)[OFFSET(0)] `timestamp`
  FROM `project.dataset.table`
  WHERE value >= 0
  GROUP BY customer_id
), last_any AS (
  SELECT customer_id, MAX(`timestamp`) `timestamp` 
  FROM `project.dataset.table`
  GROUP BY customer_id
)
SELECT customer_id, DATE_DIFF(a.timestamp, b.timestamp, DAY) days_since, DATE_ADD(b.timestamp, INTERVAL 1 DAY) `timestamp`
FROM last_any a
JOIN last_positive b
USING(customer_id)
WHERE a.timestamp > b.timestamp

if to apply to sample data from your question as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 customer_id, -200 value, DATE '2019-11-18' `timestamp` UNION ALL
  SELECT 2, 1, 100, '2019-11-17' UNION ALL
  SELECT 3, 1, -500, '2019-11-16' UNION ALL
  SELECT 4, 1, -200, '2019-11-15' UNION ALL
  SELECT 5, 2, 200, '2019-11-15' UNION ALL
  SELECT 6, 1, 0, '2019-11-14' 
), last_positive AS (
  SELECT customer_id, ARRAY_AGG(`timestamp` ORDER BY `timestamp` DESC LIMIT 1)[OFFSET(0)] `timestamp`
  FROM `project.dataset.table`
  WHERE value >= 0
  GROUP BY customer_id
), last_any AS (
  SELECT customer_id, MAX(`timestamp`) `timestamp` 
  FROM `project.dataset.table`
  GROUP BY customer_id
)
SELECT customer_id, DATE_DIFF(a.timestamp, b.timestamp, DAY) days_since, DATE_ADD(b.timestamp, INTERVAL 1 DAY) `timestamp`
FROM last_any a
JOIN last_positive b
USING(customer_id)
WHERE a.timestamp > b.timestamp

result is 

Row customer_id days_since  timestamp    
1   1           1           2019-11-18

Upvotes: 1

Related Questions