with mints as (
SELECT
split(pool_name, '.')[1] as pool_name_tmp,
split(pool_name_tmp, '-')[0] as pool_name_part,
split(pool_name, '.')[0] as network,
concat(pool_name_part, ' - ', network) as pool_name,
count(DISTINCT tx_id) as counter,
sum(from_amount_usd) as volume
from thorchain.swaps
where to_asset like '%/%'
GROUP by pool_name
),
burns as (
SELECT
split(pool_name, '.')[1] as pool_name_tmp,
split(pool_name_tmp, '-')[0] as pool_name_part,
split(pool_name, '.')[0] as network,
concat(pool_name_part, ' - ', network) as pool_name,
count(DISTINCT tx_id) as counter,
sum(from_amount_usd) * -1 as volume
from thorchain.swaps
where from_asset like '%/%'
GROUP by pool_name
)
SELECT
pool_name,
sum(counter) as counter,
sum(volume) as volume
from (
SELECT * from mints
UNION
SELECT * from burns
)
GROUP by pool_name