with t1 as (select

HOUR::date as day,

SYMBOL,

DECIMALS,

avg(PRICE) as token_price

from ethereum.core.fact_hourly_token_prices

group by 1,2,3),

t5 as (select

distinct s.tx_hash,

s.block_timestamp,

'Polygon' as source_chain,

try_hex_decode_string(substr(data,451,16)) as target_chain,

concat(source_chain,'->',target_chain) as pathway,

case when d.SYMBOL ilike '%usdc%' then 'USDC' else d.symbol end as token,

a.origin_from_address as sender,

RAW_AMOUNT/pow(10,b.DECIMALS) as amount,

case when amount*token_price is null then amount else amount*token_price end as AMOUNT_USD

from Polygon.core.fact_event_logs s left join Polygon.core.fact_token_transfers a on s.tx_hash=a.tx_hash

join Polygon.core.dim_contracts d on a.CONTRACT_ADDRESS=d.ADDRESS

left join t1 b on d.symbol=b.symbol and s.BLOCK_TIMESTAMP::date=b.day

where s.CONTRACT_ADDRESS=lower('0x2d5d7d31f671f86c782533cc367f14109a082712')

and TX_STATUS='SUCCESS'

and s.topics[0] = '0x999d431b58761213cf53af96262b67a069cbd963499fd8effd1e21556217b841'

and a.ORIGIN_TO_ADDRESS='0xce16f69375520ab01377ce7b88f5ba8c48f8d666'

and token not ilike '%usdc%'

select

token,

count(distinct tx_hash) as no_txn,

count(distinct sender) as no_users,

sum(AMOUNT_USD) as total_volume,