users as (

SELECT

distinct tx_from,

min(trunc(block_timestamp,'day')) as debut

from osmosis.core.fact_transactions where tx_from in (select distinct receiver from osmosis.core.fact_transfers where currency ilike '%ibc/AB589511ED0DD5FA56171A39978AFBF1371DB986EC1C3526CE138A16377E39BB%'

and transfer_type ='IBC_TRANSFER_IN' and tx_succeeded='TRUE' )

group by 1

)

select

trunc(block_timestamp,'day') as date,

case when debut<=date-INTERVAL '1 MONTH' then 'New user' else 'Old user' end as type,

count(distinct sender) as users,

count(distinct tx_id) as matic_transfers_to_osmosis,

--count(distinct sender) as active_senders,

count(distinct receiver) as active_receivers,

sum(amount/pow(10,decimal)) as volume_transferred,

avg(amount/pow(10,decimal)) as avg_volume_transferred_per_tx

from osmosis.core.fact_transfers x

join users y on x.receiver=y.tx_from

where currency ilike '%ibc/AB589511ED0DD5FA56171A39978AFBF1371DB986EC1C3526CE138A16377E39BB%'

and transfer_type ='IBC_TRANSFER_IN' and tx_succeeded='TRUE'

group by 1,2 order by 1 asc