NineRealmsAffiliate Fee Averaged Basis Points
    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), '/', '.'),
    '-',
    Last run: 2 months ago
    AFFILIATE
    RAW_AVERAGE_AFFILIATE_BPS
    WEIGHTED_AVERAGE_AFFILIATE_BPS
    1
    000
    2
    10.0067945643490.00618071157
    3
    300
    4
    BTC/BTC
    5
    bbs00
    6
    bot100100
    7
    cakewallet82.08333333333399.680957105
    8
    commission3030
    9
    dcf0.2835463258790.2401053572
    10
    dokwallet100100
    11
    ds11.65151515151513.627699842
    12
    dx8.273738237816.472633374
    13
    ej73.59967051070874.944725491
    14
    ferz53.33333333333330.005753759
    15
    giddy8080.024342471
    16
    iw2530.187580074
    17
    iwt3549.602362129
    18
    kf00
    19
    kk2424.802889592
    20
    lends00
    44
    1KB
    13s