with base as (SELECT

'Polygon' as source

,*,

try_hex_decode_string(substr(data, 3 + (64*7), 16)) as destination,

concat (source,'->',destination) as direction

FROM polygon.core.fact_event_logs

WHERE tx_hash in (

SELECT tx_hash

FROM polygon.core.fact_token_transfers

WHERE to_address ILIKE ('0xce16f69375520ab01377ce7b88f5ba8c48f8d666')

and topics[0] = '0x999d431b58761213cf53af96262b67a069cbd963499fd8effd1e21556217b841'

and destination is not null

),

raw as (SELECT

tx_hash ,

RAW_AMOUNT / 1e6 as AMOUNT

FROM Polygon.core.fact_token_transfers

WHERE tx_hash in (

SELECT tx_hash

FROM Polygon.core.fact_token_transfers

WHERE to_address ilike '0xce16f69375520ab01377ce7b88f5ba8c48f8d666')

and contract_address LIKE lower('0x750e4C4984a9e0f12978eA6742Bc1c5D248f40ed'))

select

direction,

sum(AMOUNT ) as USD_AMOUNT,

round(avg(AMOUNT ),2 )as avg_USD_AMOUNT,

count(distinct b.ORIGIN_FROM_ADDRESS) as users,

count(distinct b.TX_HASH) as txns

from

base b , raw r