WITH graph AS(

ETH_VALUE as TX_VALUE_IN_ETH,

date_trunc('day', BLOCK_TIMESTAMP) AS DATE,

FROM_ADDRESS,

TO_ADDRESS,

TX_HASH,

TX_JSON:receipt:logs[0]:address AS currency

FROM optimism.core.fact_transactions

WHERE (FROM_ADDRESS = {{whale}}

OR TO_ADDRESS= {{whale}})

AND (BLOCK_TIMESTAMP BETWEEN {{start_date}} AND {{end_date}})

AND STATUS='SUCCESS'

),

prices AS(

date_trunc('day', HOUR) AS DATE,

avg(PRICE) AS ETH_PRICE_USD

FROM crosschain.core.ez_hourly_prices

WHERE SYMBOL='WETH'

GROUP BY 1

ORDER BY 1 DESC

SELECT

g.TX_VALUE_IN_ETH*p.ETH_PRICE_USD AS TX_VALUE_USD,

g.date,

g.FROM_ADDRESS,

g.TO_ADDRESS,

g.TX_HASH,

p.ETH_PRICE_USD,

g.TX_VALUE_IN_ETH,

g.currency

FROM graph g

LEFT JOIN prices p ON g.date=p.date