flipside

    Weekly Affiliate Fees copy
    winnie-fsWeekly Affiliate Fees copy
    Updated 2025-02-11
    -- forked from Spot-Wiggum / Weekly Affiliate Fees @ https://flipsidecrypto.xyz/Spot-Wiggum/q/mOPR-zprgvgC/weekly-affiliate-fees

    -- forked from Daily Affiliate Fees @ https://flipsidecrypto.xyz/studio/queries/41959f50-148d-4d11-a7d6-d1c90fe176ef

    -- forked from banbannard / Affiliate Fee - RUNE @ https://flipsidecrypto.xyz/banbannard/q/Zqikq5qhAE20/affiliate-fee---rune

    WITH base AS (
    SELECT
    tx_id,
    DATE_TRUNC('week',block_timestamp) AS Week,
    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) AS swap_volume
    FROM
    thorchain.defi.fact_swaps
    WHERE
    week >= '2024-01-01'
    GROUP BY
    tx_id, week, affiliate_address, affiliate_fee_basis_points,
    from_asset, to_asset
    ),
    Last run: about 2 months ago
    WEEK
    Affiliate
    AFFILIATE_FEE_USD
    Cumulative Affiliate Fees
    WEEKLY_AFFILIATE_FEES
    1
    		2025-02-10 00:00:00.000Trust Wallet340572316023745933
    2
    		2025-02-10 00:00:00.000Asgardex53712316023745933
    3
    		2025-02-10 00:00:00.000-_24962316023745933
    4
    		2025-02-10 00:00:00.000Thorwallet10372316023745933
    5
    		2025-02-10 00:00:00.000Zengo8782316023745933
    6
    		2025-02-10 00:00:00.000Edge Wallet8112316023745933
    7
    		2025-02-10 00:00:00.000Shapeshift6622316023745933
    8
    		2025-02-10 00:00:00.000Vultisig4722316023745933
    9
    		2025-02-10 00:00:00.000El Dorado902316023745933
    10
    		2025-02-10 00:00:00.000ro542316023745933
    11
    		2025-02-10 00:00:00.000CS32316023745933
    12
    		2025-02-10 00:00:00.000zengo-qa12316023745933
    13
    		2025-02-10 00:00:00.000g112316023745933
    14
    		2025-02-03 00:00:00.000Asgardex3017423114304103594
    15
    		2025-02-03 00:00:00.000Trust Wallet2292823114304103594
    16
    		2025-02-03 00:00:00.000Vultisig1141123114304103594
    17
    		2025-02-03 00:00:00.000Thorswap1054623114304103594
    18
    		2025-02-03 00:00:00.000Thorwallet588823114304103594
    19
    		2025-02-03 00:00:00.000-_584623114304103594
    20
    		2025-02-03 00:00:00.000Shapeshift567523114304103594
    ...
    1169
    67KB
    28s