Spot-Wiggum2024 Top 10 Affiliate by Fees
Updated 2024-12-02
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from Cumulative Affiliate Fees by Aff (30d) copy @ https://flipsidecrypto.xyz/studio/queries/f147bead-fd29-4e6c-9532-3bf16c37eec4
-- 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 (
-- Your base query here
SELECT
tx_id,
block_timestamp::date AS day,
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
block_timestamp >= '2024-01-01'
GROUP BY
tx_id, day, affiliate_address, affiliate_fee_basis_points,
from_asset, to_asset
Auto-refreshes every 12 hours
QueryRunArchived: QueryRun has been archived