Query editor
WITH base AS (with stake as (select"from" as address,sum(value/1e18) as stakedfrom erc20_base.evt_transferwhere contract_address = 0x98d0baa52b2d063e780de12f615f963fe8537553and "to" = 0x548D3B444da39686d1a6F1544781d154e7cD1EF7group by 1order by 2 desc),unstake as (select"to" as address,sum(value/1e18) as unstakedfrom erc20_base.evt_transferwhere contract_address = 0x98d0baa52b2d063e780de12f615f963fe8537553and "from" = 0x548D3B444da39686d1a6F1544781d154e7cD1EF7group by 1order by 2 desc),stake_ranking as (selectrow_number() over(order by staked-coalesce(unstaked, 0) desc) as rk,address,staked-coalesce(unstaked, 0) as total_stakedfrom stakeleft join unstake using(address)order by 1 asc)selectsr.rk,address,total_staked,coalesce(claimed, 0) as claimed,total_staked / claimed as percentage,(casewhen (total_staked / claimed > 0.75 or claimed = 0) and total_staked > 100000 then 'GOLD'when total_staked / claimed > 0.75 then 'SILVER'when total_staked / claimed > 0.5 then 'BLUE'else 'NORMAL'end) as typesfrom stake_ranking srleft join query_4751506 using(address)order by rk asc)SELECTb.*,e.name AS ens_nameFROM base bLEFT JOIN labels.ens eON e.address = b.addressORDER BY b.rk ASC;