select BLOCK_TIMESTAMP, position,

collateralSymbol, collateralAmount, positionSize, userDeposit,

currencySymbol, positionId, trader, tx_hash

from (

select DECODED_LOG, a.BLOCK_TIMESTAMP,

case when a.ORIGIN_TO_ADDRESS='0x046299143a880c4d01a318bc6c9f2c0a5c1ed355'

then 'Short' else 'Long' end as position,

case when position='Long' then DECODED_LOG:collateralCurrency

else DECODED_LOG:currency end as collateralCurrency,

b.symbol as collateralSymbol,

case when position='Long' then DECODED_LOG:collateralAmount

else DECODED_LOG:downPayment+DECODED_LOG:principal end as collateralAmountRaw,

collateralAmountRaw/pow(10,b.DECIMALS) as collateralAmount,

collateralAmount*price as positionSize, AMOUNT_USD as userDeposit,

case when position='Long' then DECODED_LOG:currency

else DECODED_LOG:collateralCurrency end as currency,

case when c.symbol='USDB' then 'ETH'

else c.symbol end as currencySymbol,

DECODED_LOG:positionId as positionId,

DECODED_LOG:trader as trader,

a.TX_HASH

from blast.core.ez_decoded_event_logs a

left join blast.price.ez_prices_hourly b

on b.TOKEN_ADDRESS=collateralCurrency and hour=date_trunc(hour,BLOCK_TIMESTAMP)

left join blast.core.dim_contracts c

on c.address=currency

left join blast.core.ez_native_transfers d

on a.tx_hash=d.tx_hash

where a.ORIGIN_TO_ADDRESS in ('0x0301079dabdc9a2c70b856b2c51aca02bac10c3a','0x046299143a880c4d01a318bc6c9f2c0a5c1ed355')

and EVENT_NAME='PositionOpened')),

max_volume as

(select sum(AMOUNT_USD) as max_volume, count(*) as txs, tx_hash as tx

from blast.core.ez_token_transfers

where CONTRACT_ADDRESS='0x4300000000000000000000000000000000000004'