select

BLOCK_TIMESTAMP::date as receive_date,

event_data:to as to_wllet,

sum(event_data:amount) as deposit_amt

from flow.core.fact_events

where event_contract = 'A.1654653399040a61.FlowToken'

and event_type = 'TokensDeposited'

and tx_succeeded = 'TRUE'

group by 1,2

)

, tb2 as (

select

BLOCK_TIMESTAMP::date as send_date,

event_data:from as from_wallet,

sum(event_data:amount) as withdraw_amt

from flow.core.fact_events

where event_contract = 'A.1654653399040a61.FlowToken'

and event_type = 'TokensWithdrawn'

and tx_succeeded = 'TRUE'

group by 1,2

)

, final as (

select

a.to_wllet,

datediff(day, receive_date, send_date) as holding_time,

sum(deposit_amt - withdraw_amt) as tot_balance

from tb1 a join tb2 b on a.to_wllet = b.from_wallet

full outer join flow.core.dim_contract_labels c on a.to_wllet = c.account_address

where a.to_wllet != 'null'

group by 1,2

HAVING tot_balance > 1

)

SELECT

case

when holding_time < 10 then '< 10 day Hold'