SELECT

hour::date AS hour,

AVG(price) as price,

FROM ethereum.price.ez_prices_hourly

WHERE symbol = 'WETH'

and hour::date >= '2024-12-20'

GROUP BY 1

),

mints AS (

block_timestamp,

tx_hash,

(CASE

WHEN decoded_log:dropStageIndex = 1 THEN 'Phase1'

WHEN decoded_log:dropStageIndex = 2 THEN 'Phase2'

END

) AS phase,

decoded_log:feeRecipient AS fee_recipient,

decoded_log:minter AS minter,

decoded_log:quantityMinted AS quantity_minted,

decoded_log:unitMintPrice / pow(10,18) AS unit_mint_price,

unit_mint_price * quantity_minted AS mint_price,

mint_price * price AS mint_price_usd,

FROM ethereum.core.ez_decoded_event_logs

INNER join prices on hour = block_timestamp::date

WHERE block_timestamp::date >= '2024-12-20'

AND event_name = 'SeaDropMint'

AND decoded_log:nftContract = lower('0x9830b32f7210f0857A859c2A86387e4d1bB760B8')

),

phase1 AS(

SELECT tx_hash, tx_fee, 'Phase1' AS phase

FROM ethereum.core.fact_transactions

where tx_hash in (select tx_hash from mints WHERE phase = 'Phase1')