DATE_TRUNC('day', block_timestamp) AS block_day,

count(distinct TX_HASH)*-1 as burn_tx_count,

count (distinct origin_from_address)*-1 as burn_users_count,

sum(event_inputs:value/1e6)*-1 as burn_sum_volume

FROM

avalanche.core.fact_event_logs

WHERE

event_inputs:to = '0x0000000000000000000000000000000000000000'

and tx_status = 'SUCCESS'

AND contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

AND block_timestamp >= '2022-07-01'

AND event_name = 'Transfer'

group by block_day),

B as (SELECT

DATE_TRUNC('day', block_timestamp) AS block_day,

count(distinct TX_HASH) as mint_tx_count,

count (distinct origin_from_address) as mint_users_count,

sum(event_inputs:value/1e6) as mint_sum_volume

FROM

avalanche.core.fact_event_logs

WHERE

event_inputs:from = '0x0000000000000000000000000000000000000000'

and tx_status = 'SUCCESS'

AND contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

AND block_timestamp >= '2022-07-01'

AND event_name = 'Transfer'

group by block_day)

select * ,

sum(burn_sum_volume) over (order by A.block_day asc) as cum_burn_volume,

sum(mint_sum_volume) over (order by B.block_day asc) as cum_mint_volume,

cum_mint_volume + cum_burn_volume as cum_flow,

sum(burn_tx_count) over (order by A.block_day asc) as cum_burn_tx,