SELECT TX_ID, FROM_CURRENCY, TO_CURRENCY

FROM osmosis.core.fact_swaps

WHERE TX_STATUS = 'SUCCEEDED'

),

t2 AS (

SELECT TX_ID, TO_CURRENCY, Label AS From_label

FROM t1

LEFT JOIN osmosis.core.dim_labels ON t1.FROM_CURRENCY = osmosis.core.dim_labels.Address

),

t3 AS (

SELECT TX_ID, CONCAT(From_label,'➤',label) AS Swap_pair

FROM t2

LEFT JOIN osmosis.core.dim_labels ON t2.To_CURRENCY = osmosis.core.dim_labels.Address

),

t4 AS (

Select Swap_pair,

COUNT(Swap_Pair) AS TX_COUNT,

CONCAT(Round(AVG(LEFT(FEE,LEN(FEE)-5))/1e6,9),' OSMO') AS FEE_AVG,

AVG(LEFT(FEE,LEN(FEE)-5)) AS fees

FROM t3

JOIN osmosis.core.fact_transactions

ON osmosis.core.fact_transactions.TX_ID = t3.TX_ID

WHERE LEN(FEE) < 15

AND Block_timestamp::DATE >= CURRENT_DATE - 90

GROUP BY 1

)

SELECT Swap_pair,TX_COUNT,FEE_AVG

FROM t4

WHERE TX_COUNT > 5000

ORDER BY fees