shaunoffAffiliate Fee - RUNE copy
    Updated 2024-11-14
    -- 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),

    QueryRunArchived: QueryRun has been archived