nitsAffiliate Fees by Address for swaps
Updated 2022-08-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with affiliate_addresses as (select *,substring(MEMO,0,CHARINDEX(':',memo)-1) as cmd1,CHARINDEX(':',memo) as index1,substring(MEMO,CHARINDEX(':',memo,index1)+1) as right1,
substring(right1,0,CHARINDEX(':',right1)-1) as cmd2,CHARINDEX(':',right1) as index2, substring(right1,CHARINDEX(':',right1,index2)+1) as right2,
substring(right2,0,CHARINDEX(':',right2)-1) as cmd3,CHARINDEX(':',right2) as index3, substring(right2,CHARINDEX(':',right2,index3)+1) as right3,
substring(right3,0,CHARINDEX(':',right3)-1) as cmd4,CHARINDEX(':',right3) as index4, substring(right3,CHARINDEX(':',right3,index4)+1) as right4,
substring(right4,0,CHARINDEX(':',right4)-1) as cmd5,CHARINDEX(':',right4) as index5, substring(right4,CHARINDEX(':',right4,index5)+1) as right5
from thorchain.swap_events
where right3!= right4),
addr as
(select cmd5 as affiliate_address from affiliate_addresses
where cmd5 ilike 'thor%' and cmd2 = 'THOR.RUNE' and right5 not ilike '%:%'
group by cmd5 )
SELECT date(block_timestamp) as day,native_to_address, sum(total_amt_usd) as fee_collected,
sum(fee_collected) over (partition by native_to_address order by day) as cum_fees,
COUNT(DISTINCT tx_id) as total_txs,
sum(total_txs) over (partition by native_to_address order by day) as cum_txs,
cum_fees/cum_txs as fee_per_tx
from
(SELECT *, affiliate_fee_basis_points/1000*10*from_amount_usd/100 as total_amt_usd from thorchain.swaps
where native_to_address in (SELECT * from addr ))
GROUP by 1,2
-- limit 10
Run a query to Download Data