TC AffiliatesTrader CRM

RayyykTC AffiliatesTrader CRM

Updated 14 hours ago

with table_1 as (select date_trunc('day', block_timestamp) as day,

from_address,

a1.value::string as affiliate_address,

a2.value::number as affiliate_fee_basis_points,

tx_id,

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 concat(from_asset_names, ' -> ', to_asset_names) ilike '%rune' then 2 else 1 end as numbering,

sum(from_amount_usd / rune_usd) as rune_volume,

sum(from_amount_usd) as usd_volume

from thorchain.defi.fact_swaps as t,

lateral flatten(input => t.affiliate_addresses_array) a1,

lateral flatten(input => t.affiliate_fee_basis_points_array) a2

where affiliate_address is not null

and a1.path = a2.path

and block_timestamp >= '{{Start_Date}}'

and block_timestamp <= '{{End_Date}}'

group by 1,2,3,4,5,6,7,8),

table_2 as (select day,

from_address,

tx_id,

affiliate_address,

affiliate_fee_basis_points,

array_agg(distinct assets) within group (order by assets asc) as swap_direction,

sum(rune_volume) as swap_volume_rune,

sum(usd_volume) as swap_volume_usd

Last run: about 14 hours agoAuto-refreshes every 24 hours

	Address	Total TX	TX on Platform	TX on Others	% of TX on Platform	Total $Vol	$Vol on Platform	$Vol on Others	% of Vol on Platform	Total Fees Paid	Fees on Platform	Fees on Others	% of Fees on Platform	Total Avg BPS	Avg BPS on Platform	Avg BPS on Others	Lowest Swap	Highest Swap	Platform Used
1	0x0dc5a54e864b8f7b2b4e5072c57dc3c5ba67bfae	10	10	0	100	4027.51	4027.51	0	100	7.72	7.72	0	100	19.17	19.17	0	125.03	1143.61	ShapeShift
2	0xfb4db0b7f891694273f44ccaf1d75fa4b99cdfe7	9	9	0	100	9448.81	9448.81	0	100	10.52	10.52	0	100	11.13	11.13	0	980.08	1557.23	ShapeShift
3	0x5283a70dad5e0e598d117ba727969ab461c90452	6	6	0	100	10640.97	10640.97	0	100	69.94	69.94	0	100	65.73	65.73	0	284.97	3929.84	ShapeShift
4	0xa4a5689f688c0c4c9483497947af46fbd250aa95	5	5	0	100	2420.73	2420.73	0	100	0	0	0	0	0	0	0	371.55	599.33	ShapeShift
5	0x36159d2343b992552263bfbcd9185cd04098ac69	5	5	0	100	2353.61	2353.61	0	100	0	0	0	0	0	0	0	79.89	743.28	ShapeShift
6	0x5daf465a9ccf64deb146eeae9e7bd40d6761c986	4	4	0	100	15.01	15.01	0	100	0	0	0	0	0	0	0	2.01	4.99	ShapeShift
7	0x77653e011f44efe752d5e11c959b5ec4fa75a17b	4	4	0	100	2752.87	2752.87	0	100	10.63	10.63	0	100	38.61	38.61	0	86.75	1574.02	ShapeShift
8	0x402775f7f1dbe266193efadf46ef7c562ba2ca9c	4	4	0	100	109.86	109.86	0	100	0	0	0	0	0	0	0	5	69.86	ShapeShift
9	0xe505e6d69a1c7a374bb63866221c2e390c22981b	4	4	0	100	1006.78	1006.78	0	100	0	0	0	0	0	0	0	58.1	499.47	ShapeShift
10	0x52e1ee958b9dccb79c1214932a983bb747933183	4	4	0	100	4540.71	4540.71	0	100	0	0	0	0	0	0	0	607.5	2051.51	ShapeShift
11	cosmos1rtppk52z2hxufgtfqkx7dw6p680q473xeangt7	9	3	6	33.33	37747.69	19776.45	17971.24	52.39	182.86	133.57	49.29	73.04	48.44	67.54	27.43	4238.42	9516.19	SwapKit, THORSwap, ShapeShift
12	0xfee73611726fd31a50e297473dff75e2aead8c4d	3	3	0	100	9045.15	9045.15	0	100	15.83	15.83	0	100	17.5	17.5	0	1364.1	6153.8	ShapeShift
13	qphgax8jtwn20kvrqfmxcps79n3wyhge7va8hnhngd	3	3	0	100	13942.14	13942.14	0	100	94.17	94.17	0	100	67.54	67.54	0	2648.84	6564.43	ShapeShift
14	0xba2cbccf92a726cfc7591119b487f51b4c1b39dc	3	3	0	100	1012.86	1012.86	0	100	0	0	0	0	0	0	0	303.49	399	ShapeShift
15	bc1qmkup25ggxdv5lckj6lgdr07a8cslux4hnwv6y6	3	3	0	100	20315.27	20315.27	0	100	135.24	135.24	0	100	66.57	66.57	0	70	15215.9	ShapeShift
16	0x69520fce540bb2a8632d6d2be45efba21d5263cf	3	3	0	100	2956.21	2956.21	0	100	0	0	0	0	0	0	0	973.39	992.63	ShapeShift
17	bc1qzmz6udgq9ytjjwr2aaasejdy4xmpcnj6whawwh	3	3	0	100	3618.61	3618.61	0	100	24.44	24.44	0	100	67.54	67.54	0	1004.17	1606.6	ShapeShift
18	0x557c61ec8f7a675be03efe11962430ac8cff4229	3	3	0	100	14.99	14.99	0	100	0	0	0	0	0	0	0	4.98	5.01	ShapeShift
19	thor1ycgzfpcz93qa0xc392xgk75lfee5vdc59hv3r8	3	3	0	100	21.89	21.89	0	100	0	0	0	0	0	0	0	5.08	9.98	ShapeShift
20	thor1wrsru5klw2pjp7hu52lmt5qadlat44r579muq8	2	2	0	100	40.06	40.06	0	100	0	0	0	0	0	0	0	10.05	30.01	ShapeShift

...

383

45KB

flipside

flipside