from avalanche.core.fact_transactions

group by 2)

,

new as ( select DISTINCT from_address

from new_user

where date >= current_date - {{N_Days}})

,

swaps as ( select DISTINCT origin_from_address

from avalanche.core.ez_dex_swaps

where symbol_out = 'BFG'

and block_timestamp::date >= current_date - {{N_Days}}

select DISTINCT origin_from_address

from avalanche.core.ez_dex_swaps

where symbol_in = 'BFG'

and block_timestamp::date >= current_date - {{N_Days}})

,

final as ( select date(block_timestamp) as date, tx_hash,to_address, amount_usd

from avalanche.core.ez_token_transfers

where date >= current_date - {{N_Days}}

and origin_from_address in (select origin_from_address from swaps)

select date(block_timestamp) as date, tx_hash,avax_to_address as to_address, amount_usd

from avalanche.core.ez_avax_transfers

where date >= current_date - {{N_Days}}

and origin_from_address in (select origin_from_address from swaps))

select date, label_type, count(DISTINCT(tx_hash)) as total_tx, sum(amount_usd) as volume

from final a join avalanche.core.dim_labels b on a.to_address = b.address

where label_type in ('dapp','layer2','cex','defi','nft','dex')

group by 1,2