select

tr1.tx_hash,

lo.block_timestamp,

tr1.symbol as in_token,

tr2.symbol as out_token,

tr1.AMOUNT_USD as amount_in,

tr2.AMOUNT_USD as amount_out,

lo.decoded_log:taker as taker,

CASE

when amount_in is not null then amount_in

else amount_out

end as volume_usd,

CASE

WHEN in_token < out_token THEN in_token || '-' || out_token

ELSE out_token || '-' || in_token

END AS Pairs_trade

from

blast.core.ez_decoded_event_logs lo

left join blast.core.ez_token_transfers tr1 on lo.tx_hash = tr1.tx_hash

left join blast.core.ez_token_transfers tr2 on tr1.tx_hash = tr2.tx_hash

where

lo.contract_address = '0xb1a49c54192ea59b233200ea38ab56650dfb448c'

and lo.event_name = 'OrderComplete'

and tr1.from_Address = tr1.origin_from_address

and tr1.to_Address = '0xb1a49c54192ea59b233200ea38ab56650dfb448c'

and tr2.from_Address = '0xb1a49c54192ea59b233200ea38ab56650dfb448c'

and tr2.to_Address = tr2.origin_from_address

),

volume_data AS (

SELECT

date_trunc('day', block_timestamp) as date,

SUM(volume_usd) AS volume,

count(DISTINCT tx_hash) as tx_count,

AVG(SUM(volume_usd)) OVER () AS mean_volume,

STDDEV(SUM(volume_usd)) OVER () AS std_volume