(SELECT

SUM(FROM_AMOUNT_USD) AS Bought_amount

, FROM_ASSET

FROM

thorchain.defi.fact_swaps

WHERE

BLOCK_TIMESTAMP > CURRENT_DATE - 30

GROUP BY 2

),

sold AS

(SELECT

SUM(FROM_AMOUNT_USD) AS sold_amount

, to_ASSET

FROM

thorchain.defi.fact_swaps

WHERE

BLOCK_TIMESTAMP > CURRENT_DATE - 30

GROUP BY 2

),

FINAL AS

(SELECT

COALESCE(FROM_ASSET,TO_ASSET) AS Contract

, COALESCE(BOUGHT_AMOUNT,0) AS BUY

, COALESCE(SOLD_AMOUNT,0) AS SELL

FROM

Bought A FULL JOIN sold B ON A.FROM_ASSET=B.to_ASSET),

FINAL0 AS

(SELECT

COALESCE(SPLIT(CONTRACT,'.')[1],SPLIT(CONTRACT,'~')[1],SPLIT(CONTRACT,'/')[1]) AS Contract

, BUY

, SELL