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)

select cmd5 as "Affiliate Address",count(DISTINCT tx_id) as "Total Transactions" from affiliate_addresses where cmd5 ilike 'thor%' and cmd2 = 'THOR.RUNE' and right5 not ilike '%:%'

group by cmd5

order by 2 desc