with price as

(SELECT RECORDED_HOUR::date as datex,

SYMBOL, CURRENCY, avg(PRICE) as pr

from osmosis.price.ez_prices

GROUP by all

union

SELECT HOUR::date as datex,

'LAVA' as SYMBOL,

'ibc/1AEF145C549D4F9847C79E49710B198C294C7F4A107F4610DEE8E725FFC4B378' as CURRENCY,

avg(close) as pr

from crosschain.price.fact_prices_ohlc_hourly

where ASSET_ID = 'lava-network'

GROUP by all),

x as (select block_timestamp , tx_id,

x.symbol as from_token,

z.symbol as to_token,

case when from_CURRENCY = 'ibc/1AEF145C549D4F9847C79E49710B198C294C7F4A107F4610DEE8E725FFC4B378' then 6

else from_decimal end as from_decimalx,

case when to_CURRENCY = 'ibc/1AEF145C549D4F9847C79E49710B198C294C7F4A107F4610DEE8E725FFC4B378' then 6

else to_decimal end as to_decimalx,

(from_AMOUNT/pow(10, from_decimalx)) as "volumex", "volumex"*(x.pr) as "volume(USD)x" ,

(from_AMOUNT/pow(10, to_decimalx)) as "volumez", "volumez"*(z.pr) as "volume(USD)z" ,

("volume(USD)x"+"volume(USD)z")/2 as "volume(USD)fin"

from osmosis.defi.fact_swaps join price x on x.datex= block_timestamp::date

and from_CURRENCY = x.CURRENCY

join price z on z.datex= block_timestamp::date

and to_CURRENCY = z.CURRENCY

where

block_timestamp::date >= '2024-01-01'

and FROM_TOKEN = 'LAVA'