HOUR::date as day,

avg(price) as matic_price

from ethereum.core.fact_hourly_token_prices

where SYMBOL='MATIC'

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/AB589511ED0DD5FA56171A39978AFBF1371DB986EC1C3526CE138A16377E39BB')

select

block_timestamp::date as date,

case

when block_timestamp < '2022-12-20' then 'Before incentives turned on'

when block_timestamp >= '2022-12-20' then 'After incentives turned on'

end as time_gp,

count (distinct tx_id) as no_trabsfer,

count (distinct sender) as no_senders,

count(distinct 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

where BLOCK_TIMESTAMP>='2022-11-01'

group by 1 , 2