Query editor
-- list of labeled ETH depositors with share/*This query calculates the cumulative amounts of ETH deposited by projectsand computes the shares for each project*/with indexes AS (SELECT pubkey,index as validator_index,max_by(name, amount) as name,max_by(name1, amount) as name1FROM dune.lido.result_deposits_assigned_to_projectsGROUP BY 1, 2), balances as (SELECT case when trim(i.name1) = '' or trim(i.name1) = 'Unidentified' then COALESCE(i.name, 'Unidentified') else i.name1 end as name,i.name1,-- exited Vs can be in the beacon.validators, we don't account their effective balances for this widgetcase when exit_epoch is not null then if(date_trunc('minute', from_unixtime(1606824023 + (exit_epoch * 12 * 32))) <= updated_at, 0, cast(v.effective_balance as double))else cast(v.effective_balance as double) end as effective_balanceFROM beacon.validators vLEFT JOIN indexes i on i.pubkey = v.public_key), depositors_list_with_share as (SELECT name,--case when trim(name1) = '' then name else name1 end name1,sum(effective_balance)/1e9 as eth_deposited,sum(effective_balance) / (SELECT sum(effective_balance) FROM balances) * 100.0 as shareFROM balancesGROUP BY 1--, 2ORDER BY 3 DESC), depositors_list_with_cumu_share as (SELECTname,eth_deposited,share,SUM(share) OVER (order by share desc) as cumu_shareFROM depositors_list_with_share--query_5164353--2410129 --ETH net deposits by projectORDER BY 2 desc)SELECT name, SUM(eth_deposited) as eth_depositedFROM (--select depositors that have 80% of total deposits in order to display them with labels and all others in "Other" categorySELECTname,--case when trim(name1) = '' then COALESCE(name, 'Unidentified') else name1 end as name,eth_deposited,shareFROM depositors_list_with_cumu_shareWHERE cumu_share <= 85union all