(right(memo, charindex(':', reverse(memo)) - 1))/10000 as perc, (amount*perc) as fees_collected

from thorchain.swap_events

where memo like '%thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8%' or memo like '%thor1r765pfnwehv0rdy3jwyskldugw09fz7exncn2x%'

or memo like '%thor1e993ue8h3h8cf53c4cf6k9kywp7yfnwnkwhum2%' or memo like '%thor1jc5pa3djjwdm2mqjxee0463q9lth6pedu0flnl%'

or memo like '%thor13gym97tmw3axj3hpewdggy2cr288d3qffr8skg%' or memo like '%thor1uj0v03uhaxm7judgea2lj8tcwx2x8rz4g7pytm%'),

price as (select date(block_timestamp) as dt, pool_name, avg(asset_usd) as asset_usd, avg(rune_usd) as rune_usd

from thorchain.prices

group by 1,2),

final as (select a.dt, a.amount, a.pool_name, a.from_asset, a.perc, a.fees_collected, b.asset_usd, b.rune_usd, case

when a.from_asset = 'THOR.RUNE' then (a.fees_collected * b.rune_usd)

else (a.fees_collected * asset_usd) end as fees_usd

from raw a

left join price b on a.dt = b.dt and a.pool_name = b.pool_name)

select dt, sum(fees_usd) as total_fees_usd, SUM(total_fees_usd) OVER(ORDER BY dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_fees_usd

from final

group by 1