BUY AS (SELECT

DATE_TRUNC ('DAY',BLOCK_TIMESTAMP) AS DATE,

count (DISTINCT TX_HASH) as txn,

count (DISTINCT TRADER) as users,

sum (AMOUNT_IN) as Volume

FROM near.core.ez_dex_swaps

WHERE TOKEN_IN ilike '%usdt%'

AND TOKEN_OUT NOT IN (SELECT TOKEN_OUT FROM near.core.ez_dex_swaps WHERE TOKEN_OUT ilike '%usdt%')

AND BLOCK_TIMESTAMP >= CURRENT_DATE -30

GROUP BY 1),

SELL AS (SELECT

DATE_TRUNC ('DAY',BLOCK_TIMESTAMP) AS DATE,

count (DISTINCT TX_HASH) as txn,

count (DISTINCT TRADER) as users,

sum (AMOUNT_OUT) as Volume

FROM near.core.ez_dex_swaps

WHERE TOKEN_OUT ilike '%usdt%'

AND TOKEN_IN NOT IN (SELECT TOKEN_IN FROM near.core.ez_dex_swaps WHERE TOKEN_IN ilike '%usdt%')

AND BLOCK_TIMESTAMP >= CURRENT_DATE -30

GROUP BY 1),

final as (SELECT B.DATE AS DATE,

SUM (B.txn - S.txn) AS NET_txn,

SUM (B.users - S.users) AS NET_users,

SUM (B.Volume - S.Volume) AS NET_VOLUME

FROM BUY B JOIN SELL S ON B.DATE::DATE = S.DATE::DATE

GROUP BY 1)

SELECT 'Last Week' as Type,

sum (NET_txn) as NET_txn,

sum (NET_users) as NET_users,

sum (NET_VOLUME) as NET_VOLUME