date_trunc('hour', block_timestamp) as hours,

count(distinct tx_id) as count_tx

from thorchain.defi.fact_swaps

group by 1,2

having count_tx >= 100),

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

from_address,

a1.value::string as affiliate_address,

a2.value::number as affiliate_fee_basis_points,

tx_id,

split(from_asset, '-')[0] as from_assets,

blockchain,

case

when from_assets ilike '%/%' then split(from_assets, '/')[1]

else split(from_assets, '.')[1]

end as from_asset_names,

split(to_asset, '-')[0] as to_assets,

case

when to_assets ilike '%/%' then split(to_assets, '/')[1]

else split(to_assets, '.')[1]

end as to_asset_names,

concat(from_asset_names, ' -> ', to_asset_names) as assets,

case when concat(from_asset_names, ' -> ', to_asset_names) ilike '%rune' then 2 else 1 end as numbering,

sum(from_amount_usd / rune_usd) as rune_volume,

sum(from_amount_usd) as usd_volume

from thorchain.defi.fact_swaps as t,

lateral flatten(input => t.affiliate_addresses_array) a1,

lateral flatten(input => t.affiliate_fee_basis_points_array) a2

where affiliate_address is not null

and from_address not in (select from_address from bots)

and a1.path = a2.path

group by 1,2,3,4,5,6,7,8,9),

table_2 as (select day,