MLDZMNgauchol1
    Updated 2023-11-15
    WITH token_holdings AS (
    SELECT block_timestamp, post_tokens.value:owner AS wallet, post_tokens.value:mint AS token, post_tokens.value:uiTokenAmount:uiAmount AS amount,
    ROW_NUMBER() OVER (PARTITION BY wallet, token ORDER BY block_timestamp DESC) AS rn
    FROM solana.core.fact_transactions, LATERAL FLATTEN(input => post_token_balances) post_tokens
    WHERE SUCCEEDED = 'True'
    AND post_tokens.value:owner = wallet
    AND post_tokens.value:mint = 'AZsHEMXd36Bj1EMNXhowJajpUXzrKcK57wW4ZGXVa7yR'
    --and wallet not in ('')
    AND block_timestamp >= '2023-01-01'
    ORDER BY wallet, token, rn
    )
    select
    avg(amount/1e6) as avg_balance,
    median(amount/1e6) as median_holding,
    count(distinct wallet) as total_holders
    FROM token_holdings
    WHERE rn = 1 AND amount IS NOT NULL AND amount <> ''
    order by amount desc



    Run a query to Download Data