with initial_claims as (

select

RECEIVER as user,

sum(AMOUNT / 1e6) as total_claimed

from lava.core.fact_transfers

where SENDER = 'lava@188kzvhru5ch303a2h78a2kya9dp7gup9fkpd2t'

and TRANSFER_TYPE = 'LAVA'

and TX_SUCCEEDED = 'TRUE'

group by RECEIVER

),

total_sales as (

select

SENDER as user,

sum(AMOUNT / 1e6) as total_sent

from lava.core.fact_transfers

where SENDER in (select user from initial_claims)

and TRANSFER_TYPE = 'LAVA'

and TX_SUCCEEDED = 'TRUE'

group by SENDER

),

user_status as (

select

a.user,

a.total_claimed,

coalesce(b.total_sent, 0) as total_sent,

(a.total_claimed - coalesce(b.total_sent, 0)) as total_held

from initial_claims a

left join total_sales b on a.user = b.user

user,