--3889258 with dune_table as (SELECT live.udf_api( 'https://api.dune.com/api/v1/query/3889258/results?api_key=y9IjkzucOrPXHWOWluAnCyeBHixk9LGE' ) as response), --9MIpVsNO8vlEEIpN5UqJ6dTmIRgl5QtU data_table as (select list:"data":"result":"rows" as c from (select PARSE_JSON(response) as list from dune_table)) , ethereum as (select date(block_timestamp) as date, 'Ethereum' as label, address, sum(amount) as netflow from (select block_timestamp, origin_from_address as address, amount as amount from ethereum.core.ez_token_transfers where to_address = lower('0x14C5a9007FDC5eE4De1581C892b5fd4623D8cdBf') and symbol = 'weETH' union all select block_timestamp, origin_to_address, -amount as amount from ethereum.core.ez_token_transfers where from_address = lower('0x14C5a9007FDC5eE4De1581C892b5fd4623D8cdBf') and symbol = 'weETH') group by 1,2,3 ) , date_array as ( select dateadd( day, '-' || row_number() over (order by null), dateadd(day, '+1', current_date()) ) as date from table (generator(rowcount => 120)) ), tvl_ether_filter as ( select distinct * from date_array cross join (select address from ethereum) ), final_table as (select date, address, case when tvl > 0 then 1 else 0 end as day_bal_count, tvl from (select date, address, sum(netflow_edited) over (partition by address order by date) as tvl from (select a.date, a.address, coalesce(b.netflow,0) as netflow_edited from tvl_ether_filter a left join ethereum b on a.date = b.date and a.address = b.address )) ), final_table_ether as (select rank() over (order by total_point desc) as "Rank", address , holding_duration , total_point from (select date, address, --day_bal_count, --tvl, sum(sum(day_bal_count)) over (partition by address order by date) as holding_duration, sum(sum(tvl*2400)) over (partition by address order by date) as total_point from final_table where day_bal_count = 1 group by 1,2) where date = current_date order by 1 asc), ethereum_point_segment as (select count(distinct address) as address_cnt, case when total_point > 0 and total_point<= 60000 then '<60K points' when total_point > 60001 and total_point <= 500000 then '<500K points' when total_point > 500001 and total_point <= 1000000 then '500K - 1M points' when total_point > 1000001 and total_point<= 5000000 then '1M - 5M points' when total_point > 5000001 and total_point<= 10000000 then '5M - 10M points' when total_point > 10000001 and total_point<= 15000000 then '10M - 15M points' when total_point > 15000001 and total_point<= 20000000 then '15M - 20M points' else '>20M points' end as point_segment from final_table_ether group by 2), linea_point_segment as ( SELECT f.value:"address_cnt" as address_cnt, case when f.value:"point_segment" = '<1M points' then '<500K points' else f.value:"point_segment" end as point_segment FROM data_table p, lateral flatten(input => p.c, path => '') f), arbitrum as (select date(block_timestamp) as date, 'Arbitrum' as label, address, sum(amount) as netflow from (select block_timestamp, origin_from_address as address, amount as amount from arbitrum.core.ez_token_transfers where to_address = lower('0x3c09fb9630b8f89f73871506f445f5dbedbeb0df') and symbol = 'weETH' union all select block_timestamp, origin_to_address, -amount as amount from arbitrum.core.ez_token_transfers where from_address = lower('0x3c09fb9630b8f89f73871506f445f5dbedbeb0df') and symbol = 'weETH') group by 1,2,3 ) , tvl_arb_filter as ( select distinct * from date_array cross join (select address from arbitrum) ), final_table_arb as (select date, address, case when tvl > 0 then 1 else 0 end as day_bal_count, tvl from (select date, address, sum(netflow_edited) over (partition by address order by date) as tvl from (select c.date, c.address, coalesce(d.netflow,0) as netflow_edited from tvl_arb_filter c left join arbitrum d on c.date = d.date and c.address = d.address )) ), last_table_arb as (select rank() over (order by total_point desc) as "Rank", address , holding_duration , total_point from (select date, address, --day_bal_count, --tvl, sum(sum(day_bal_count)) over (partition by address order by date) as holding_duration, sum(sum(tvl*2400)) over (partition by address order by date) as total_point from final_table_arb where day_bal_count = 1 group by 1,2) where date = current_date order by 1 asc) , arbitrum_point_segment as ( select count(distinct address) as address_cnt, case when total_point > 0 and total_point<= 60000 then '<60K points' when total_point > 60001 and total_point <= 500000 then '<500K points' when total_point > 500001 and total_point <= 1000000 then '500K - 1M points' when total_point > 1000001 and total_point<= 5000000 then '1M - 5M points' when total_point > 5000001 and total_point<= 10000000 then '5M - 10M points' when total_point > 10000001 and total_point<= 15000000 then '10M - 15M points' when total_point > 15000001 and total_point<= 20000000 then '15M - 20M points' else '>20M points' end as point_segment from last_table_arb group by 2 ), optimism as (select date(block_timestamp) as date, 'Optimism' as label, address, sum(amount) as netflow from (select block_timestamp, origin_from_address as address, amount as amount from optimism.core.ez_token_transfers where to_address = lower('0xea8eDdb19a980B6D9D77167ca08a70a692DA0DD7') and symbol = 'weETH' union all select block_timestamp, origin_to_address, -amount as amount from optimism.core.ez_token_transfers where from_address = lower('0xea8eDdb19a980B6D9D77167ca08a70a692DA0DD7') and symbol = 'weETH') group by 1,2,3 ) , tvl_op_filter as ( select distinct * from date_array cross join (select address from optimism) ), final_table_op as (select date, address, case when tvl > 0 then 1 else 0 end as day_bal_count, tvl from (select date, address, sum(netflow_edited) over (partition by address order by date) as tvl from (select e.date, e.address, coalesce(f.netflow,0) as netflow_edited from tvl_op_filter e left join optimism f on e.date = f.date and e.address = f.address )) ), last_table_op as (select rank() over (order by total_point desc) as "Rank", address , holding_duration , total_point from (select date, address, --day_bal_count, --tvl, sum(sum(day_bal_count)) over (partition by address order by date) as holding_duration, sum(sum(tvl*2400)) over (partition by address order by date) as total_point from final_table_op where day_bal_count = 1 group by 1,2) where date = current_date order by 1 asc), optimism_point_segment as ( select count(distinct address) as address_cnt, case when total_point > 0 and total_point<= 60000 then '<60K points' when total_point > 60001 and total_point <= 500000 then '<500K points' when total_point > 500001 and total_point <= 1000000 then '500K - 1M points' when total_point > 1000001 and total_point<= 5000000 then '1M - 5M points' when total_point > 5000001 and total_point<= 10000000 then '5M - 10M points' when total_point > 10000001 and total_point<= 15000000 then '10M - 15M points' when total_point > 15000001 and total_point<= 20000000 then '15M - 20M points' else '>20M points' end as point_segment from last_table_op group by 2 ), blast as (select date(block_timestamp) as date, 'Blast' as label, address, sum(amount) as netflow from (select block_timestamp, origin_from_address as address, amount as amount from blast.core.ez_token_transfers where to_address = lower('0x0817b88a528E2F5F980d26e98fC950CbD6aE31Ef') and symbol = 'weETH' union all select block_timestamp, origin_to_address, -amount as amount from blast.core.ez_token_transfers where from_address = lower('0x0817b88a528E2F5F980d26e98fC950CbD6aE31Ef') and symbol = 'weETH') group by 1,2,3 ) , tvl_blast_filter as ( select distinct * from date_array cross join (select address from blast) ), final_table_blast as (select date, address, case when tvl > 0 then 1 else 0 end as day_bal_count, tvl from (select date, address, sum(netflow_edited) over (partition by address order by date) as tvl from (select g.date, g.address, coalesce(h.netflow,0) as netflow_edited from tvl_blast_filter g left join blast h on g.date = h.date and g.address = h.address )) ), last_table_blast as (select rank() over (order by total_point desc) as "Rank", address, holding_duration , total_point from (select date, address, --day_bal_count, --tvl, sum(sum(day_bal_count)) over (partition by address order by date) as holding_duration, sum(sum(tvl*2400)) over (partition by address order by date) as total_point from final_table_blast where day_bal_count = 1 group by 1,2) where date = current_date order by 1 asc), blast_point_segment as ( select count(distinct address) as address_cnt, case when total_point > 0 and total_point<= 60000 then '<60K points' when total_point > 60001 and total_point <= 500000 then '<500K points' when total_point > 500001 and total_point <= 1000000 then '500K - 1M points' when total_point > 1000001 and total_point<= 5000000 then '1M - 5M points' when total_point > 5000001 and total_point<= 10000000 then '5M - 10M points' when total_point > 10000001 and total_point<= 15000000 then '10M - 15M points' when total_point > 15000001 and total_point<= 20000000 then '15M - 20M points' else '>20M points' end as point_segment from last_table_blast group by 2 ) select POINT_SEGMENT, sum(ADDRESS_CNT) as sum_add_cnt from (select * from ethereum_point_segment union all select * from linea_point_segment union all select * from arbitrum_point_segment union all select * from optimism_point_segment union all select * from blast_point_segment) group by 1

MITOSIS (EOL L1 Blockchain)