pietrektTHORSwap Affiliate Fees
Updated 2024-10-01
99
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
›
⌄
WITH swaps_with_aff_fees AS (SELECT to_date(block_timestamp) as date, from_asset, to_asset, to_e8, from_e8, liq_fee_e8, SPLIT_PART(memo, ':', 5) as aff_address,
SPLIT_PART(memo, ':', 6) as aff_fee from thorchain.defi.fact_swaps_events
WHERE SPLIT_PART(memo, ':', 5) != '' and SPLIT_PART(memo, ':', 6) != '' and
(SPLIT_PART(memo, ':', 5) = 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk' or SPLIT_PART(memo, ':', 5) = 'T' or SPLIT_PART(memo, ':', 5) = 't')),
from_rune AS (SELECT date, from_e8 * aff_fee / 10000 / 100000000 as fees, aff_address FROM swaps_with_aff_fees WHERE from_asset = 'THOR.RUNE'),
to_rune AS (SELECT date, to_e8 * aff_fee / 10000 / 100000000 as fees, aff_address FROM swaps_with_aff_fees WHERE to_asset = 'THOR.RUNE'),
rune_prices AS (SELECT to_date(block_timestamp) as date, avg(rune_usd) as rune_price_usd
from thorchain.price.fact_prices where pool_name='BNB.BUSD-BD1' group by date),
thor_prices AS (SELECT to_date(block_timestamp) as date, avg(asset_usd) as thor_price_usd
from thorchain.price.fact_prices where pool_name='ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' group by date),
swaps_union AS (SELECT * FROM to_rune UNION ALL SELECT * FROM from_rune),
fees_taken_per_address AS (SELECT date, aff_address, SUM(fees) as aff_fee_earned FROM swaps_union GROUP BY aff_address, date),
swaps_and_prices AS(SELECT a.date, a.aff_address, a.aff_fee_earned, rune_price_usd, thor_price_usd
FROM fees_taken_per_address as a left join rune_prices as b on a.date = b.date left join thor_prices as c on a.date = c.date)
SELECT date, MAX(aff_address) as aff_address, sum(aff_fee_earned) as aff_fee_earned,
rune_price_usd, thor_price_usd,
sum(aff_fee_earned) * rune_price_usd as aff_fee_earned_usd,
aff_fee_earned_usd / thor_price_usd as aff_fee_earned_thor FROM swaps_and_prices
WHERE date IS NOT null
GROUP BY date, rune_price_usd, thor_price_usd ORDER BY date
QueryRunArchived: QueryRun has been archived