flipside

    Affiliate Fee - RUNE copy
    shaunoffAffiliate Fee - RUNE copy
    Updated 2025-03-19
    -- forked from winnie-fs / Affiliate Fee - RUNE @ https://flipsidecrypto.xyz/winnie-fs/q/eBgZt8kUEw5J/affiliate-fee---rune

    with base as (select tx_id,
    date_trunc('{{granularity}}', block_timestamp) as date,
    affiliate_address, affiliate_fee_basis_points,
    split(from_asset, '-')[0] as from_assets,
    case
    when from_assets ilike '%/%' then split(from_assets, '/')[1]
    else split(from_assets, '.')[1]
    end as from_asset_names,
    split(to_asset, '-')[0] as to_assets,
    case
    when to_assets ilike '%/%' then split(to_assets, '/')[1]
    else split(to_assets, '.')[1]
    end as to_asset_names,
    concat(from_asset_names, ' -> ', to_asset_names) as assets,
    case when assets ilike '%RUNE' then 2
    else 1
    end as numbering,
    sum(from_amount_usd/rune_usd) as swap_volume
    from thorchain.defi.fact_swaps
    where date >= '{{start_date}}'
    and date <= '{{end_date}}'--and tx_id = '902C27D4CDDF672D7E88F0CCBCB402ED1303827E90631320C849F5DD1294D5E3'
    --and tx_id in (select tx_id from thorchain.defi.fact_swaps_events where memo ilike '%/%/%')
    group by tx_id, date,
    affiliate_address, affiliate_fee_basis_points,
    from_asset, to_asset),

    base2 as (select date,
    tx_id,
    affiliate_address, affiliate_fee_basis_points,
    array_agg(distinct assets) within group (order by assets asc) as swap_direction, --merging 2 sep path to 1
    sum(swap_volume) as swap_volume
    from base
    group by 1,2,3,4),

    Last run: 20 days ago
    DATE
    Affiliates
    AFFILIATE_FEE
    AFFILIATE_FEE_WITH_RANK
    BASE4_SUM
    RANK
    AFFILIATE_FEE_SUM
    1
    		2022-09-01 00:00:00.000THORSwap19198.9434149761THORSwap1346668.1426784211004519.36346791
    2
    		2023-06-01 00:00:00.000THORSwap39350.3829450131THORSwap1346668.1426784211004519.36346791
    3
    		2023-11-01 00:00:00.000THORSwap86122.6130556331THORSwap1346668.1426784211004519.36346791
    4
    		2022-12-01 00:00:00.000THORSwap37415.2645363241THORSwap1346668.1426784211004519.36346791
    5
    		2022-05-01 00:00:00.000THORSwap72787.1760205051THORSwap1346668.1426784211004519.36346791
    6
    		2023-02-01 00:00:00.000THORSwap22716.5506571551THORSwap1346668.1426784211004519.36346791
    7
    		2023-08-01 00:00:00.000THORSwap148326.1365340551THORSwap1346668.1426784211004519.36346791
    8
    		2023-03-01 00:00:00.000THORSwap26272.0809551881THORSwap1346668.1426784211004519.36346791
    9
    		2022-04-01 00:00:00.000THORSwap17478.9236782351THORSwap1346668.1426784211004519.36346791
    10
    		2023-07-01 00:00:00.000THORSwap48308.0969159261THORSwap1346668.1426784211004519.36346791
    11
    		2022-10-01 00:00:00.000THORSwap12585.1573276681THORSwap1346668.1426784211004519.36346791
    12
    		2022-06-01 00:00:00.000THORSwap49487.4609425981THORSwap1346668.1426784211004519.36346791
    13
    		2023-10-01 00:00:00.000THORSwap171008.6698032211THORSwap1346668.1426784211004519.36346791
    14
    		2023-09-01 00:00:00.000THORSwap57296.1426326091THORSwap1346668.1426784211004519.36346791
    15
    		2023-04-01 00:00:00.000THORSwap18048.2175655251THORSwap1346668.1426784211004519.36346791
    16
    		2022-07-01 00:00:00.000THORSwap25452.4076671851THORSwap1346668.1426784211004519.36346791
    17
    		2022-08-01 00:00:00.000THORSwap23660.056647281THORSwap1346668.1426784211004519.36346791
    18
    		2023-12-01 00:00:00.000THORSwap45171.8431708611THORSwap1346668.1426784211004519.36346791
    19
    		2022-11-01 00:00:00.000THORSwap31336.0365167691THORSwap1346668.1426784211004519.36346791
    20
    		2023-01-01 00:00:00.000THORSwap37412.854184581THORSwap1346668.1426784211004519.36346791
    ...
    274
    31KB
    23s