algo as (

select trunc(block_timestamp,'day') as date,

count(distinct swapper) as total_unique_swapper,

count(distinct TX_GROUP_ID) as total_swaps,

sum(swap_to_amount*price_usd) as total_volume_in_usd,

avg(swap_to_amount*price_usd) average_volume_swapped,

median(swap_to_amount*price_usd) median_volume_swapped,

min(swap_to_amount*price_usd) min_volume_swapped,

max(swap_to_amount*price_usd) max_volume_swapped

from algorand.defi.fact_swap x

join algorand.defi.ez_price_swap y on trunc(x.block_timestamp,'hour')=y.block_hour and x.SWAP_TO_ASSET_ID=y.asset_id

--join algorand.nft.ez_nft_asset y on x.nft_asset_id=y.nft_asset_id

where swap_to_amount != 0

and block_timestamp::date >= current_date - INTERVAL '{{Number}} {{Period}}' group by 1

),

flow_prices as (

SELECT

date_trunc('day',timestamp) as days,

token_contract,

avg(price_usd) as price_usd

from flow.core.fact_prices

group by 1,2

),

flow as (

select trunc(block_timestamp,'day') as date,

count(distinct trader) as total_unique_swapper,

count(distinct tx_id) as total_swaps,

sum(token_out_amount*price_usd) as total_volume_in_usd,

avg(token_out_amount*price_usd) average_volume_swapped,

median(token_out_amount*price_usd) median_volume_swapped,

min(token_out_amount*price_usd) min_volume_swapped,

max(token_out_amount*price_usd) max_volume_swapped

from flow.core.ez_swaps x

join flow_prices y on trunc(x.block_timestamp,'day')=y.days and x.TOKEN_OUT_CONTRACT=y.token_contract

where token_out_amount != 0