Create Balance Sheet with every date is filled in Bigquery-CodePudding

I want to create table that consist users daily points balanced every day. that means the we still have the data even though the users didnt have any transaction.

my current table

date_id      user_id    income    spend   point_expired   balance
2021-02-10      A       100000      0           0         100000
2021-02-18      A        50000      0           0         150000
2021-02-25      A        30000      0           0         180000
2021-02-28      A            0    100000        0          80000

my desired table

date_id      user_id    income    spend   point_expired   balance
2021-02-10      A       100000      0           0         100000
2021-02-11      A            0      0           0         100000
2021-02-12      A            0      0           0         100000
2021-02-13      A            0      0           0         100000
2021-02-14      A            0      0           0         100000
2021-02-15      A            0      0           0         100000
2021-02-16      A            0      0           0         100000
2021-02-17      A            0      0           0         100000
2021-02-18      A        50000      0           0         150000
2021-02-19      A            0      0           0         150000
2021-02-20      A            0      0           0         150000
2021-02-21      A            0      0           0         150000
2021-02-22      A            0      0           0         150000
2021-02-23      A            0      0           0         150000
2021-02-24      A            0      0           0         150000
2021-02-25      A        30000      0           0         180000
2021-02-26      A            0      0           0         180000
2021-02-27      A            0      0           0         180000
2021-02-28      A            0    100000        0          80000

CodePudding user response：

Consider below approach

select day as date_id,
  if(day = date_id, struct(income, spend, point_expired, balance), struct(0, 0, 0, balance)).*
from (
  select *, 
    lead(date_id) over(partition by user_id order by date_id) as next_date_id,
  from your_table
) t, unnest(generate_date_array(date_id, ifnull(next_date_id, date_id   1) - 1)) day

if applied to sample data in your question

with your_table as (
  select date '2021-02-10' date_id, 'A' user_id, 100000 income, 0 spend, 0 point_expired, 100000 balance union all
  select '2021-02-18', 'A', 50000, 0, 0, 150000 union all
  select '2021-02-25', 'A', 30000, 0, 0, 180000 union all
  select '2021-02-28', 'A', 0, 100000, 0, 80000 
)

output is