select

tx_id,

count(DISTINCT pool_name) as n_pools

from thorchain.defi.fact_swaps

where tx_id not in (select tx_id from thorchain.defi.fact_refund_events)

group by 1

select

round(sum(from_amount_usd)) as swap_volume,

round(count(distinct tx_id)) as n_swaps,

round(sum((from_amount_usd / n_pools) * AFFILIATE_FEE_BASIS_POINTS) / 10000) as affiliate_fee_paid

from thorchain.defi.fact_swaps a

join pools_count using(tx_id)

where from_address = '{{wallet_address}}'