WITH graph AS(

ETH_VALUE as TX_VALUE_IN_ETH,

date_trunc('day', BLOCK_TIMESTAMP) AS DATE,

FROM_ADDRESS,

TO_ADDRESS,

FROM arbitrum.core.fact_transactions

WHERE (FROM_ADDRESS = {{whale}}

OR TO_ADDRESS= {{whale}})

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

AND STATUS='SUCCESS'

LIMIT 25000

),

prices AS(

date_trunc('day', RECORDED_HOUR) AS DATE,

avg(CLOSE) AS ETH_PRICE_USD

FROM crosschain.core.fact_hourly_prices

WHERE ID='ethereum'

GROUP BY 1

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

FROM graph g

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

HAVING TX_VALUE_USD>0

ORDER BY 1 DESC

LIMIT 10000