Balances

-- Parameters to alter:
-- [1] wallet_addresses: list of wallet addresses, comma-separated
-- e.g: 0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
-- [2] token_address: token contract of interest, comma-separated  
-- e.g: 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xdac17f958d2ee523a2206206994597c13d831ec7
-- [3] time_granularity: time granularity of address: e.g. hour / day / month
-- [4] blockchain: blockchain of interest: e.g. ethereum 
-- [5] balances_table: name of balances table: e.g. erc20_balances 

with
    address_table as ( 
        select distinct value::varchar as address_filter
        from
            (select split(replace('{{wallet_addresses}}', ' ', ''), ',') as addresses),
            lateral flatten(input => addresses)
    ),
    tokens_table as ( 
        select distinct value::varchar as tokens_filter
        from
            (select split(replace('{{token_address}}', ' ', ''), ',') as addresses),
            lateral flatten(input => addresses)
    ),
    daily_balances as (  -- Select the last balance of each token for each wallet by time window
        select
            date_trunc('{{time_granularity}}', balances.block_timestamp) as date,
            balances.block_number,
            balances.address,
            balances.raw_balance,
            balances.usd_balance,
            balances.balance,
            balances.token_address,
            balances.token_name,
            balances.token_symbol
        from {{blockchain}}.assets.{{balances_table}} balances
        inner join address_table on address = address_filter
        inner join tokens_table on token_address = tokens_filter
        qualify
            row_number() over (
                partition by address, token_address, date order by block_number desc
            )
            = 1
    ),
    hoders_dates as (  -- Generate timestamp x address x tokens 
        select
            date_trunc('{{time_granularity}}', timestamp) as date,
            address,
            token_address,
            count(1) as count_all
        from {{blockchain}}.raw.blocks, daily_balances
        where timestamp >= (select min(date) from daily_balances)
        group by all
    ),
    final_balances as (
        select
            t1.date,
            t1.address,
            lag(t2.token_address) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_address,
            lag(t2.token_name) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_name,
            lag(t2.token_symbol) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_symbol,
            lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as usd_balance,
            lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as raw_balance,
            lag(t2.balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as balance
        from hoders_dates t1
        left join
            daily_balances t2
            on t1.date = t2.date
            and t1.address = t2.address
            and t1.token_address = t2.token_address
        where 1 = 1
    )
select *
from final_balances
where raw_balance > 0
order by 1 desc