pavlyshyn2023-09-26 11:29 AM
    SELECT
    to_date(block_timestamp) as Date,
    count(distinct tx_id) as tx_count,
    max(post_balances_values.VALUE::INTEGER) as post_balance,
    sum(post_balances_values.VALUE::INTEGER - pre_balances_values.VALUE::INTEGER) as fee_sum

    FROM
    solana.core.fact_transactions,
    LATERAL FLATTEN(input => account_keys) as flattened_account_keys,
    LATERAL FLATTEN(input => pre_balances) as pre_balances_values,
    LATERAL FLATTEN(input => post_balances) as post_balances_values

    WHERE block_id>=210000000
    and flattened_account_keys.INDEX = pre_balances_values.INDEX
    and flattened_account_keys.INDEX = post_balances_values.INDEX
    and flattened_account_keys.VALUE:pubkey::STRING = '9kwU8PYhsmRfgS3nwnzT3TvnDeuvdbMAXqWsri2X8rAU'
    group by Date