(select BLOCK_TIMESTAMP, AFFILIATE_ADDRESS, POOL_NAME, FROM_AMOUNT_USD, AFFILIATE_FEE_BASIS_POINTS, (AFFILIATE_FEE_BASIS_POINTS/10000)*FROM_AMOUNT_USD as AFFILIATE_FEE_amount,

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

(select sum(RUNE_AMOUNT_USD+ASSET_AMOUNT_USD) as AFFILIATE_FEE from add_aff_fees) as add_AFFILIATE_FEE,

(select count(*) from add_aff_fees) as add_liq,

(select sum(AFFILIATE_FEE_amount) as AFFILIATE_FEE from swaps_aff_fees) as swap_AFFILIATE_FEE,

(select count(*) as swaps from swaps_aff_fees) as swaps, swap_AFFILIATE_FEE+add_AFFILIATE_FEE as total_rev