with eth_price as (

select hour::date as price_date,

avg(price) as price

from ethereum.price.ez_prices_hourly

where token_address = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

group by 1

, refund as (

select block_timestamp,

tx_hash,

-- case when block_timestamp::date < '2024-09-11' then '1-10/09' else '11-20/09' end as type,

decoded_log:amount / pow(10, 18) as refund,

refund * price as refund_usd

from ethereum.core.ez_decoded_event_logs

join eth_price on (block_timestamp::date = price_date)

where contract_address = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251'

and event_name = 'Refund'

and decoded_log:recipient = '0x555ce236c0220695b68341bc48c68d52210cc35b'

and decoded_log:token = '0x0000000000000000000000000000000000000000'

-- and block_timestamp::date >= '2024-09-01' and block_timestamp::date < '2024-09-21'

and block_timestamp::date > CURRENT_DATE - 30

, base as (

select block_timestamp,

tx_hash,

origin_function_signature,

-- case when block_timestamp::date < '2024-09-11' then '(1) 01-10/09' else '(2) 11-20/09' end as type,

case when origin_function_signature in ('0x40968794', '0x5b5a646e') then '0x409../0x5b5..' else 'Other' end as is_sig,

case when is_sig = '0x409../0x5b5..' then tx_fee + value else tx_fee end as tx_paid,

tx_paid * price as tx_paid_usd,