Balances

-- Parameters to alter:
-- [1] wallet_addresses: list of wallet addresses, comma-separated
-- e.g: 0x1d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e, 0x2d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e
-- [2] coin_types: coin types of interest, comma-separated  
-- e.g: 0x2::sui::SUI, 0x2::usdc::USDC
-- [3] time_granularity: time granularity of address: e.g. hour / day / month

WITH
    wallets AS ( 
        SELECT DISTINCT value::varchar AS wallet_address
        FROM
            (SELECT split(replace('{{wallet_addresses}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    coins AS ( 
        SELECT DISTINCT value::varchar AS coin_type
        FROM
            (SELECT split(replace('{{coin_types}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    balances AS ( 
        SELECT
            date_trunc('{{time_granularity}}', b.checkpoint_timestamp) AS date,
            b.checkpoint_sequence,
            b.address,
            b.raw_balance,
            b.usd_balance,
            b.balance,
            b.coin_type,
            b.coin_name,
            b.coin_symbol
        FROM sui.assets.balances b
        INNER JOIN wallets ON b.address = wallets.wallet_address
        INNER JOIN coins ON b.coin_type = coins.coin_type
        QUALIFY
            row_number() OVER (
                PARTITION BY b.address, b.coin_type, date ORDER BY b.checkpoint_sequence DESC
            ) = 1
    ),
    distinct_address_tokens AS ( -- Generate a list of address x token
        SELECT DISTINCT address, coin_type FROM balances
    ),
    distinct_dates AS ( -- Generate a list of timestamps
        SELECT date_trunc('{{time_granularity}}', timestamp) AS date, count(1) AS count_all
        FROM sui.raw.checkpoints
        WHERE timestamp >= (SELECT min(date) FROM balances)
        GROUP BY date
    ),
    date_address_token_cte AS (  -- Generate timestamp x address x tokens 
        SELECT t1.date, t2.address, t2.coin_type
        FROM distinct_dates t1, distinct_address_tokens t2
    ),
    final AS (
        SELECT
            t1.date,
            t1.address,
            lag(t2.coin_type) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_type,
            lag(t2.coin_name) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_name,
            lag(t2.coin_symbol) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_symbol,
            lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS usd_balance,
            lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS raw_balance,
            lag(t2.balance) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS balance
        FROM date_address_token_cte t1
        LEFT JOIN balances t2
        ON t1.date = t2.date
        AND t1.address = t2.address
        AND t1.coin_type = t2.coin_type
        WHERE 1 = 1
    )
SELECT 
    date,
    address,
    coin_type,
    coin_name,
    coin_symbol,
    usd_balance,
    raw_balance,
    balance
FROM final
WHERE raw_balance > 0
ORDER BY date DESC, address, coin_type