NineRealmsTop 20 Affiliates (By Affiliate Fees USD)
    Updated 2024-11-22
    -- forked from Top 10 Affiliates (By Volume USD) @ https://flipsidecrypto.xyz/edit/queries/94af4cff-a83c-498b-b9d4-5c79895eb063

    -- 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
    Last run: 2 months ago
    AFFILIATE
    TOTAL_AFFILIATE_FEES_USD
    1
    ti2647413.50673581
    2
    t888638.977545612
    3
    ss429594.051467181
    4
    td301824.543640814
    5
    wr262663.491350484
    6
    dx106398.843012226
    7
    xdf81448.069456823
    8
    ts70379.923541339
    9
    tl30519.521177107
    10
    thor1ap5vn4svwkpch2c9jm7hlpr2pj47e62xwpcvtw22805.303515732
    11
    ds20993.179333822
    12
    pl16336.217713143
    13
    rg11945.109837716
    14
    ej10289.272390161
    15
    oky3433.246731453
    16
    sy1032.753615931
    17
    commission360.159555601
    18
    ferz149.066502532
    19
    dcf110.003992993
    20
    valswappa58.929496037
    20
    516B
    13s