with pools_count as (

select

tx_id,

count(DISTINCT pool_name) as n_pools

from thorchain.defi.fact_swaps

group by 1

select

date_trunc('day', block_timestamp) as day,

sum((from_amount_usd / n_pools) * AFFILIATE_FEE_BASIS_POINTS) / 10000 as affiliate_fee

from thorchain.defi.fact_swaps a

left join pools_count using(tx_id)

WHERE AFFILIATE_ADDRESS is not null

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

and block_timestamp::date >= dateadd(month, -12, current_date)

group by 1

select

day,

round(EARNINGS_TO_NODES_USD) as "Bond Earning",

round(EARNINGS_TO_POOLS_USD) as "Pool & Dev Earning",

round(affiliate_fee) as "Affiliate Earning"

from thorchain.defi.fact_daily_earnings

left join affiliate_earning using (day)

where --day >= '2023-01-01'

day >= dateadd(month, -12, current_date)

order by day desc