with base as (select * from optimism.core.dim_labels where project_name ilike '%uni%'), base2 as (select tx_hash from optimism.core.fact_event_logs where event_name = 'Swap' and origin_to_address in (select address from base) and block_timestamp >= '2022-06-15'), base3 as (select block_timestamp, event_inputs:from as swapper, event_inputs:value/1e6 as swap_size from optimism.core.fact_event_logs where contract_address ilike '0x7F5c764cBc14f9669B88837ca1490cCa17c31607' and event_name = 'Transfer' and tx_hash in (select tx_hash from base2) and event_inputs:value > 0), base1 as (select * from ethereum.core.dim_labels where address_name ilike '%uni%'), base12 as (select tx_hash from ethereum.core.fact_event_logs where event_name = 'Swap' and origin_to_address in (select address from base1) and block_timestamp >= '2022-06-15'), base13 as (select block_timestamp, event_inputs:from as swapper, event_inputs:value/1e6 as swap_size from ethereum.core.fact_event_logs where contract_address ilike '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48' and event_name = 'Transfer' and tx_hash in (select tx_hash from base12) and event_inputs:value > 0), base20 as (select swapper, avg(swap_size) as avg_swap_size, case when avg_swap_size > 0 and avg_swap_size <= 100 then 'Fish > 0 and <= 100' when avg_swap_size > 100 and avg_swap_size <= 1000 then 'Shrimp > 100 and <= 1K' when avg_swap_size > 1000 and avg_swap_size <= 10000 then 'Crab > 1K and <= 10K' when avg_swap_size > 10000 and avg_swap_size <= 100000 then 'Dolphin > 10K and <= 100K' when avg_swap_size > 100000 and avg_swap_size <= 1000000 then 'Shark > 100K and <= 1M' else 'Whale > 1M' end as avg_swap_size_ranges, 1 as numbering, 'Optimism' from base3 group by 1) select avg_swap_size_ranges, case when avg_swap_size_ranges = 'Fish > 0 and <= 100' then 1 when avg_swap_size_ranges = 'Shrimp > 100 and <= 1K' then 2 when avg_swap_size_ranges = 'Crab > 1K and <= 10K' then 3 when avg_swap_size_ranges = 'Dolphin > 10K and <= 100K' then 4 when avg_swap_size_ranges = 'Shark > 100K and <= 1M' then 5 when avg_swap_size_ranges = 'Whale > 1M' then 6 end as orderings, sum(numbering) from base20 group by 1 order by 2 asc

Average Optimism Portfolio