SELECT

tx_receiver,

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

FROM near.core.fact_transfers

GROUP BY 1

), tab2 as (

SELECT

TX_SIGNER,

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

FROM near.core.fact_transfers

GROUP BY 1

), tab3 as (

SELECT

*,

in_volume - out_volume as net_balance

FROM tab1

LEFT OUTER join tab2

on tx_signer = tx_receiver

HAVING net_balance > 0

), tab4 as (

SELECT

tx_receiver as user1,

CASE WHEN net_balance < 10 THEN 'a Below 10 NEAR'

WHEN net_balance < 100 THEN 'b Between 10 and 100 NEAR'

WHEN net_balance < 1000 THEN 'c Between 100 and 1,000 NEAR'

WHEN net_balance < 10000 THEN 'd Between 1,000 and 10,000 NEAR'

WHEN net_balance < 100000 THEN 'e Between 10,000 and 100,000 NEAR'

WHEN net_balance < 1000000 THEN 'f Between 100,000 and 1,000,000 NEAR'

ELSE 'g Over 1,000,000 NEAR' end as category

FROM tab3

SELECT

date_trunc('week', block_timestamp) as week,

CATEGORY,