with tab1 as (

SELECT

ORIGIN_FROM_ADDRESS as users,

sum(raw_amount / power(10, decimals)) as air_drop_volume

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts

ON address = contract_address

WHERE symbol LIKE 'OP'

AND from_address LIKE lower('0xFeDFAF1A10335448b7FA0268F56D2B44DBD357de')

AND ORIGIN_FUNCTION_SIGNATURE LIKE '0x2e7ba6ef'

GROUP by 1

), tab2 as (

SELECT

from_address,

percent_rank() over (ORDER BY sum(tx_fee * price)) as tx_fee_usd_percentile,

percent_rank() over (ORDER BY count(DISTINCT date_trunc('month', block_timestamp))) as active_months_percentile,

percent_rank() over (ORDER BY count(DISTINCT to_address)) as wallets_interacted_with_percentile,

sum(tx_fee * price) as tx_fee_usd,

count(DISTINCT date_trunc('month', block_timestamp)) as active_months,

count(DISTINCT to_address) as wallets_interacted_with

FROM optimism.core.fact_transactions

LEFT OUTER JOIN (

SELECT

hour,

avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol LIKE 'WETH'

GROUP BY 1

) ON hour = date_trunc('hour', block_timestamp)

WHERE not from_address in (

SELECT

from_address

FROM (

SELECT