with

flowpass_buys as (

SELECT distinct event_data:to as address, trunc(block_timestamp,'hour') as hour,

count(distinct event_data:id) as counts

from flow.core.fact_events where event_type='Deposit'

and event_contract='A.9212a87501a8a6a2.FlowversePass'

group by 1,2

),

flowpass_sells as (

SELECT distinct event_data:from as address, trunc(block_timestamp,'hour') as hour,

count(distinct event_data:id) as counts

from flow.core.fact_events where event_type='Withdraw'

and event_contract='A.9212a87501a8a6a2.FlowversePass'

group by 1,2

),

flowpass_final as (

select ifnull(x.hour,y.hour) as hour, ifnull(x.address,y.address) as address,

ifnull(x.counts,0) as buys, ifnull(y.counts,0) as sells

from flowpass_buys x

full join flowpass_sells y on x.hour=y.hour and x.address=y.address

),

flowpass_net as (

hour, address, buys-sells as nets, sum(nets) over (partition by address order by hour) as flowverse_pass_holdings

from flowpass_final

), --select * from flowpass_net --where address='0x9b879eee67d839cc'

flowpass_net_202376 as (

hour, address, buys-sells as nets

from flowpass_final where trunc(hour,'day')<='2023-07-04'

),

flowpass_202376 as (

select address, sum(nets) as holdings