-- forked from Antonidas / TUTORIAL - DAILY BALANCES JOIN @ https://flipsidecrypto.xyz/Antonidas/q/64WbIVRLYL-L/tutorial---daily-balances-join
with raw_data as (
select
day_date
, user as address
, daily_balances as value
from $query('903d747a-b72d-4da3-989e-055beeab4f0c')
)
, date_table as (
-- limit the date range to cross
select DATE_DAY
from crosschain.core.dim_dates
where DATE_DAY >= '2024-07-02'
and DATE_DAY < '2024-11-03'
)
, manidata as (
-- cross join distinct addresses to all dates
SELECT DISTINCT address, DATE_DAY
FROM (
SELECT DISTINCT address
FROM raw_data -- Replace 'your_table' with your actual table name
) AS t1
CROSS JOIN date_table AS d2
)
, join_back_to_values as (
-- now we have full address and dates pairing
-- join back to original values
select
m.*
, r.value
from manidata m
left join raw_data r on m.address = r.address and r.day_date = m.date_day
)
, fill_nulls as(
select