sender,

total_amount as usd,

case

when total_amount < 1000 then 1.0

when total_amount >= 1000 and total_amount < 5000 then 1.05

when total_amount >= 5000 and total_amount < 10000 then 1.1

else 1.15

end as points

from (

select

sender,

sum(amount)/power(10,6)*0.75 as total_amount

from

osmosis.core.fact_transfers

where

transfer_type = 'IBC_TRANSFER_OUT' -- 'IBC_TRANSFER_IN'

and currency = 'uosmo'

and tx_succeeded = true

group by

sender

) as subquery

order by

total_amount desc;