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

from near.defi.ez_dex_swaps

where block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}' and

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

token_in_contract='usn' or -- USN

token_in_contract='fusd.tkn.near' or -- FUSD

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

token_in_contract='17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1' or -- USDC

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

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

group by 1,2),

tab2 as (select date_trunc('{{Time_Frame}}',block_timestamp) as date, symbol_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 block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}' and

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

token_out_contract='usn' or -- USN

token_out_contract='fusd.tkn.near' or -- FUSD

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

token_out_contract='17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1' or -- USDC

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

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

group by 1,2)

select tab1.date as "Date", tab1.symbol_in as "Stablecoin", selling_volume+buying_volume as "Swap Volume",

selling_volume AS "Selling Volume", buying_volume AS "Buying Volume",

buying_volume-selling_volume as "Net Volume",

SELLING_COUNT AS "Selling Count",

buying_count as "Buying Count",

selling_count+buying_count as "Swap Count", seller_count as "Seller Count", buyer_count as "Buyer Count"

from tab1 left join tab2 on tab1.date=tab2.date and tab1.symbol_in=tab2.symbol_out

order by 1