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