blockchain AS Blockchain,

CASE

WHEN POSITION('-' IN from_asset) > 0 THEN SPLIT_PART(from_asset, '-', 1)

WHEN POSITION('~' IN from_asset) > 0 THEN SPLIT_PART(from_asset, '~', 1)

WHEN POSITION('/' IN from_asset) > 0 THEN SPLIT_PART(from_asset, '/', 1)

ELSE from_asset

END AS AssetName,

CASE

WHEN POSITION('-' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '-', 1)

WHEN POSITION('~' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '~', 1)

WHEN POSITION('/' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '/', 1)

ELSE pool_name

END AS PoolAssetName,

CASE

WHEN POSITION('-' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '-', 2)

WHEN POSITION('~' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '~', 2)

WHEN POSITION('/' IN pool_name) > 0 THEN SPLIT_PART(pool_name, '/', 2)

ELSE NULL

END AS PoolIdentifier,

SUM(from_amount_usd) AS TotalSwapVolumeUsd,

AVG(swap_slip_bp) AS AvgSwapSlipBp,

MAX(swap_slip_bp) AS MaxSwapSlipBp,

MIN(swap_slip_bp) AS MinSwapSlipBp

FROM

thorchain.defi.fact_swaps

GROUP BY

Blockchain, AssetName, PoolAssetName, PoolIdentifier

ORDER BY

AvgSwapSlipBp DESC

LIMIT 5;