SELECT date_hour AS hour, LAST_VALUE(price IGNORE NULLS) OVER (ORDER BY date_hour) AS eth_usd

FROM crosschain.core.dim_date_hours

LEFT JOIN ethereum.price.ez_hourly_token_prices p ON date_hour = p.hour

AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

),

chains AS (

SELECT $1 AS chain_id, $2 AS chain_name

FROM (VALUES

(1, 'ETH'),

(10, 'OP'),

(42161, 'ARB'),

(324, 'ZK'),

(137, 'MATIC')

)

),

l2_receptions AS (

SELECT e.tx_hash, e.block_timestamp, e.decoded_log:deadline AS deadline,

e.decoded_log:amount AS amount, e.decoded_log:recipient AS recipient,

e2.decoded_log:tokensBought AS tokens_bought

FROM optimism.core.ez_decoded_event_logs e

LEFT JOIN optimism.core.ez_decoded_event_logs e2 ON e.tx_hash = e2.tx_hash

AND e2.block_timestamp = e.block_timestamp

AND e2.topics[0] = '0xc6c1e0630dbe9130cc068028486c0d118ddcea348550819defd5cb8c257f8a38'

AND e2.decoded_log:tokensSold = e.decoded_log:amount

WHERE e.block_timestamp >= CURRENT_DATE - 300

AND e.contract_address = '0x83f6244bd87662118d96d9a6d44f09dfff14b30e'

AND e.topics[0] = '0x320958176930804eb66c2343c7343fc0367dc16249590c0f195783bee199d094'

AND e.tx_hash = '0xeb49283ecd00fc9b47192ccc1bcf4213e61ca3f38e225b769a821edabf4fe131'

),

withdrawals AS (

SELECT e.*

FROM ethereum.core.ez_decoded_event_logs e

JOIN ethereum.core.fact_transactions t ON e.tx_hash = t.tx_hash

JOIN chains ch ON ch.chain_id = e.decoded_log:chainId

LEFT JOIN eth_price p ON p.hour = TRUNC(e.block_timestamp, 'hour')