CODEWITHSUNDEEP
google-bigquery
Ilja
Ilja

Reputation: 1053

bigquery - calculate monthly outstanding values

I'm trying to solve the following problem:

a user took three loans with running times of 3,4 and 5 months. How to calculate in BigQuery for each point in time, how much he owns?

I know to do this calculation in R or Python but would clearly prefer a BigQuery/SQL solution.

Thank you!

I have the data:

Take Date   Return Date   Sum
2016-01-01  2016-03-31    10
2016-02-01  2016-05-31    20
2016-03-01  2016-07-31    50

I need the output like this:

Date        Sum
2016-01-01  10
2016-02-01  30
2016-03-01  80
2016-04-01  70
2016-05-01  70
2016-06-01  50
2016-07-01  50
2016-08-01   0

Upvotes: 0

Views: 85

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, DATE '2016-01-01' take_date, DATE '2016-03-31' return_date, 10 amount 
  UNION ALL SELECT 1, DATE '2016-02-01', DATE '2016-05-31', 20 
  UNION ALL SELECT 1, DATE '2016-03-01', DATE '2016-07-31', 50 
), dates AS (
  SELECT id, day
  FROM (
    SELECT id, GENERATE_DATE_ARRAY(
        MIN(take_date), 
        DATE_ADD(DATE_TRUNC(MAX(return_date), MONTH), INTERVAL 1 MONTH), 
        INTERVAL 1 MONTH
      ) days
    FROM `project.dataset.table`
    GROUP BY id
  ), UNNEST(days) day
)
SELECT d.id, d.day, SUM(IF(d.day BETWEEN t.take_date AND t.return_date, amount, 0)) amount
FROM dates d 
LEFT JOIN `project.dataset.table` t
ON d.id = t.id
GROUP BY d.id, d.day
ORDER BY d.day

with result as 

Row id  day         amount   
1   1   2016-01-01  10   
2   1   2016-02-01  30   
3   1   2016-03-01  80   
4   1   2016-04-01  70   
5   1   2016-05-01  70   
6   1   2016-06-01  50   
7   1   2016-07-01  50   
8   1   2016-08-01  0

Upvotes: 3

Related Questions