with initial_claims as (

select

RECEIVER as user,

sum(AMOUNT / 1e6) as total_claimed,

min(BLOCK_TIMESTAMP) as first_claim_timestamp

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,

zeroifnull(total_sent) as Lava_sent,

case

when LAVA_sent = 0 then 'HODL'

when LAVA_sent > 0 and Lava_sent < total_claimed then 'Partial sold'

else 'Fully sold'

end as status

from initial_claims a

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