prices as ( SELECT hour::date as date1, token_address, avg(price) as pr

from crosschain.price.ez_prices_hourly

where hour::date >= '2023-09-01'

GROUP by all),

trs as (SELECT

CREATED_AT,

COMMAND_ID as tx,

amount as volume,

volume*pr as usd,

data:executed:receipt:logs[1]:address as token_addy,

data:interchain_transfer:symbol as symbol,

data:interchain_transfer:name as name,

call:chain as source,

call:returnValues:destinationChain as destination,

call:chain || '➡' || call:returnValues:destinationChain as "Route",

CALL:receipt:from as sender

from axelar.axelscan.fact_gmp

join prices

on CREATED_AT::date = date1

and token_addy = token_address

where

data:interchain_transfer:event = 'InterchainTransfer'

and STATUS = 'executed')

SELECT date_trunc('week', CREATED_AT::date) as date, sum(usd) as "Volume(USD)",

sum("Volume(USD)") over (order by date) as "Cumulative Volume",

count(DISTINCT token_addy) as "Intercahin tokens",

count(DISTINCT sender) as "Senders"

from trs

GROUP by all