--Compoonent 1: Active addresses in the past 30 days with active_addresses as (select distinct msg_value:sender as address from terra.msgs where tx_status = 'SUCCEEDED' and block_timestamp >= cast('2022-02-13' as date) - 30 and address is not null), --Component 2: External assets external_assets as (select address, sum(balance_usd) as amount_usd from terra.daily_balances -- native assets (including staked LUNA) and non-native assets where date = '2022-02-13' and address in (select address from active_addresses) and address_label_type IS NULL -- excludes defi protocols, admins etc. group by address), --Component 3: Anchor deposits anchor_deposit as (select tx_id, block_timestamp, sender as address, deposit_amount --use amount in ust instead of usd to be consistent with redemption as amount_usd is missing in anchor.redeem table. from anchor.deposits where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), anchor_redeem as (select tx_id, block_timestamp, sender as address, (-1) * amount as deposit_amount -- amount_usd is null in anchor.redeem table. amount is in ust so good proxy from anchor.redeem where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), deposit as ( select * from anchor_deposit union all select * from anchor_redeem), deposit_agg as (select max(date_trunc('day', block_timestamp)) as date, address, sum(deposit_amount) as amount --sum(amount) OVER (partition by address order by date) as net_deposit_amount_usd -- just showing one date so no need for window function from deposit group by address), deposit_final as (select address, amount as amount_usd from deposit_agg), --Component 4: Anchor collateral anchor_collateral as (select tx_id, block_timestamp, sender as address, currency, case when currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'bLUNA' when currency = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' then 'bETH' end as collateral, case when event_type = 'withdraw' then -amount else amount end as amount from anchor.collateral where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), anchor_liquidation as (select tx_id, block_timestamp, borrower as address, liquidated_currency as currency, case when liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'bLUNA' when liquidated_currency = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' then 'bETH' end as collateral, (-1) * liquidated_amount as amount from anchor.liquidations where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), prices as (select date_trunc('day',block_timestamp) AS date, currency, symbol, avg(price_usd) AS price_usd from terra.oracle_prices where date = '2022-02-13' -- price on analysis date group by date, currency, symbol), collateral as ( select * from anchor_collateral union all select * from anchor_liquidation), collateral_agg as (select max(date_trunc('day', block_timestamp)) as date, address, currency, collateral, sum(amount) as amount from collateral group by address, currency, collateral), collateral_agg_usd as (select ca.*, p.price_usd, ca.amount * p.price_usd as amount_usd from collateral_agg ca left join prices p on p.currency = ca.currency), collateral_final as (select address, sum(amount_usd) as amount_usd -- sum all collaterals from collateral_agg_usd group by address), --Component 5: Anchor borrowings anchor_borrow as (select tx_id, block_timestamp, sender as address, amount_usd from anchor.borrows where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), anchor_repay as (select tx_id, block_timestamp, sender as address, (-1) * amount_usd from anchor.repay where block_timestamp <= '2022-02-13' and address in (select address from external_assets)), borrow as ( select * from anchor_borrow union all select * from anchor_repay), borrow_agg as (select max(date_trunc('day', block_timestamp)) as date, address, sum(amount_usd) as amount_usd from borrow group by address), borrow_final as (select address, (-1) * amount_usd as amount_usd from borrow_agg), --Component 6: ANC-UST LP lp_txn as (select tx_id, msg_value:sender as address, msg_value:contract as contract, msg_value:coins[0]:amount / POW(10, 6) as amount_usd from terra.msgs where block_timestamp <= '2022-02-13' and tx_status = 'SUCCEEDED' and msg_value:execute_msg:provide_liquidity is not null and msg_value:contract in ('terra1gm5p3ner9x9xpwugn9sp6gvhd0lwrtkyrecdn3', --Terraswap anc-ust pool 'terra1qr2k6yjjd5p2kaewqvg93ag74k6gyjr7re37fs', --Astroport anc-ust pool 'terra1f6d3pggq7h2y7jrgwxp3xh08yhvj8znalql87h' --Loop anc-ust pool )), lp_final as (select address, sum(2 * amount_usd) as amount_usd -- multipled by 2 as pool is 50:50 from lp_txn group by address), --Component 7: Staked ANC for governance anc_gov_stake as (select tx_id, block_timestamp, msg_value:sender as address, msg_value:execute_msg:send:contract as contract, msg_value:execute_msg:send:amount/pow(10,6) as amount from terra.msgs where block_timestamp <= '2022-02-13' and tx_status = 'SUCCEEDED' and contract = 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5' -- ANC gov contract and address in (select address from external_assets)), anc_gov_unstake as (select tx_id, block_timestamp, msg_value:sender as address, msg_value:contract as contract, (-1) * msg_value:execute_msg:withdraw_voting_tokens:amount/pow(10,6) as amount from terra.msgs where block_timestamp <= '2022-02-13' and tx_status = 'SUCCEEDED' and contract = 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5' -- ANC gov contract and amount is not null -- to remove voting txn and address in (select address from external_assets)), staked as ( select * from anc_gov_stake union all select * from anc_gov_unstake), prices_anc as (select date_trunc('day',block_timestamp) AS date, avg(amount_usd/amount) AS price_usd from anchor.gov_staking where date = '2022-02-13' -- price on analysis date group by date), staked_agg as (select max(date_trunc('day', block_timestamp)) as date, address, sum(amount) as amount from staked group by address), staked_agg_usd as (select sa.*, p.price_usd, sa.amount * p.price_usd as amount_usd from staked_agg sa join prices_anc p), staked_final as (select address, amount_usd from staked_agg_usd), -- Aggregation Agg_table as (select 'Assets outside of Anchor' as category, 'External assets' as subcategory, * from external_assets union all select 'Assets on Anchor' as category, 'Deposit' as subcategory, * from deposit_final union all select 'Assets on Anchor' as category, 'Collateral (net of borrow)' as subcategory, * from collateral_final union all select 'Assets on Anchor' as category, 'Collateral (net of borrow)' as subcategory, * from borrow_final union all select 'Assets on Anchor' as category, 'ANC-UST LP' as subcategory, * from lp_final union all select 'Assets on Anchor' as category, 'ANC governance' as subcategory, * from staked_final), -- Address' total asset address_total_asset as (select address, sum(amount_usd) as total_amount_usd from agg_table group by address), -- Address' Anchor asset address_anchor_asset as (select address, sum(amount_usd) as anchor_amount_usd from agg_table where category = 'Assets on Anchor' group by address), -- Address' Anchor proportion address_anchor_proportion as (select total.address, total.total_amount_usd, anchor.anchor_amount_usd, anchor.anchor_amount_usd/total.total_amount_usd as proportion from address_total_asset total left join address_anchor_asset anchor on anchor.address = total.address where total.total_amount_usd > 0), --Final table final_table as (select proportion.address, total_amount_usd, anchor_amount_usd, proportion, case when total_amount_usd > POW(10,7) then 'Big whale (>US$10M)' when total_amount_usd > POW(10,6) and total_amount_usd <= POW(10,7) then 'Small whale (US$1M to US$10M)' when total_amount_usd > POW(10,5) and total_amount_usd <= POW(10,6) then 'Big fish (US$100K to US$1M)' when total_amount_usd > POW(10,4) and total_amount_usd <= POW(10,5) then 'Small fish (US$10K to US$100K)' when total_amount_usd <= POW(10,4) then 'Minnow ( 0.9 then '10) >90%' when proportion > 0.8 and proportion <= 0.9 then '09) 80% to 90%' when proportion > 0.7 and proportion <= 0.8 then '08) 70% to 80%' when proportion > 0.6 and proportion <= 0.7 then '07) 60% to 70%' when proportion > 0.5 and proportion <= 0.6 then '06) 50% to 60%' when proportion > 0.4 and proportion <= 0.5 then '05) 40% to 50%' when proportion > 0.3 and proportion <= 0.4 then '04) 30% to 40%' when proportion > 0.2 and proportion <= 0.3 then '03) 20% to 30%' when proportion > 0.1 and proportion <= 0.2 then '02) 10% to 20%' when proportion > 0 and proportion <= 0.1 then '01) 0% to 10%' end as proportion_bucket from address_anchor_proportion proportion where proportion > 0) select asset_bucket, proportion_bucket, count(address) as frequency, count(address) * 100.0 / sum(count(address)) over() as proportion_of_wallets from final_table group by asset_bucket, proportion_bucket

Anchor Dominance