-- forked from (THORSwap) Top 10 Directed Swap Pairs By Affiliate Address @ https://flipsidecrypto.xyz/edit/queries/dd2bdbc7-f3f0-4332-b970-d18ef5cc9822

WITH attempted_txs AS (

SELECT

TX_ID,

SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,

CASE

WHEN split_part(MEMO, ':', 5) != '' THEN split_part(MEMO, ':', 5)

WHEN (MEMO LIKE '%::0' AND MEMO NOT LIKE '+%') THEN 'te-ios'

ELSE NULL

END AS AFFILIATE_ADDRESS,

CASE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))

WHEN 'A' THEN 'AVAX.AVAX'

WHEN 'B' THEN 'BTC.BTC'

WHEN 'C' THEN 'BCH.BCH'

WHEN 'N' THEN 'BNB.BNB'

WHEN 'S' THEN 'BSC.BNB'

WHEN 'D' THEN 'DOGE.DOGE'

WHEN 'E' THEN 'ETH.ETH'

WHEN 'G' THEN 'GAIA.ATOM'

WHEN 'L' THEN 'LTC.LTC'

WHEN 'R' THEN 'THOR.RUNE'

ELSE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))

END AS OUTBOUND_ASSET,

COUNT(1) AS SUB_SWAP_COUNT,

COUNT(DISTINCT POOL_NAME) AS POOL_COUNT,

MIN(UPPER(SPLIT_PART(POOL_NAME, '-',1))) AS _ASSET_1,

MAX(UPPER(SPLIT_PART(POOL_NAME, '-',1))) AS _ASSET_2,

CASE

WHEN POOL_COUNT = 2 AND OUTBOUND_ASSET = _ASSET_1 THEN _ASSET_2

WHEN POOL_COUNT = 2 AND OUTBOUND_ASSET <> _ASSET_1 THEN _ASSET_1

WHEN POOL_COUNT = 1 AND OUTBOUND_ASSET = 'THOR.RUNE' THEN _ASSET_1

ELSE 'THOR.RUNE'

END AS INBOUND_ASSET

FROM thorchain.defi.fact_swaps_events