select block_timestamp, affiliate_address, affiliate_fee_basis_points,

from_amount_usd*affiliate_fee_basis_points/10000 as affiliate_fee_swap_out,

to_amount_usd*affiliate_fee_basis_points/10000 as affiliate_fee_swap_in,

case when from_asset='THOR.RUNE' then affiliate_fee_swap_out

when to_asset='THOR.RUNE' then affiliate_fee_swap_in

end as affiliate_fee,

case when from_asset='THOR.RUNE' then 'SWAP_OUT'

when to_asset='THOR.RUNE' then 'SWAP_IN'

end as user_action

from flipside_prod_db.thorchain.swaps

WHERE tx_id NOT IN (SELECT tx_id FROM flipside_prod_db.thorchain.refund_events)

select date_trunc('day', block_timestamp) as day, user_action,

sum(affiliate_fee) as total_affiliate_fee

from affiliate_rows

where block_timestamp>'2022-01-01 00:00:00.000'

group by day, user_action