count(distinct tx_hash) as selling_count, count(distinct trader) as seller_count

from near.defi.ez_dex_swaps

where

(token_in_contract='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' or -- USDC.e

token_in_contract='usdt.tether-token.near' or --USDt **

token_in_contract='dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' or -- USDT.e

token_in_contract='usn' or -- USN

token_in_contract='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' or -- DAI

token_in_contract='17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1') --USDC

and amount_in<50000000 and token_in is not null

group by 1,2),

tab2 as (select date_trunc('{{Time_Frame}}',block_timestamp) as date, token_out, sum(amount_out) as buying_volume,

count(distinct tx_hash) as buying_count, count(distinct trader) as buyer_count

from near.defi.ez_dex_swaps

where (token_out_contract='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' or -- USDC.e

token_out_contract='usdt.tether-token.near' or --USDt **

token_out_contract='dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' or -- USDT.e

token_out_contract='usn' or -- USN

token_out_contract='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' or -- DAI

token_out_contract='17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1') --USDC

and amount_out<50000000 and token_out is not null

group by 1,2)

select tab1.date as "Date", tab1.token_in as "Stablecoin", case when selling_volume is null then 0 else selling_volume end as "Selling Volume",

case when buying_volume is null then 0 else buying_volume end as "Buying Volume",

case when selling_count is null then 0 else selling_count end as "Selling Count",

case when buying_count is null then 0 else buying_count end as "Buying Count",

case when seller_count is null then 0 else seller_count end as "Seller Count",

case when buyer_count is null then 0 else buyer_count end as "Buyer Count"

from tab1 left join tab2 on tab1.date=tab2.date and tab1.token_in=tab2.token_out

order by 1)

select "Stablecoin", round(sum("Selling Volume")) as "Selling Volume", round(SUM("Buying Volume")) as "Buying Volume", round(sum("Selling Volume")+sum("Buying Volume")) as "Swap Volume",

sum("Selling Count") as "Selling Count", sum("Buying Count") as "Buying Count", sum("Selling Count")+sum("Buying Count") as "Swap Count"