token_contract, ifnull(avg (price_usd),0) as price_in_USD

from flow.core.fact_prices

where token != 'Blocto'

group by 1,2,3,4)

select

case when token_in_contract = token_contract then 'Swap From Token'

when token_out_contract = token_contract then 'Swap To Token'

else null end as swap_type,

count (distinct tx_id) as number_of_swaps,

count (distinct trader) as number_of_swappers,

sum (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as total_amount_token,

sum (case when token_in_contract = token_contract then token_in_amount*price_in_USD

when token_out_contract = token_contract then token_out_amount*price_in_USD end) as Total_USD_amount

from flow.core.ez_swaps t1 join table1 t2 on (t1.token_in_contract = t2.token_contract or t1.token_out_contract = t2.token_contract)

and t1.block_timestamp::Date = t2.day

where symbol ilike '{{ticker}}'

group by 1