from_address,

percent_rank() over (ORDER BY sum(tx_fee * price) DESC) 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

from_address,

count(DISTINCT tx_hash) as txs

FROM optimism.core.fact_transactions

GROUP BY 1

HAVING txs = 1

)

GROUP BY 1

ORDER BY tx_fee_usd_percentile, active_months_percentile, wallets_interacted_with_percentile

LIMIT 100