sum(deposit / power(10, 24)) as volume_ins

FROM near.core.fact_transfers

group by 1

),

volume_out as (select TX_SIGNER,

sum(deposit / power(10, 24)) as volume_outs

from near.core.fact_transfers

group by 1

),

category as (SELECT TX_RECEIVER as users,

case when near_balance <10 then 'less than 10 Near balance'

when near_balance >=10 and near_balance <50 then 'balance between 10 to 50 Near'

when near_balance >=50 and near_balance <100 then 'balance between 50 to 100 Near'

when near_balance >=100 and near_balance <200 then 'balance between 100 to 200 Near'

when near_balance >=200 and near_balance <500 then 'balance between 200 to 500 Near'

when near_balance >=500 and near_balance <1000 then 'balance between 500 to 1K Near'

else 'balance more than 1K Near'

end as category

FROM (SELECT *,

volume_ins - volume_outs as near_balance

FROM volume_in

LEFT OUTER join volume_out on tx_signer = tx_receiver

HAVING near_balance > 0

)

)

SELECT CATEGORY,

count(DISTINCT users) as wallets_count

FROM near.core.fact_transactions

LEFT outer JOIN category ON users = TX_SIGNER

GROUP BY 1

HAVING not CATEGORY is NULL