CODEWITHSUNDEEP
google-bigquery
DarkLeafyGreen
DarkLeafyGreen

Reputation: 70466

Slicing account balance data in BigQuery to generate a debit report

I have a collection of account balances over time:

+-----------------+------------+-------------+-----------------------+
| account_balance | department | customer_id |  timestamp            |
+-----------------+------------+-------------+-----------------------+
| 5               | A          | 1           |  2019-02-12T00:00:00  |
| -10             | A          | 1           |  2019-02-13T00:00:00  |
| -35             | A          | 1           |  2019-02-14T00:00:00  |
| 20              | A          | 1           |  2019-02-15T00:00:00  |
+-----------------+------------+-------------+-----------------------+

Each record shows the total account balance of a customer at a specified timestamp. The account balance increases e.g. to 20 from -35, when a customer tops-up his account with 55. As a customer uses a services, his account balances decreases e.g. from 5 to -10.

I want to aggregate this data in two ways:

1) Get the debit, credit and balance (credit-debit) of a department per month and year. The results from April should be a summary of all previous months:

+---------+--------+-------+------------+-------+--------+
| balance | credit | debit | department | month |  year  |
+---------+--------+-------+------------+-------+--------+
| 5       | 10     | -5    | A          | 1     |  2019  |
| 20      | 32     | -12   | A          | 2     |  2019  |
| 35      | 52     | -17   | A          | 3     |  2019  |
| 51      | 70     | -19   | A          | 4     |  2019  |
+---------+--------+-------+------------+-------+--------+

A customer's account balance might not change every month. There might be account balance records of customer 1 in February, but not March.

Notes towards the solution:

2) Get the change of debit, credit and balance of a department by date.

+---------+--------+-------+------------+-------------+
| balance | credit | debit | department |  date       |
+---------+--------+-------+------------+-------------+
| 5       | 10     | -5    | A          | 2019-01-15  |
| 15      | 22     | -7    | A          | 2019-02-15  |
| 15      | 20     | -5    | A          | 2019-03-15  |
| 16      | 18     | -2    | A          | 2019-04-15  |
+---------+--------+-------+------------+-------------+
  51       70       -19

When I create a SUM of the deltas, I should get the same values as the last row from results in 1).

Notes towards the solution:

Upvotes: 0

Views: 215

Answers (1)

rtenha
rtenha

Reputation: 3628

Your question is unclear, but it sounds like you want to get the outstanding balance at any given point in time.

The following query does this for 1 point in time.

with calendar as (
  select cast('2019-06-01' as timestamp) as balance_calc_ts
),
most_recent_balance as (
  select customer_id, balance_calc_ts,max(timestamp) as most_recent_balance_ts
  from <table>
  cross join calendar
  where timestamp < balance_calc_ts -- or <=
  group by 1,2
)
select t.customer_id, t.account_balance, mrb.balance_calc_ts
from <table> t
inner join most_recent_balance mrb on t.customer_id = mrb.customer_id and t.timestamp = mrb.balance_calc_ts

If you need to calculate it at a series of points in time, you will need to modify the calendar CTE to return more dates. This is the beauty of CROSS JOINS in BQ!

Upvotes: 2

Related Questions