Q:

Get last known record per month in BigQuery

Account balance collection, that shows the account balance of a customer at a given day:
+---------------+---------+------------+
|  customer_id  |  value  | timestamp  |
+---------------+---------+------------+
| 1             |  -500   | 2019-10-12 |
| 1             |  -300   | 2019-10-11 |
| 1             |  -200   | 2019-10-10 |
| 1             |  0      | 2019-10-09 |
| 2             |  200    | 2019-09-10 |
| 1             |  600    | 2019-09-02 |
+---------------+---------+------------+

Notice, that customer #2 had no updates to his account balance in October. 
I want to get the last account balance per customer per month. If there has been no account balance update for a customer in a given month, the last known account balance should be transferred to the current month. The result should look like that:
+---------------+---------+------------+
|  customer_id  |  value  | timestamp  |
+---------------+---------+------------+
| 1             |  -500   | 2019-10-12 |
| 2             |  200    | 2019-10-10 |
| 2             |  200    | 2019-09-10 |
| 1             |  600    | 2019-09-02 |
+---------------+---------+------------+

Since the account balance of customer #2 was not updated in October but in September, we create a copy of the row from September changing the date to October. Any ideas how to achieve this in BigQuery?

A:

Below is for BigQuery Standard SQL    
#standardSQL
WITH customers AS (
  SELECT DISTINCT customer_id FROM `project.dataset.table`
), months AS (
  SELECT month FROM (
    SELECT DATE_TRUNC(MIN(timestamp), MONTH) min_month, DATE_TRUNC(MAX(timestamp), MONTH) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month
)
SELECT customer_id, 
  IFNULL(value, LEAD(value) OVER(win)) value,  
  IFNULL(timestamp, DATE_ADD(LEAD(timestamp) OVER(win), INTERVAL DATE_DIFF(month, LEAD(month) OVER(win), MONTH) MONTH)) timestamp  
FROM months, customers
LEFT JOIN (
  SELECT DATE_TRUNC(timestamp, MONTH) month, customer_id, 
    ARRAY_AGG(STRUCT(value, timestamp) ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)].* 
  FROM `project.dataset.table` 
  GROUP BY month, customer_id
) USING(month, customer_id)
WINDOW win AS (PARTITION BY customer_id ORDER BY month DESC)

if to apply to sample data from your question - as it is in below example    
#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 customer_id, -500 value, DATE '2019-10-12' timestamp UNION ALL
  SELECT 1, -300, '2019-10-11' UNION ALL
  SELECT 1, -200, '2019-10-10' UNION ALL
  SELECT 2, 200, '2019-09-10' UNION ALL
  SELECT 2, 100, '2019-08-11' UNION ALL
  SELECT 2, 50, '2019-07-12' UNION ALL
  SELECT 1, 600, '2019-09-02' 
), customers AS (
  SELECT DISTINCT customer_id FROM `project.dataset.table`
), months AS (
  SELECT month FROM (
    SELECT DATE_TRUNC(MIN(timestamp), MONTH) min_month, DATE_TRUNC(MAX(timestamp), MONTH) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month
)
SELECT customer_id, 
  IFNULL(value, LEAD(value) OVER(win)) value,  
  IFNULL(timestamp, DATE_ADD(LEAD(timestamp) OVER(win), INTERVAL DATE_DIFF(month, LEAD(month) OVER(win), MONTH) MONTH)) timestamp  
FROM months, customers
LEFT JOIN (
  SELECT DATE_TRUNC(timestamp, MONTH) month, customer_id, 
    ARRAY_AGG(STRUCT(value, timestamp) ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)].* 
  FROM `project.dataset.table` 
  GROUP BY month, customer_id
) USING(month, customer_id)
WINDOW win AS (PARTITION BY customer_id ORDER BY month DESC)
-- ORDER BY month DESC, customer_id   

result is    
Row customer_id value   timestamp    
1   1           -500    2019-10-12   
2   2           200     2019-10-10   
3   1           600     2019-09-02   
4   2           200     2019-09-10   
5   1           null    null     
6   2           100     2019-08-11   
7   1           null    null     
8   2           50      2019-07-12   

