with df AS ( select SENDER, tx_id from axelar.core.fact_transfers where TRANSFER_TYPE in ('IBC_TRANSFER_IN') and TX_SUCCEEDED = 'TRUE' and BLOCK_TIMESTAMP >= '2022-01-26' AND not CURRENCY ilike any ('factory%', 'gravity%') and amount is not null and currency is not null ), df1 as ( select SENDER, max(DATE) as date, count(DISTINCT tx_id) as tx_count from df left join osmosis.core.fact_daily_balances on df.SENDER = osmosis.core.fact_daily_balances.ADDRESS group by 1), prices as ( SELECT date, price, CASE WHEN symbol = 'axlUSDC' then 'USDC' WHEN symbol = 'axlWBTC' then 'WBTC' WHEN SYMBOL = 'axlWETH' THEN 'WETH' ELSE symbol end as token from( SELECT date_trunc('day', RECORDED_AT) as date, avg(price) as price, symbol FROM osmosis.core.dim_prices group by 1,3)), dftable as ( select df1.date, df1.SENDER, BALANCE/pow(10,decimal) as balance, replace(replace(Project_name, '.axl'),'.grv') as Asset, tx_count from df1 left join osmosis.core.fact_daily_balances on df1.SENDER = osmosis.core.fact_daily_balances.ADDRESS and df1.date = osmosis.core.fact_daily_balances.DATE left join osmosis.core.dim_labels on osmosis.core.fact_daily_balances.currency = osmosis.core.dim_labels.address ) select *, CASE WHEN balance_usd < 10 then 'less then 10 USD' WHEN balance_usd >=10 and balance_usd <100 then '10-100 USD' WHEN balance_usd >=100 and balance_usd <1000 then '100-1k USD' WHEN balance_usd >=1000 and balance_usd <10000 then '1k-10k USD' else 'More then 10k USD' end as cat_usd, CASE WHEN tx_count = 1 then '1 Transfer' WHEN tx_count >=2 and tx_count <5 then '2-5 Transfers' WHEN tx_count >=5 and tx_count <10 then '5-10 Transfers' WHEN tx_count >=10 and tx_count <50 then '10-50 Transfers' WHEN tx_count >=50 and tx_count <100 then '50-100 Transfers' WHEN tx_count >= 100 and tx_count <500 then '100-500 Transfers' WHEN tx_count >= 500 and tx_count <1000 then '500-1000 Transfers' ELSE 'More then 1k Transfers' END AS "Category" from ( select dftable.date, sender, balance, asset, balance*price as balance_usd, tx_count from dftable left join prices on dftable.date = prices.date and dftable.asset = prices.token) where asset is not null and balance_usd is not null and balance != '1e-18' order by tx_count desc

AXELAR BRIDGE. User Behavior