select

recorded_at::date as date,

Symbol,

avg(price) as avg_price

from osmosis.core.dim_prices

group by 1,2

),

transfer as (

SELECT

BLOCK_TIMESTAMP,

TX_ID,

SENDER,

AMOUNT/pow(10,DECIMAL) as "Adjusted Amount",

lower(split(currency,'-')[0]) as Symbol,

iff(Symbol ilike 'u%', substring(Symbol, 2, LEN(Symbol)), Symbol) as Token

from axelar.core.fact_transfers

where 1=1

and TX_SUCCEEDED = 'TRUE'

and TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

AND AMOUNT is not NULL

AND CURRENCY is not NULL

AND not CURRENCY ilike any ('factory%', 'gravity%')

)

BLOCK_TIMESTAMP::date as date,

Token,

count(distinct tx_id) as "# TXs",

COUNT(DISTINCT sender) as "# Users",

sum("Adjusted Amount" * b.avg_price) as "Volume-USD"

FROM transfer a left join price b on a.BLOCK_TIMESTAMP::date = b.date and a.token = lower(b.symbol)

WHERE BLOCK_TIMESTAMP >= '2022-11-02'

and BLOCK_TIMESTAMP < CURRENT_DATE

GROUP by 1,2