distinct receiver,

min(BLOCK_TIMESTAMP) as first_receive

from osmosis.core.fact_transfers

group by 1),

tb1 as (select

HOUR::date as day,

avg(price) as matic_price

from ethereum.core.fact_hourly_token_prices

where SYMBOL='USDC'

group by 1),

tb2 as (

select *,

regexp_substr (sender,'[a-zA-Z]+|\d+') as source_chain,

regexp_substr (receiver,'[a-zA-Z]+|\d+') as Destination_chain,

CONCAT(source_chain,' -> ',Destination_chain) as Path_transfer

from osmosis.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN')

and TX_SUCCEEDED = 'TRUE'

and currency='ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858')

select

case

when first_receive < '2022-12-20' then 'Wallets created before incentives'

when first_receive >= '2022-12-20' then 'Wallets created after incentives'

end as time_gp,

count (distinct tx_id) as no_trabsfer,

count(distinct s.RECEIVER) as no_receivers,

sum(AMOUNT/pow(10,DECIMAL)) as total_volume,

sum(AMOUNT/pow(10,DECIMAL)*matic_price) as USD_volume,

avg(AMOUNT/pow(10,DECIMAL)) as avg_volume,

median(AMOUNT/pow(10,DECIMAL)) as median_volume

from tb2 s left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day

left join tbt a on s.RECEIVER=a.RECEIVER