Fill table using last value per user-CodePudding

I've got data on customers balance on a daily basis, with the following structure in BigQuery:

CREATE TABLE if not EXISTS balance (
  user_id int,
  updated_ag DATE,
  value float
);

INSERT INTO balance VALUES
(1, '2021-01-01', 0),
(1, '2021-01-02', 1),
(1, '2021-01-05', 2),
(1, '2021-01-07', 5),
(2, '2021-01-01', 5),
(2, '2021-01-03', 0),
(2, '2021-01-04', 1),
(2, '2021-01-06', 2);

I have one row for a user on a given day if the balance on that day changed.

I'd like to complete the data for each user by putting the balance of the last day where there was an update, with the dates being between the first day with balance and last date in the table.

So, the output table in this example would have the following values:

(1, '2021-01-01', 0),
(1, '2021-01-02', 1),
(1, '2021-01-03', 1),
(1, '2021-01-04', 1),
(1, '2021-01-05', 2),
(1, '2021-01-06', 2),
(1, '2021-01-07', 5),
(2, '2021-01-01', 5),
(2, '2021-01-02', 5),
(2, '2021-01-03', 0),
(2, '2021-01-04', 1),
(2, '2021-01-05', 1),
(2, '2021-01-06', 2),
(2, '2021-01-07', 2)

What's the simplest way of doing this in BigQuery?

CodePudding user response：

Try this one:

select user_id, generated_date, value
from (
  select 
    *, 
    ifnull(lead(date) over(partition by user_id order by date) - 1, max(date) over()) date_to
  from balance
), unnest(generate_date_array(date, date_to, interval 1 day)) generated_date