case when AFFILIATE_ADDRESS='thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk' then 'THORSwap' when AFFILIATE_ADDRESS='thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8' then 'THORWallet'

when AFFILIATE_ADDRESS='thor1zw97zhhgwy3u99rxpn7cyelj44y733ntgg30e8' then 'Defispot' else AFFILIATE_ADDRESS end as AFFILIATE_NAME

from flipside_prod_db.thorchain.swaps where AFFILIATE_ADDRESS is not null and AFFILIATE_ADDRESS in ('thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8','thor19pvu2yn5x6jazdmxukuhutpqvpzmaysf0ej7dd',

'thor1zw97zhhgwy3u99rxpn7cyelj44y733ntgg30e8','thor1e993ue8h3h8cf53c4cf6k9kywp7yfnwnkwhum2', 'thor1r765pfnwehv0rdy3jwyskldugw09fz7exncn2x','thor1zawre3we072gm59q5nxd2ew2juygtxhe0j4ga3')

and tx_id not in (select TX_ID from thorchain.refund_events)

),

add_aff_fees as (select BLOCK_TIMESTAMP, FROM_ADDRESS as AFFILIATE_ADDRESS, POOL_NAME, RUNE_AMOUNT, RUNE_AMOUNT_USD, ASSET_AMOUNT, ASSET_AMOUNT_USD, ASSET_BLOCKCHAIN,

case when AFFILIATE_ADDRESS='thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk' then 'THORSwap'

when AFFILIATE_ADDRESS='thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8' then 'THORWallet' when AFFILIATE_ADDRESS='thor1zw97zhhgwy3u99rxpn7cyelj44y733ntgg30e8' then 'Defispot'

else AFFILIATE_ADDRESS end as AFFILIATE_NAME from flipside_prod_db.thorchain.liquidity_actions

where FROM_ADDRESS in ('thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8','thor19pvu2yn5x6jazdmxukuhutpqvpzmaysf0ej7dd',

'thor1zw97zhhgwy3u99rxpn7cyelj44y733ntgg30e8','thor1e993ue8h3h8cf53c4cf6k9kywp7yfnwnkwhum2',

'thor1r765pfnwehv0rdy3jwyskldugw09fz7exncn2x','thor1zawre3we072gm59q5nxd2ew2juygtxhe0j4ga3') and TO_ADDRESS is null and tx_id not in (select TX_ID from thorchain.refund_events)

and ASSET_TX_ID not in (select TX_ID from thorchain.refund_events)

select *, row_number() over (order by AFFILIATE_FEE desc) as rank from (select AFFILIATE_NAME, sum(AFFILIATE_FEE) as AFFILIATE_FEE, sum(swaps) as actions

from (select date_trunc('month',BLOCK_TIMESTAMP) as date, AFFILIATE_NAME, sum(RUNE_AMOUNT_USD+ASSET_AMOUNT_USD) as AFFILIATE_FEE, count(*) as swaps

from add_aff_fees

group by 1,2

union all

select date_trunc('month',BLOCK_TIMESTAMP) as date, AFFILIATE_NAME, sum(AFFILIATE_FEE_amount) as AFFILIATE_FEE, count(*) as swaps from swaps_aff_fees

group by 1,2)

group by 1

order by AFFILIATE_FEE desc )