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'

and DATEDIFF('day', '2024-07-30', BLOCK_TIMESTAMP) <= 1

group by RECEIVER

),

claimers_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'

and DATEDIFF('day', '2024-07-30', BLOCK_TIMESTAMP) <= 1

group by SENDER

),

purchases_after_airdrop as (

select

RECEIVER as user,

sum(AMOUNT / 1e6) as total_bought,

min(BLOCK_TIMESTAMP) as first_purchase_timestamp

from lava.core.fact_transfers

where RECEIVER not in (select user from initial_claims)

and SENDER != 'lava@188kzvhru5ch303a2h78a2kya9dp7gup9fkpd2t'

and TRANSFER_TYPE = 'LAVA'

and TX_SUCCEEDED = 'TRUE'

and DATEDIFF('day', '2024-07-30', BLOCK_TIMESTAMP) <= 1