P as( select hour, price from flow.price.ez_prices_hourly where symbol = 'FLOW' and price > 0 order by 1 desc limit 1),

X as( select block_timestamp, event_data as data, event_contract as contract, event_type as type from flow.core.fact_events where tx_succeeded = 'True'),

A as(

select data:initiator as user, sum(data:amount) as balance, sum(data:amount*price) as tvl

from X,P

where lower(contract) = 'a.a45ead1cf1ca9eda.flowrewards'

and type = 'Locked'

group by 1),

B as(

select rank() over(order by balance desc) as rank, balance

from A )

select

case when rank <= 10 then 'TOP 01-10'

when rank <= 100 then 'TOP 10-100'

else 'OTHER' end as tier,

sum(balance) as balance

from B

group by 1

order by 2 desc