event_data:to as user,

date_trunc('week',block_timestamp) as date,

sum(event_data:amount) as "volume in (Flow)"

from flow.core.fact_events

where event_type = 'TokensDeposited'

and tx_succeeded = 'TRUE'

and event_contract = 'A.1654653399040a61.FlowToken'

and block_timestamp >= '2022-01-01'

group by 1,2

),flow_out as (select

event_data:from as user,

date_trunc('week',block_timestamp) as date,

sum(event_data:amount) as "volume out (Flow)"

from flow.core.fact_events

where event_type = 'TokensWithdrawn'

and tx_succeeded = 'TRUE'

and event_contract = 'A.1654653399040a61.FlowToken'

and block_timestamp >= '2022-01-01'

group by 1,2

) , main as (select

distinct a.user "holders",

a.date,

sum("volume in (Flow)" - "volume out (Flow)") as "volume of flow that hold"

from flow_in a join flow_out b on a.user = b.user

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

where a.user is not null

and a.user != 'null'

group by 1,2)

select

date,

count("holders") as "holders count",

sum("volume of flow that hold") as "total hold volume (flow)",

avg("volume of flow that hold") as "average hold volume (flow)"

from main

where "volume of flow that hold" > 0