select

asset_id,

asset_name

from algorand.asset

where creator_address = 'LOFTYRITC3QUX6TVQBGT3BARKWAZDEB2TTJWYQMH6YITKNH7IOMWRLC7SA'

and asset_deleted = 'FALSE'

),

property_circulating_supply as (

select

case when sender = 'LOFTYRITC3QUX6TVQBGT3BARKWAZDEB2TTJWYQMH6YITKNH7IOMWRLC7SA' then asset_receiver else sender end as user_address,

sum(case when sender = 'LOFTYRITC3QUX6TVQBGT3BARKWAZDEB2TTJWYQMH6YITKNH7IOMWRLC7SA' then asset_amount else -asset_amount end) as properties_owned,

count(distinct algorand.asset_transfer_transaction.asset_id) as number_of_different_properties,

properties_owned * 50 as total_token_investment,

total_token_investment / number_of_different_properties as average_investment_per_property

from algorand.asset_transfer_transaction

inner join property_list on (algorand.asset_transfer_transaction.asset_id = property_list.asset_id)

and (sender = 'LOFTYRITC3QUX6TVQBGT3BARKWAZDEB2TTJWYQMH6YITKNH7IOMWRLC7SA' or asset_receiver = 'LOFTYRITC3QUX6TVQBGT3BARKWAZDEB2TTJWYQMH6YITKNH7IOMWRLC7SA')

and asset_amount is not null

group by user_address

),

rank_table as (

select

*,

row_number() over (order by properties_owned desc) as rank,

count(user_address) over () as number_of_users_with_at_least_1_property

from property_circulating_supply

where properties_owned > 0

select * from rank_table

where rank < 11

order by rank