select date_trunc('hour', block_timestamp) AS date, token_1_currency, token_0_currency,

COUNT(DISTINCT tx_id) AS n_trades, sum(token_0_amount_usd) as trading_vol_token0

from terra.swaps

where token_1_currency is not NULL and token_0_currency is not null

GROUP BY date,token_1_currency, token_0_currency ORDER BY date DESC, n_trades DESC

) , ust as (

SELECT date as ust_date, sum(n_trades) as trades, sum(trading_vol_token0) as tvol

from native_swaps group by ust_date order by ust_date desc

), non_ust as (

SELECT date as no_ust_date, sum(n_trades) as nousttrades, sum(trading_vol_token0) as noustvol

from native_swaps where token_0_currency <> 'UST' and token_1_currency <> 'UST'

group by no_ust_date order by no_ust_date desc

),

final_one as (

select * from ust left join non_ust on non_ust.no_ust_date = ust.ust_date

)

select ust_date, sum(nousttrades) as non_ust_trd_amt, sum(trades) as trade_amt, sum(tvol) as ust_tot_vol_amt,

sum(noustvol) as non_ust_tot_vol_amt, (non_ust_trd_amt/trade_amt)*100 as non_ust_trd_percent,

(non_ust_tot_vol_amt/ust_tot_vol_amt)*100 as non_ust_vol_percent

from final_one group by ust_date order by ust_date desc