nickpayiatis_ASA asset Stats
    Updated 2022-10-19
    with ALGO_holders as(
    select 0 as asset_id, count(distinct address) as holders, sum(balance)/holders as average_holding from algorand.account
    where balance > 0
    ),
    ASA_holders as(
    select asset_id as asset_id, count(distinct address) as holders, sum(amount)/holders as average_holding from algorand.account_asset
    where amount > 0
    and asset_id = {{ASA_ID}}
    group by asset_id
    )
    ,
    ASA_opt as(
    select asset_id as asset_id, count(distinct address) as holders, sum(amount)/holders as average_holding from algorand.account_asset
    where asset_id = {{ASA_ID}}
    group by asset_id
    )
    ,
    asset_price as(
    select * from algorand.prices_pool_balances
    where asset_id = {{ASA_ID}}
    and block_hour = (Select max(block_hour) from algorand.prices_pool_balances where asset_id = {{ASA_ID}})
    )

    select a.*,
    case when {{ASA_ID}} = 0 then ah.holders
    else asa.holders
    end as holders,
    ao.holders as opt_in_holders,
    case when {{ASA_ID}} = 0 then ah.average_holding
    else asa.average_holding
    end as average_holding
    ,ap.price_usd
    FROM algorand.ASSET a
    left join ALGO_holders ah on a.asset_id = ah.asset_id
    left join ASA_holders asa on a.asset_id = asa.asset_id
    left join ASA_opt ao on a.asset_id = ao.asset_id
    Run a query to Download Data